Excel VBA: Get Cell Value From Another Workbook Without Opening

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

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

B4:E10

from the

Source

workbook and pastes them in the

Destination

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

Sub GetDataFromClosedBook_WO_Formatting()creates a subroutine named

GetDataFromClosedBook_WO_Formatting

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.

Steps:

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

=INDEX('C:\Users\Aniruddha\Documents\Aniruddah_90\90_0072\[Source.xlsm]Sheet1'!$B$4:$E$10,2,1)

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.

=INDEX('C:\Users\Aniruddha\Documents\Aniruddah_90\90_0072\[Source.xlsm]SheetName'!$B$4:$E$10,2,1)

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

Destination.xlsm

Source.xlsm

Related Articles

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

Get FREE Advanced Excel Exercises with Solutions!