cannot open multiple Excel documents

[qwertyjjj]

I have the following code, which works well individually:

Private Sub cmd_Export_Click()
DoCmd.OutputTo acOutputForm, "InterestCharge", acFormatXLS, "C:\InterestCharge.xls", False

Dim appExcel As Object
Dim workBook As Object
Dim workSheet As Object

' Open an existing spreadsheet
Set appExcel = GetObject("C:\InterestCharge.xls")

' Show spreadsheet on screen
appExcel.Application.Visible = True
appExcel.Parent.Windows(1).Visible = True

' Autofit all columns on a worksheet
Set workSheet = appExcel.Worksheets("InterestCharge")

With workSheet
.Range("G1").Value = "1"
.Range("G1").Copy
.Range("B2:F200").Select
.Range("B2:F200").PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply, SkipBlanks:=False, Transpose:=False
.Range("G1").ClearContents
.Range("A1").Value = "Cost Centre"
.Range("B1").Value = "Interest Charge"
.Range("C1").Value = "DD"
.Range("D1").Value = "DD-1"
.Range("E1").Value = "DD-2"
.Range("F1").Value = "DD-3"
.Range("A1").Select
End With

' Release objects
Set workSheet = Nothing
Set workBook = Nothing
Set appExcel = Nothing
End Sub

However, when I ty to export the data and open Excel when Excel is already open nothing happens.
Any ideas on what I have to change?

Thanks

 

[boblarson]

Use CreateObject instead of GetObject. GetObject will use an existing Excel instance and CreateObject will open multiple instances.

 

[qwertyjjj]

I tried that but same problem.

I am now using this but I have to call the Shell command to open 3 separate instances of Excel for each of the export buttons.
Isn't there a better way?


Call Shell("C:\Program Files\Microsoft Office\Office11\excel.EXE " & "C:\A.xls", 1)
Set appExcel = CreateObject("C:\A.xls")
Set workSheet = appExcel.Worksheets("A")

 

[boblarson]

Have you used Task Manager to find out if you have open, but invisible instances open? That is what's happened to me at times. Then it won't work right. They are usually hanging around after running your code but getting an error and it doesn't close. Normally it won't happen with the .visible added, but if it glitched sometime before getting to that point, it could have one, or more, instances hanging.

Otherwise, it works fine for me in my applications.