What is the syntax to retrieve all open workbooks in Excel?

'wb.Name holds the name of the open workbook
Dim wb As Workbook
For Each wb In Application.Workbooks
 <code here with involving wb.Name>
Next wb
'example
Dim wb As Workbook
For Each wb In Application.Workbooks 'for each open workbook, pass the name to lookupEGA()
   MsgBox wb.Name
   lstWorkbooks.AddItem wb.Name
Next wb

 

Advertisements

What is the syntax to close workbook?

Workbooks("<workbook name>").Close saveChanges:=<true or false>
'example
Workbooks("example.xlsm").Close saveChanges:=true

 

 

Tips for debugging when working with workbooks

When working with workbooks, you may get Run-Time Error:9. That is, the following message box may pop up during execution:

Capture.PNG

Tips for debugging this error:

  • Is the workbook you are referencing currently closed?
  • Did you misspell the name of the workbook?
  • Are you trying to reference a new workbook that has not been explicitly saved for the first time?
  • Are you running two instances of Excel?
  • Did you pass an index that is greater than the number of workbooks open?