Excel VBA: Getting the Cell Value from Another Workbook without Opening it – 2 Examples

How to Launch the VBA Editor in Excel

  1. Go to the Developer tab.
  2. Select Visual Basic.

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

A new window will open.

  1. Select Insert,
  2. Choose Module.
  3. A new Module will be created.

How to create a module

Note: If the Developer tab is not visible on the ribbon, press Alt + F11 to launch VBA Editor.

You have an Excel workbook containing information about the employees of an organization. Source workbook, here. The source file is stored in “E:\study\Office\Comments\Get Value From Another Workbook\Source.xlsm”.  Create a file, “Destination”, here, where you will copy  cell values.

Folder Address of Source & Destination File

Copy the range B4:E10 in the Source workbook 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


Example 1 – Copying Cell Values with Formatting

Result After Running VBA Code to Copy Values With Formatting

The code below will copy 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

Code Breakdown

Sub get_cell_value()creates a sub-procedure named “get_cell_value”.
ScreenUpdating = Falsedisables the Application.ScreenUpdating to run the background process.
Set Source_workbook = Workbooks.Open("C:\Users\User\OneDrive\Desktop\VBA\Source.xlsx")opens the Source workbook and keeps it in a variable to enable calling it further.
Set Source = Workbooks("Source").Worksheets("Sheet1").Range("B4:E10")Sets the source range.
Set Destination = Workbooks("Destination").Worksheets("Sheet1").Range("B4:E10")Sets the destination range.
Copy Destinationcopies cell values in


 from the


workbook and pastes them in the


Close SaveChanges:=False:closes the source workbook.
ScreenUpdating = True
End Subsets the screen updating to be turned on and ends the current sub-procedure in VBA.

The B4:D10 range from the source file was copied with formatting to the Destination file.


  • 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

Example 2 – Getting the Cell Value from Another Workbook Without Formatting

Result After Running VBA Code to Copy Values Without Formatting

This code copies values without formatting.

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
 Code Breakdown

Sub GetDataFromClosedBook_WO_Formatting()creates a subroutine named


Dim source_data As Stringdeclares 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"

assigns the data location & range to copy to source_data. Here:

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

is the range in the destination file.

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

cell values in the source file are copied to the destination file.

Run the code to see the result.

How to Reference from or Link Value with Unopened/Closed Excel Workbook File Using a Formula

The file path or address of an unopened file is “C:\Users\Aniruddha\Documents\Aniruddah_90\90_0072\Source.xlsm” and you want to refer to B5 of Sheet1 in another Excel file.

Source File for Referencing


  • Go to the formula bar and enter the following formula.

Inserting Formula For Referencing Closed Workbook

Formula Breakown

  • 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 references the 2nd row and 1st column of the array B4:E10 (B5).
  • To manually choose the sheet, enter the formula below.


  • Press Enter.
  • A new window will be displayed. Choose your sheet.
  • Click OK.

Manually Selecting Sheet of the Source File

  • The value of B5 in the Sheet1 of the source Excel file will be displayed.

Result After Referring Closed Excel File


Frequently Asked Question

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

Related Articles

Get FREE Advanced Excel Exercises with Solutions!
ASM Arman
ASM Arman

Abu Saleh Arman is a Marine engineer and Excel & VBA expert. He loves programming with VBA. He finds VBA programming a time-saving tool to manipulate data, handle files, and interact with the internet. He is very interested in Python, MATLAB, PHP, Deep Neural Networks, and Machine Learning, showcasing his diverse skill set. Arman holds a B.Sc in Naval Architecture & Marine Engineering from BUET, Bangladesh. However, he switched to a content developer, where he writes technical content... Read Full Bio

  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 Bishawajit Chakraborty
      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