CODEWITHSUNDEEP
excelvbaoutlook
TiTus
TiTus

Reputation: 77

Set workbook variable if it is open

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
....

Upvotes: 1

Views: 1498

Answers (1)

Scott Holtzman
Scott Holtzman

Reputation: 27259

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.

Upvotes: 4

Related Questions