How to Use Reference from Another Excel Workbook Without Opening it – 5 Examples

Example 1 – Use a Reference from Another Workbook Using the Paste Link Option

Step 1:

  • Open the worksheet: Closed.xlsm.
  • Copy B5:C9.

Step 2:

  • Open the other workbook.
  • Go to B5.
  • Right-click.
  • Select Paste Link (N).

Reference from Another Workbook Using the Paste Link Option

Step 3:

Data is pasted.

Step 4:

  • The reference code of C9 is:
=[Closed.xlsm]Sheet1'!C9

Reference from Another Workbook Using the Paste Link Option

Step 5:

  • Close the Closed.xlsm worksheet.
  • The time reference will also change:
='C:\Users\Alok\Desktop\25-0056-1688\[Closed.xlsm]Sheet1'!C9

Reference from Another Workbook Using the Paste Link Option

 

Read More: Link Excel Workbooks for Automatic Update


Example 2 – Use a Reference from a Closed Excel Workbook in a Desktop Folder

Refer Closed.xlsm in Open.xlsm without opening the Closed.xlsm file.

Step 1:

  • Open the Open.xlsm file.
  • Go to B5.
  • Enter the file path: workbook name, sheet name, and cell reference.
  • Enter the formula:
='C:\Users\Alok\Desktop\25-0056-1688\[Closed.xlsm]Sheet1'!B5

Reference from a Closed Excel Workbook in Desktop Folder

Step 2:

  • Press Enter.

Step 3:

  • Drag down the Fill Handle to see the result in the rest of the cells.

Reference from a Closed Excel Workbook in Desktop Folder

 

Step 4:

  • Use the modified formula in B5:
='C:\Users\Alok\Desktop\25-0056-1688\[Closed.xlsm]Sheet1'!B5:C9

Reference from a Closed Excel Workbook in Desktop Folder

Step 5:

  • Press Enter.

The whole data range was referred without opening the worksheet.

Step 6:

  • Enter the code in B5.
='C:\Users\Alok\Desktop\25-0056-1688\[Closed.xlsm]SheetName'!B5:C9

Reference from a Closed Excel Workbook in Desktop Folder

Step 7:

  • Press Enter.
  • The available sheets of Closed.xlsm are displayed.
  • Choose a sheet.

Reference from a Closed Excel Workbook in Desktop Folder

Step 8:

  • Click OK.

 


Example 3 – Reference a Closed Excel Workbook from the Cloud

Step 1:

  • Sample.xlsm worksheet is saved in One drive. Refer this workbook to another workbook.
  • Copy B5:C9.

Reference from a Closed Excel Workbook from the Cloud

Step 2:

  • Go to the destination workbook.
  • In B5, right-click.

Step 3:

  • Click Paste Link(N).

Reference from a Closed Excel Workbook from the Cloud

Copied data is pasted.

Step 4:

  • Close the Sample.xlsm workbook located in One drive.
  • Get the reference of C9:
='https://d.docs.live.net/03e01967881debf5/Softeko/25-0056-1688/[Sample.xlsm]Sheet'!C9

Reference from a Closed Excel Workbook from the Cloud

 

Read More: How to Link Two Workbooks in Excel


Example 4 – Use a Defined Name to Refer to Another Workbook

Step 1:

  • Define the name of the source data.
  • Go to Formulas.
  • Choose Define Name in the Define Name drop-down.

Use Defined Name for Reference from Another Workbook

Step 2:

  • Name the data and select the cell range.
  • Click OK.

Use Defined Name for Reference from Another Workbook

Step 3:

  • Close the source file and go to the destination file.
  • In B5, enter the code:
='C:\Users\Alok\Desktop\25-0056-1688\[Closed.xlsm]Sheet1'!Fruit

Use Defined Name for Reference from Another Workbook

Step 4:

  • Press Enter.


Example 5 – Apply a VBA Macro to Refer to a Workbook Without Opening It

Step 1:

  • Open the destination file.
  • Go to the Developer tab.
  • Choose Record Macro.
  • Name the macro as Referencedata.
  • Click OK.

Apply VBA Macro to Refer to a Workbook Without Opening It

Step 2:

  • Enter the code in the module.
Sub Importdata1()
Dim AreaAddress As String
Sheet1.UsedRange.Clear
Sheet1.Cells(1, 1) = "= 'C:\Users\Alok\Desktop\25-0056-1688\" & "[Closed.xlsm]Sheet2'!RC"
AreaAddress = Sheet1.Cells(1, 1)
With Sheet1.Range(AreaAddress)
.FormulaR1C1 = "=IF('C:\Users\Alok\Desktop\25-0056-1688\" & "[Closed.xlsm]Sheet1'!RC="""",NA(),'C:\Users\Alok\Desktop\25-0056-1688\" & _
"[Closed.xlsm]Sheet1'!RC)"
On Error Resume Next
.SpecialCells(xlCellTypeFormulas, xlErrors).Clear
On Error GoTo 0
.Value = .Value
End With
End Sub

Apply VBA Macro to Refer to a Workbook Without Opening It

Step 3:

  • Press F5 to run the code.

Only data will be imported from the referred workbook, the format will not be copied.


Problems with Referencing Multiple Excel Workbooks

1. Referred Data Location may Change

When a closed workbook changes its location, the referred workbook loses reference.

2. Sub-links not Updated Promptly

Do not refer multiple workbooks to each other to avoid incorrect updates.

3. Data from Previous Versions is not Retrieved

Data can only be retrieved from the last saved version of a file.


Download Practice Workbook

Download the practice workbook.

Source File:

Destination File:


 

Related Articles

<< Go Back To Linking Workbooks in Excel | Linking in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Alok Paul
Alok Paul

Alok Paul has completed his B.Sc. in Electronics and Telecommunication Engineering from East West University. He has been working on the ExcelDemy project for more than 2 years. He has written 220+ articles and replied to numerous comments. He is experienced in Microsoft Office, especially in Excel. He also led some teams on Excel and VBA content development. He has a keen interest in Advanced Excel, Data analysis, Excel Pivot Table, Charts, and Dashboard. He loves to research... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo