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

How to Launch the VBA Editor in Excel

• Go to the Developer tab.
• Select Visual Basic.

A new window will open.

• Select Insert,
• Choose Module.
• A new Module will be created.

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.

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.

Example 1 – Copying Cell Values with Formatting

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

 workbook.

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

This code copies values without formatting.

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()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"

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

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

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

Steps:

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

• To manually choose the sheet, enter the formula below.

• Press Enter.

A new window will be displayed. Choose your sheet.

• Click OK.

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

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

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