Excel VBA: Get Cell Value from Another Workbook without Opening

Get FREE Advanced Excel Exercises with Solutions!

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.

Going to the developer option to select Visual Basic for creating Macro

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.

How to create a module

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.

Folder Address of Source & Destination File

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.

Source File from where will get the cell value without opening

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

Result After Running VBA Code to Copy 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.

VBA Code to get cell value from another workbook without opening

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?

Sub get_cell_value()

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

Copy Destination

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.

Close SaveChanges:=False:

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

2. Get Cell Value from Another Workbook Without Formatting

Result After Running VBA Code to Copy Values 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.

5.2-VBA Code for Copy Values Without Formatting Without Opening

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
📌How Does the Code Work
Sub GetDataFromClosedBook_WO_Formatting()

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,

  1. E:\study\Office\Comments\Get Value From Another Workbook should be replaced by the file path/address of your source file.
  2. Source.xlsm should be replaced by the name of your source file
  3. Sheet1′!$B$4:$E$10 should be replaced by the range that you want to copy from the source file.
With ThisWorkbook.Worksheets(2).Range("B4:E10")

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.

Source File for Referencing

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.

Inserting Formula For Referencing Closed Workbook

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

Manually Selecting Sheet of the Source File

  • As a result, we get the exact value of cell B5 in the Sheet1 of the source Excel file.

Result After Referring Closed 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!!!

Related Articles

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
ASM Arman
ASM Arman

Hi there! I am ASM Arman. I Completed B.Sc. in Naval Architecture and Marine Engineering. I take a great interest in learning about new technologies and sharing my ideas and thoughts on them with others. Please do reach out to me if you have any queries or recommendations. Have a great day!!!

  1. New in VBA, is there anyway to get values from a variable Workbook name (always in same location)?

    • Hello, Larry!
      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.

      Sub GetValues()
      Dim wbName As String
      wbName = ActiveWorkbook.Name
      MsgBox wbName
      End Sub

      If you want to get all the active workbooks’ names you can use this.

      Sub GetValues()
      Dim wbName As Workbook
      For Each wbName In Workbooks
      ActiveCell = wbName.Name
      ActiveCell.Offset(1, 0).Select
      End Sub

  2. I think the explanation leaves out stuff (The workbooks’ names perhaps?). It does not work.

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

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


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

    • Reply Avatar photo
      Bishawajit Chakraborty May 3, 2023 at 10:39 AM

      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

Leave a reply

Advanced Excel Exercises with Solutions PDF