I am trying to write a macro in Outlook to do some stuff on Outlook and modify an Excel document.
If "Doc List.xlsm" is open, Set ExWbk = ExApp.Workbooks("C:\Doc List.xlsm") is not working.
If the document is closed, then Set ExWbk = ExApp.Workbooks.Open("C:\Doc List.xlsm") is working and I am able to change the Excel doc.
When I put some error handling, I get:
Error(9): Subscript Out of Range.
Dim ExApp As Excel.Application
Dim ExWbk As Workbook
Dim ExSheet As Worksheet
....
Set ExApp = New Excel.Application
x = IsWorkBookOpen("C:\Doc List.xlsm")
If x = True Then
Set ExWbk = ExApp.Workbooks("C:\Doc List.xlsm")
Else
Set ExWbk = ExApp.Workbooks.Open("C:\Doc List.xlsm")
ExApp.Visible = True
End If
....
The problem is that a new instance of Excel is always opened. And Doc List.xlsm will never be open in the instance (because you just launched the instance of Excel on your machine - via code). Doc List.xlsm, if open, will be so in an existing instance of Excel.
Therefore, you can use this to test if there is an Excel instance already open on your machine.
'initiate Excel object
On Error Resume Next
Set ExApp = GetObject(,"Excel.Application")
On Error GoTo 0
If ExApp Is Nothing Then Set ExApp = New Excel.Application
Now, I can't see your IsWorkbookOpen function, but you have to check if the workbook is open in the instance of Excel you just initiated, so make sure that happens.
Then the rest of your code becomes
If x Then
Set ExWbk = ExApp.Workbooks("Doc List.xlsm") 'no need to include directory path
Else
Set ExWbk = ExApp.Workbooks.Open("C:\Doc List.xlsm")
ExApp.Visible = True
End If
On a last note, to be clear, even this could fail if there were more than one instance of Excel open on the machine when the code is and the Doc List.xlsm file is open in the other instance not picked up by the GetObject reference. But I won't go into how to handle that here.