M
MDW
I'm pretty conversant with using Excel automation from Access, but this
problem has me perplexed.
I'd like to populate a listbox in Access with a list of any Excel files that
happen to be open. However, my code doesn't seem to work:
Set objXL = CreateObject("Excel.Application") ' Line 1
For Each objWB In objXL.Workbooks
If objWB.IsAddin = False Then
lstFiles.RowSource = lstFiles.RowSource & objWB.Name & ";"
End If
Next
Set objXL = Nothing
lstFiles.Requery
End Sub
It appears that when I create my Excel object on line 1, a completely new
instance of Excel is created (I verified this by seeing two "EXCEL.EXE"
entries suddenly appear in the task manager), and this new instance isn't
aware of the workbooks already open by the original instance. In debug mode,
my code never enters the "For Each objWB In objXL.Workbooks" loop.
I get the same problem if I change line 1 to "Set objXL = New
Excel.Application
Does anyone know of a way around this problem? Or am I not approaching the
problem properly?
problem has me perplexed.
I'd like to populate a listbox in Access with a list of any Excel files that
happen to be open. However, my code doesn't seem to work:
Set objXL = CreateObject("Excel.Application") ' Line 1
For Each objWB In objXL.Workbooks
If objWB.IsAddin = False Then
lstFiles.RowSource = lstFiles.RowSource & objWB.Name & ";"
End If
Next
Set objXL = Nothing
lstFiles.Requery
End Sub
It appears that when I create my Excel object on line 1, a completely new
instance of Excel is created (I verified this by seeing two "EXCEL.EXE"
entries suddenly appear in the task manager), and this new instance isn't
aware of the workbooks already open by the original instance. In debug mode,
my code never enters the "For Each objWB In objXL.Workbooks" loop.
I get the same problem if I change line 1 to "Set objXL = New
Excel.Application
Does anyone know of a way around this problem? Or am I not approaching the
problem properly?