I have some VBA code that tries to read in data from the active excel book using ADO (I am currently using the ADO 2.7
library). Here is the code:
Dim con As String
Dim src As String
'Open volume data as read-only recordset
con = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & fullName & ";" & _
"Extended Properties=Excel 8.0;"
src = "SELECT * FROM [volumes$]"
xl.CursorLocation = adUseClient
Call xl.Open(src, con, adOpenStatic, adLockReadOnly)
If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
The string "fullName" is determined when somebody clicks the "run" button by calling ActiveWorkbook.Path and
ActiveWorkbook.Name. The sheet being read is "volumes", which is dynamically created during the execution of the code.
Here is the problem I have encountered:
My end users often have multiple instances of Excel running. If
they open this workbook in the original (primary) instance of Excel, there is no problem; however, if they open it in a
secondary instance, the code bombs at the "Call xl.Open" line. The code opens a new copy of the workbook in the primary
instance of Excel, and this copy doesn't have the "volumes" sheet in it, so the program crashes.
The problem is
solved if they always remember to open my workbook by double-clicking on the file icon outside of Excel, rather than going
through the Excel File menu; it then always opens in the primary instance.
However, I would prefer that they be
able to open the workbook any way they choose, and in any Excel instance they choose.
One solution, which is my
least preferred, is to abandon the use of ADO, and read the data into arrays.
The preferred solution, if it
exists, is to find a way to specify the active workbook in the connection string. The code snippet I posted above is
generally referenced as a way to read data from a closed workbook; is there a different, preferred way to read from the
active workbook, so that Excel doesn't attempt to open a new copy?
Failing that, is there a way within VBA to
determine if the Excel instance executing the code is the primary instance? I could use this code in Workbook_Open() to
determine if it was the right instance of Excel, and if not, inform the user to open the file by double-clicking on it, and