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.
Download Practice Workbook
Download these practice books to exercise the task while you are reading this article.
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. If you don’t have that, you can look for it in how to enable the Developer tab 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.
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.
- Excel VBA: R1C1 Formula with Variable (3 Examples)
- How to Reference Text in Another Cell in Excel (14 Ways)
- Excel VBA Examples with Cell Reference by Row and Column Number
- [Fixed] F4 Not Working in Absolute Cell Reference in Excel (3 Solutions)
- Excel VBA: Insert Formula with Relative Reference (All Possible Ways)
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.
Read More: Cell Reference in Excel VBA (8 Examples)
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. To enable the Developer tab, follow this article. 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.
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!!!
- Different Types of Cell References in Excel (With Examples)
- Difference Between Absolute and Relative Reference in Excel
- Absolute Reference in Excel (With Examples)
- Excel Sheet Name in Formula Dynamic (3 Approaches)
- Reference Another Sheet in Excel (3 Methods)
- How to Lock a Cell in Excel Formula (2 Ways)
New in VBA, is there anyway to get values from a variable Workbook name (always in same location)?
To get the values from a variable workbook name you can use this code. This will show the variable workbook name in a Msg Box.
Dim wbName As String
wbName = ActiveWorkbook.Name
If you want to get all the active workbooks’ names you can use this.
Dim wbName As Workbook
For Each wbName In Workbooks
ActiveCell = wbName.Name
I think the explanation leaves out stuff (The workbooks’ names perhaps?). It does not work.
We apologize for the inconvenience. We are aware of the missing source reference and are working on updating the explanation. Further, we recommend using the code given before the explanation. Thank you for bringing this to our attention.
Might be useful for some newbies, since it (somewhat) does the job. However, very misleading — both in title and in first paragraph, you promise to show how to get values from another workbook *without opening the same* but your code does just that: open the second workbook, copy(?!) the source cell to the destination cell, then close that second workbook.
Moreover, you state that the job is to copy the *value* of the source cell. Copying the source cell may give you unexpected results, because the target cell will have all the properties of the source cell (e.g. formula, formatting etc.). And if it contains a formula, copying it will most likely create a link to the source workbook, which Excel will annoyingly ask to update each time the destination workbook is opened.
If one only wants to get the value, the correct code would be:
Workbooks(“Destination”).Worksheets(“Sheet1”).Range(“B2:E10”).value = Workbooks(“Source”).Worksheets(“Sheet1”).Range(“B2:E10”).value
I hope this will put you on the right path, and maybe persuade you to post instructional code only after seriously improving your Excel vba skills. I arrived on this page looking for a fresh idea onto how to really get values without opening the second workbook (for an already established idea, google for ExecuteExcel4Macro), and ended up instead by writing this advice :))
Hi Fake Hidden,
Thanks for your suggestions. We updated our article so that it fulfills the need of all types of readers. You may check our article now and let us know any kinds of suggestions.
Hi.. it’s amazing, many thanks.
But there’s one more thing I need to know and assistance from you.
If I protected the source file, is the data still can be got from it?
Thanks in advance.
Thank you, FAJAR, for your wonderful question.
Here is the explanation to your question.
It is still possible for someone to obtain the cell value from another workbook without opening it using Excel VBA even if the source file is password- or other security-protected.
However, depending on the situation, accessing a protected file in this manner might be regarded as unethical or unlawful. It’s crucial to respect the security precautions taken by the file’s owner and to only access the data through legitimate, approved methods. Additionally, it is important to keep in mind that Excel offers a variety of protection options, and the efficacy of the protection depends on the particular technique used. While some security measures can be easily bypassed with a basic understanding of VBA, others are more robust and call for sophisticated techniques.
Because of this, it’s crucial to carefully examine the degree of protection needed for your unique use case and implement the proper security measures in accordance.
I hope this may solve your issue.
Bishawajit, on behalf of ExcelDemy