Open and close Excel workbooks in the background
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 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 Sub
Note 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 Sub
What 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.
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.