How could I write code to say.
Dim xlApp As Excel.Application
Dim xlWorkbook As Excel.Workbook
Dim xlWorksheet As Excel.Worksheet
if an excel Workbook is already open then....
Set xlApp = GetObject(, "Excel.Application")
elseif xlApp is nothing then
Set xlApp = New Excel.Application
xlApp.Visible = True
Set xlWorkbook = xlApp.Workbooks.Open("E:\InspectionCreator\InspectionSheet.xlsx")
End if
I don't want it to have to be a specific workbook just any workbook I can't seem to find anything on the internet. Any help would be awesome.
Steps to run macro automatically when the workbook opens: Step 2: Go to ThisWorkbook Tab. Step 3: Write down Private Sub Workbook_open() and hit enter. You will then see the below screen. You can write your code or basically whatever you want between this and it will automatically run every time the workbook is opened.
Click Developer > Visual Basic. In the VBA Project Explorer on the left hand side, expand the VBA Project folder for your workbook, then double-click the ThisWorkbook module. Paste your recorded code in the Sub procedure between the Sub and End Sub lines. Close the Visual Basic Editor (you don't have to save anything).
First try using getobject: if it throws an error then use createobject:
Dim xlApp As Excel.Application
On Error Resume Next
Set xlApp = GetObject(, "Excel.Application")
On Error GoTo 0
If xlApp Is Nothing Then
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
End If
I used to run code very similar to Tim's until Kevin Jones pointed out in an Experts-Exchange post that I will repeat here (as the EE post is behind the paywall)
"Be aware that when launching Excel through automation using the CreateObject function, Excel does not load any Add-Ins or other workbooks normally loaded automatically. This is not a good way to start an Excel session that will be used by the user. To start an Excel application instance without using automation from any application other than Excel, the Excel application must be launched using non-automation means. The code below illustrates the steps to do this. The code first tries to obtain an automation handle to an existing application instance. If an existing instance is not found then a new instance is started using the Shell command."
Dim ExcelApplication As Object
Dim TimeoutTime As Long
On Error Resume Next
Set ExcelApplication = GetObject(, "Excel.Application")
On Error GoTo 0
If ExcelApplication Is Nothing Then
Shell "Excel.exe"
TimeoutTime = Timer + 5
On Error Resume Next
Do
DoEvents
Err.Reset
Set ExcelApplication = GetObject(, "Excel.Application")
Loop Until Not ExcelApplication Is Nothing Or Timer > TimeoutTime
On Error GoTo 0
End If
If ExcelApplication Is Nothing Then
MsgBox "Unable to launch Excel."
Else
' Do something with the Excel instance...
End If
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With