Open and close Excel workbooks in the background
Use a Visual Basic for Applications (VBA) script to open secondary Excel workbooks as data sources when opening a VLOOKUP-dependent workbook.
One of the limitations of VLOOKUP in Excel is that references to tables outside of the workbook (ie. within a different file) require that workbook to be open in order for them to work. Failure to open that second workbook will cause a #Ref! error. Not nice.
This situation becomes problematic because a person who wants to access data from an Excel sheet with external references not only has to remember to open the external file but also has to remember precisely which file to open.
Here’s a far better solution: A VBA script which automates the process and minimises confusion for the user.
Let’s say that we have three Excel sheets:
- ExcelSheet1.xlsm
- ExcelSheet2.xlsx
- ExcelSheet3.xlsx
ExcelSheet1.xlsm contains two tables. The first table contains data which extracts values via a VLOOKUP function from a table in ExcelSheet2.xlsx. The second pulls data from a table in ExcelSheet3.xlsx. The following scrips will automatically open ExcelSheet2.xlsx and ExcelSheet3.xlsx when ExcelSheet1.xlsm is opened, but will keep them in the background so that the user doesn’t notice.
Private Sub Workbook_Open()
Application.ScreenUpdating = False
Workbooks.Open Filename:="C:\Documents\ExcelSheet2.xlsx"
Workbooks.Open Filename:="C:\Documents\ExcelSheet3.xlsx"
ThisWorkbook.Activate
Application.ScreenUpdating = True
End SubNote the full file paths? They’re not necessary, but helpful. To enable the script, simply paste the code into the ThisWorkbook code page under the ‘Microsoft Excel Objects’ tree for ExcelSheet1.xlsm in Visual Basic for Applications. You can add or subtract as many files as you like to the list.
That part deals with the opening of the files, but what about the closing? I want all of the files to close when ExcelSheet1.xlsm is closed. To do this, I utilised the following script which is also located in the ThisWorkbook code page for ExcelSheet1.xlsm. Do not use full file paths!
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.DisplayAlerts = False
Workbooks("ExcelSheet2.xlsx").Close SaveChanges:=False
Workbooks("ExcelSheet3.xlsx").Close SaveChanges:=False
End SubWhat this does is enable the files to be closed without generating a “Save As…” dialogue box. Since the files were opened (but never visible), the user should not have made any changes and therefore the closing of the files without saving is a safe option.
Outcome
When a user opens ExcelSheet1.xlsm, the VBA script will automatically open ExcelSheet2.xlsx and ExcelSheet3.xlsx in the background.
A “This workbook contains links to other data sources” dialogue box will appear, as per usual. The user can click on “Update” and the tables will update correctly.
When the ExcelSheet1.xlsm file is closed, the ExcelSheet2.xlsx and ExcelSheet3.xlsx files will also close without a warning dialogue.
Don’t forget: You need to use the Excel Macro-Enabled Workbook (xlsm) file format for your initial Excel file.
Comments
2 responses to “Open and close Excel workbooks in the background”
Thank you for this. I’m trying to do as it says but I’m having issues (total VBA novice).
The coding works in opening the second spreadsheet but I have to manually run the macro, it’s not happening automatically when I open the first spreadsheet.
Any help appreciated!
Nice tutorial