asked on open xls workbook, check if already open
In MS Access I want to first check to see if Excel is open. If so, check to see if a certain workbook is already open. If not open it, otherwise set the open workbook as the active workbook.
Here is what I have so far. Almost there but if it is already open it gives a warning about re-opening an open workbook.
Here is what I have so far. Almost there but if it is already open it gives a warning about re-opening an open workbook.
Private Function UpdateCrisisChart()
Dim xls As Object
Dim wrk As Object
Dim Sheet As Object
Dim strExcelFile As String
If IsExcelRunning Then
Set xls = GetObject(, "Excel.Application")
Else
Set xls = CreateObject("Excel.Application")
End If
xls.Visible = True
strExcelFile = GetExcelFilename(Me.SDate, "Adult")
Set wrk = xls.Workbooks.Open(strExcelFile)
End Function
Microsoft Access
Last Comment
mbizup
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
I should mention I am concerned if they have multiple workbooks open, one of which is the one I need. How do I set the one I want as active?
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
As an aside, I'm using "Early Binding" in my code, declaring the excel objects like this:
This makes for easy programming, because you get auto completion of your commands in the VB Editor,
However, if compatibility across multiple versions of Office is a concern, use late binding instead - declaring these as generic Objects:
The downside to Late Binding is that you lose the intellisense/autocompletio
I typically keep both versions in my code and switch between them - commenting out the late binding in favor of early binding to get auto completion, and then switching back to late binding before deploying an application so that it will work regardless of Office version.
Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook
Dim xlWS As Excel.Worksheet
Dim wb As Excel.Workbook
This makes for easy programming, because you get auto completion of your commands in the VB Editor,
However, if compatibility across multiple versions of Office is a concern, use late binding instead - declaring these as generic Objects:
Dim xlApp As Object
Dim xlWB As Object
Dim xlWS As Object
Dim wb As Object
The downside to Late Binding is that you lose the intellisense/autocompletio
I typically keep both versions in my code and switch between them - commenting out the late binding in favor of early binding to get auto completion, and then switching back to late binding before deploying an application so that it will work regardless of Office version.
Set wrk = xls.Workbooks.Open(strExce