If you are working with different Excel files that contain the same type of information i.e, information of the employees from different departments of a large organization, then you might need to copy the information from all these Excel files to one single file. But opening each file and copying the information to another one might be tiresome if you have a lot of files to process. In this tutorial, I will show you how to use Excel VBA get cell value from another workbook without opening it.
Some Scenarios Where You Might Need to Get Data from a Closed Book Using VBA
- If you have data distributed over numerous workbooks and need to consolidate it in a single workbook, you can use VBA to retrieve data from the closed workbooks and transfer it to the destination workbook.
- If you have a series of workbooks that are often updated and you need to extract data from them on a regular basis, you may use VBA to automate the data extraction process. This saves time and reduces errors that may occur when manually copying and pasting data.
- If you have sensitive information in a workbook that you want to share with others but don’t want them to see the raw data or formulas, you may use VBA to extract simply the values from the workbook and copy them to a new worksheet. You can distribute the updated workbook without disclosing the original data this way.
- If you need to study historical data, you can use VBA to extract the data and analyze it in a separate worksheet if you have a big set of historical data saved in closed workbooks. This might assist you in identifying trends and patterns in the data that may not be visible in the individual worksheets.
Finally, using VBA to retrieve data from a closed book might be useful in cases where you need to automate data updates, consolidate data, safeguard sensitive information, or study historical data.
How to Launch VBA Editor in Excel
In this section, we are going to demonstrate how to launch VBA Editor & create a VBA module in Excel. First, you need the Developer tab to display on your ribbon.
- First, we go to the Developer tab.
- Then we will select Visual Basic.
Then a new window will pop up. Then we are going to follow these steps,
- First, we will select Insert,
- Then we are going to select Module.
- A new Module will be created.
Note: If the Developer tab is not visible on your ribbon, press the Alt + F11 keys to launch VBA Editor.
Excel VBA to Get Cell Value From Another Workbook Without Opening in Excel: 2 Examples
Let’s assume a scenario where we have an Excel worksheet that has information about the employees of an organization. We have named this workbook as the Source workbook. On my PC, the file address of the source file is “E:\study\Office\Comments\Get Value From Another Workbook\Source.xlsm”. We also named the file “Destination” to where we will copy the cell values.
We will copy the cell values of range B4:E10 of the Source workbook while it is close to the Destination workbook using VBA. The image below shows the cell values of the Source workbook.
Now we will see 2 VBA Codes by which we can copy the data from the source file and paste it on the destination file without opening the source file. Let’s explore the examples one by one.
1. Copying Cell Values with Formatting
In the provided code below, we will be able to copy the cell values from the source file with formatting without opening the source file.
Sub get_cell_value() Application.ScreenUpdating = False Set Source_workbook _ = Workbooks.Open("E:\study\Office\Comments\Get Value From Another Workbook\Source.xlsm") Set Source = Workbooks("Source").Worksheets("Sheet1").Range("B4:E10") Set Destination = Workbooks("Destination").Worksheets("Sheet1").Range("B4:E10") Source.Copy Destination Source_workbook.Close SaveChanges:=False Application.ScreenUpdating = True End Sub
📌How Does the Code Work?
This will create a sub-procedure named “get_cell_value”.
ScreenUpdating = False
This code above will disable the
Application.ScreenUpdating to run the process in the background.
Set Source_workbook = Workbooks.Open("C:\Users\User\OneDrive\Desktop\VBA\Source.xlsx")
This will open the Source workbook and keep it in a variable to enable calling it further.
Set Source = Workbooks("Source").Worksheets("Sheet1").Range("B4:E10")
Setting the source range. You need to modify it according to your worksheet
Set Destination = Workbooks("Destination").Worksheets("Sheet1").Range("B4:E10")
Setting the destination range. You need to modify it according to your worksheet
The code line above will copy the cell values of the range B4:E10 from the Source workbook and paste them into the Destination workbook.
So, this will close the source workbook.
ScreenUpdating = True End Sub
Finally, the code sets the screen updating to be turned on and ends the current subroutine in VBA.
Now, if we run the code, we will see that the B4:D10 range from the source file has been copied with formatting to the Destination file without visibly opening the source file. That’s how you can use Excel VBA to get cell value from another workbook without opening.
- Even though the user will not see the opening of the source file, the source file is actually opened in the background.
- This method may not work properly when the source cells contain formulas with relative cell references.
2. Get Cell Value from Another Workbook Without Formatting
In this example, we will run a code that will get value from the source workbook while it is closed. But the major drawback of this code is that it doesn’t copy the formatting of the cells from the source file.
On the bright side, it doesn’t actually open the workbook. So it minimizes the runtime of the code. But as it only copies values but not formatting, it can be useful when we only need values.
Sub GetDataFromClosedBook_WO_Formatting() Dim source_data As String 'Setting the source address source_data = "='E:\study\Office\Comments\Get Value From Another Workbook\[Source.xlsm]Sheet1'!$B$4:$E$10" 'Assigning to Destination file location With ThisWorkbook.Worksheets(2).Range("B4:E10") '<< modify it accordingly .Formula = source_data 'Taking only values .value = .value End With End Sub
It creates a subroutine named GetDataFromClosedBook_WO_Formatting
Dim source_data As String
Declaring a variable source_data as a string-type data.
source_data = "='E:\study\Office\Comments\Get Value From Another Workbook\[Source.xlsm]Sheet1'!$B$4:$E$10"
In this line, we are assigning the data location & range to copy to source_data. In your case, you need to edit the line while keeping the structure the same. Here,
- E:\study\Office\Comments\Get Value From Another Workbook should be replaced by the file path/address of your source file.
- Source.xlsm should be replaced by the name of your source file
- Sheet1′!$B$4:$E$10 should be replaced by the range that you want to copy from the source file.
This is the range of the destination file where we want to copy the value from the source.
.Formula = source_data 'Taking only values .value = .value
Here, the cell’s value from the source file is copied to the range of the destination file.
Now if we run the code, we will get the following result.
How to Reference from or Link Value with Unopened/Closed Excel Workbook File Using Formula
In this section, I will show an easy method for referencing a cell value from a closed/unopened Excel workbook file. For example, if the file path or address of an unopened file is “C:\Users\Aniruddha\Documents\Aniruddah_90\90_0072\Source.xlsm” and I want to refer to the B5 cell of Sheet1 in another Excel file.
To do that, I need to follow the steps below.
- Go to the formula bar of a cell in the workbook where you want to put the reference and type the following formula.
📌How Does the Formula Work?
- C:\Users\Aniruddha\Documents\Aniruddah_90\90_0072 is the folder address of the source/referenced Excel File.
- [Source.xlsm] is the name of the referenced Excel file.
- Sheet1′!$B$4:$E$10,2,1 is referencing/indicating the 2nd row and 1st column of the array B4:E10 which is essentially B5.
- If you want to choose the sheet manually, then you need to apply the formula below.
- If you click Enter, you will see another window asking to choose a sheet from the Source File. Now, choose your desired sheet and click OK.
- As a result, we get the exact value of cell B5 in the Sheet1 of the source Excel file.
Things to Remember
- Always make sure that you are inserting the address of the source file correctly in the code.
- While inserting the address, you must follow the exact given structure. For example, the file path must be inside a quotation mark.
- If you want to extract only values from an unopened file, use the 2nd method.
Frequently Asked Question
- Why can’t I find the Developer tab?
Perhaps, the Developer tab in your MS Excel is not enabled. We have to enable the Developer tab manually. Alternatively, you can also use the Alt+F11 shortcut to open the Visual Basic window.
- Why I am getting errors while running the 1st code?
Most probably, your reference cell contains a formula with a cell reference. You need to use the 2nd method to extract the values only.
- Can I modify the destination cell where the data is pasted in Example 1?
Yes, you can modify the Destination variable in the code to specify the destination cell where the data will be pasted.
- What types of formatting are preserved in the copied data in Example 1?
This code preserves the number formatting, font formatting, and cell color formatting of the original data.
- Can I modify the codes to copy data from multiple closed workbooks?
Yes, you can modify the codes to loop through a list of file paths and copy data from multiple closed workbooks.
- Can I modify the codes to copy data from a specific worksheet in the closed workbook?
Yes, you can modify the source variable in the 1st code and With ThisWorkbook.Worksheets(2).Range(“B4:E10”) in the 2nd code to specify the name of the worksheet where the data is located in the closed workbook.
Download Practice Workbook
Download these practice books to exercise the task while you are reading this article.
In this article, we have learned how to use Excel VBA get cell value from another Workbook without opening it. I hope from now on you can use the VBA code to get cell values from another Workbook without opening it. However, if you have any queries or recommendations about this article, please leave a comment below. Have a great day!!!