2 Instances of Excel Object using 2 VB Projects @ same time

[ahmarshi]

Hi,

I am using VB 6.0 to automate some reports in excel. For every report there is a new VB project/VB executable.

My Program does following;
Creating a reference to Excel.Application object
Opening an excel template
Putting data in excel worksheets
Saving excel workbook with different name
Closing excel workbook

Problem
When two of my executables run at the same time, since they both are referencing excel object, they got failed, none of them is able to create excel report.

Please let me know if there is any workaround otherwise we may use threads in VB 6 or we can also move on to the .NET. But before that we need to find some workaround in VB 6 other than threading.

Thanks a lot guys,
M.Ahmar

[si_the_geek]

Hi ahmarshi,

Having two applications using Excel automation at the same time should not be a problem (I've had several running at the same time before), but I'm afraid without seeing your code I cant comment on why this would be happening.

Several people have problems with Excel staying open after they think it should close, unfortunately it is usually due to a problem with their code (but this can be hard to spot if you don't know what you are looking for!); I presume that this would be a similar sort of issue.

If you post your code I can hopefully provide the solution

I have just one question from what you have posted so far.. do the different reports use the same template?

[RobDog888]

Yes, we need to see code, but if you are using the GetObject function you could possibly be attaching to the same instance of Excel as your other program(s). Also, if your displaying a SaveAs dialog box and then another workbook tries to save at the same time, via code, it could potentially be the issue.

Just something to think about

[ahmarshi]

Please, see my code here under. As you have said that it is possible in VB, I will also try myself to write safe code.

Answer To Your Question
No, they all have different templates.

If you can help, it will be appreciated.

Thanks & Best Regards,
M.Ahmar

My Code

Public xlAppTemp As Excel.Application

Public Sub GenerateReport()
On Error GoTo ErrHandler

' Creating Object for Excel File.....
Set xlAppTemp = New Excel.Application

' Making it Invisible and non-Interactive.....
xlAppTemp.Visible = False
xlAppTemp.DisplayAlerts = False

' Opening Template Excel File.....
xlAppTemp.Workbooks.Open strTemplatePath & "MyReport.xls", , False

' Making Active to Worksheet 1.....
xlAppTemp.Sheets(1).Activate

' I am doing lot of things in it, but to provide you with example
xlAppTemp.ActiveSheet.Cells(1,1) = "This is my report 1"

' Formating Date to attach with new file name.....
strDate = Format(Date, "yyyy-mm-dd")

' Saving excel file with new name on different folder.....
xlAppTemp.ActiveSheet.Parent.SaveAs strResultPath & "MyReport\MyReport" & strDate & ".xls"

Workbooks(1).Close
Cleanup:
' Destroying Objects.....
Set xlAppTemp = Nothing
End Sub

[RobDog888]

To help clarify for Excel to know which workbook your referring to you should refer to it by name and the same with the sheets. Dont use ActiveSheet. But the problem is that if you run two instances of your program, you should have two instances of Excel application showing in your Taskmanager?

[si_the_geek]

I totally agree, and have noticed another issue (which could be causing the problems) - you have Workbooks(1).Close without reference to the parent object (xlAppTemp). This can cause various issues, including the Excel application refusing to close when your program is finished.

You also do not close your objects properly, simply setting the application object to Nothing does not close it - this just empties your variable. If you look in Task Manager you will probably see one copy of Excel.exe for each time that you have run your code.

Here is a modified version of your code to correct these issues (you should make the same changes in the code you have not shown):

Public xlAppTemp As Excel.Application
Public xlWorkBook as Excel.Workbook
Public xlSheet as Excel.Worksheet
 
Public Sub GenerateReport()
  On Error GoTo ErrHandler
  
  ' Creating Object for Excel File.....
  Set xlAppTemp = New Excel.Application
  
  ' Making it Invisible and non-Interactive.....
  xlAppTemp.Visible = False
  xlAppTemp.DisplayAlerts = False
  
  ' Opening Template Excel File.....
  Set xlWorkBook = xlAppTemp.Workbooks.Open (strTemplatePath & "MyReport.xls", , False)
  Set xlSheet = xlWorkBook.Sheets(1)
 
  ' Making Active to Worksheet 1.....
  xlSheet.Activate
 
  ' I am doing lot of things in it, but to provide you with example
  xlSheet.Cells(1,1) = "This is my report 1"
 
  ' Formating Date to attach with new file name.....
  strDate = Format(Date, "yyyy-mm-dd")
  
  ' Saving excel file with new name on different folder.....
  xlWorkBook.SaveAs strResultPath & "MyReport\MyReport" & strDate & ".xls"
 
Cleanup:
  ' Destroying Objects.....
  Set xlSheet = Nothing
'I presume this section comes after ErrHandler, in which case you will want to close the workbook without changes.
'(save happens just above if no error occurs)
  xlWorkBook.Close SaveChanges:=False
  Set xlWorkBook = Nothing
 
'The Visible and DisplayAlerts settings should be reset, as they can affect 'manual' use of Excel too.
  xlAppTemp.Visible = True
  xlAppTemp.DisplayAlerts = True
 
  xlAppTemp.Close
  Set xlAppTemp = Nothing
End Sub

[ahmarshi]

Thanks guys,

Actually, I figured this thing out few minutes ago.

Thanks for your help.
Mo

[dglienna]

This is incorrect:

xlAppTemp.Close

should be

xlAppTemp.Quit

[RobDog888]

But as you can see dglienna, it is a minor issue as ahmarshi posted he was able to figure it out.

[dglienna]

Well, so did I
Just in case others look at it.