Excel VBA: Get Cell Value from Another Workbook without Opening

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.

1. First, we go to the Developer tab.
2. Then we will select Visual Basic.

Then a new window will pop up. Then we are going to follow these steps,

1. First, we will select Insert,
2. Then we are going to select Module.
3. 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

Sub get_cell_value()

ScreenUpdating = False

Set Source_workbook = Workbooks.Open("C:\Users\User\OneDrive\Desktop\VBA\Source.xlsx")

Set Source = Workbooks("Source").Worksheets("Sheet1").Range("B4:E10")

Set Destination = Workbooks("Destination").Worksheets("Sheet1").Range("B4:E10")

Copy Destination

Close SaveChanges:=False:

ScreenUpdating = True
End Sub

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.

📌Notes:

• 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: Excel VBA Set Cell Value in Another Worksheet

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

Sub GetDataFromClosedBook_WO_Formatting()

Dim source_data As String

source_data = "='E:\study\Office\Comments\Get Value From Another Workbook\[Source.xlsm]Sheet1'!$B$4:$E$10"

With ThisWorkbook.Worksheets(2).Range("B4:E10")

.Formula = source_data
'Taking only values
.value = .value

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.

Steps:

• Go to the formula bar of a cell in the workbook where you want to put the reference and type the following formula.

• 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.

Conclusion

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!!!

Related Articles

• How to Get Cell Value as String Using Excel VBA
• How to Get Cell Value by Row and Column in Excel VBA
• Excel VBA: Read Cell Value into Variable