Reference from Another Excel Workbook Without Opening (5 Examples)

Get FREE Advanced Excel Exercises with Solutions!

Microsoft Excel has a wide range of applications for our day-to-day use. One of them is to refer to any cell or range. We can refer to cells or ranges in the same sheet or other sheets, or another workbook. When we refer from one workbook to another, we should be very careful. But referring to one workbook without opening that to another workbook is very tricky. We have to ensure some vital information of that closed file. So, this article is all about using the reference from another workbook without opening it in Excel.


How to Use Reference from Another Workbook Without Opening It: 5 Methods

Here, we will discuss some methods to reference any workbook without opening that file in Excel.


1. Reference from Another Workbook Using the Paste Link Option

Here, we will show how to reference another workbook using the paste link.

Step 1:

  • First, open the worksheet that will remain close named Closed.xlsm.
  • Then copy the required cells.
  • Now, we copy the range B5 to C9.

Step 2:

  • Then, open the other workbook.
  • Go to Cell B5.
  • Click the right button on the mouse.
  • Select Paste Link (N).

Reference from Another Workbook Using the Paste Link Option

Step 3:

  • Now, we can see that data is pasted into our desired cells.

Step 4:

  • Now, we see the reference code of Cell C9. That is:
=[Closed.xlsm]Sheet1'!C9

Reference from Another Workbook Using the Paste Link Option

Step 5:

  • Now, close the Closed.xlsm worksheet.
  • And at that time reference will also change accordingly. That is:
='C:\Users\Alok\Desktop\25-0056-1688\[Closed.xlsm]Sheet1'!C9

Reference from Another Workbook Using the Paste Link Option

This is how we can reference one worksheet and then close that worksheet.

Read More: Link Excel Workbooks for Automatic Update


2. Reference from a Closed Excel Workbook in Desktop Folder

Here, we will show if the reference workbook is saved on the local computer.

We will refer Closed.xlsm file on Open.xlsm without opening the Closed.xlsm file.

Here, we need to manually input the reference.

Step 1:

  • First, open the Open.xlsm file.
  • Now, go to Cell B5.
  • We need to input the file path, workbook name, sheet name, and cell reference here.
  • Here, we input the below formula:
='C:\Users\Alok\Desktop\25-0056-1688\[Closed.xlsm]Sheet1'!B5

Reference from a Closed Excel Workbook in Desktop Folder

Step 2:

  • Then press Enter.

Step 3:

  • Now, pull the Fill Handle icon to the last cell.

Reference from a Closed Excel Workbook in Desktop Folder

We can see that the rest of the cells are filled with data from the closed worksheet.

We can also do this by using a range in the formula.

Step 4:

  • Now, we apply the modified formula in Cell B5.
  • The formula is:
='C:\Users\Alok\Desktop\25-0056-1688\[Closed.xlsm]Sheet1'!B5:C9

Reference from a Closed Excel Workbook in Desktop Folder

Step 5:

  • Again, press Enter.

In this way, we can enter a whole of data without opening the worksheet.

In one more situation, we may forget the sheet name when entering the formula. For that purpose, we have a solution.

Step 6:

  • We will enter the below code on Cell B5.
='C:\Users\Alok\Desktop\25-0056-1688\[Closed.xlsm]SheetName'!B5:C9

Reference from a Closed Excel Workbook in Desktop Folder

Step 7:

  • Then press Enter.
  • Now, the available sheets of file Closed.xlsm is showing.
  • Choose the desired sheet.

Reference from a Closed Excel Workbook in Desktop Folder

Step 8:

  • Now, press OK.

Those are some processes that we can refer to any workbook without opening that file.


3. Reference from a Closed Excel Workbook from the Cloud

We may need to refer to any workbook file from the cloud in another workbook. In this section, we will discuss this topic. When we refer to any file from the local computer there is a problem that is if we change the location of the file the reference will not work. But when we add any reference from the cloud workbooks this problem will not occur.

Step 1:

  • This Sample.xlsm worksheet is saved in One drive. We will refer this workbook to another workbook.
  • Copy the Range B5:C9.

Reference from a Closed Excel Workbook from the Cloud

Step 2:

  • Now, go to the destination workbook.
  • In Cell B5, click the right button on the mouse.

Step 3:

  • Then click the Paste Link(N).

Reference from a Closed Excel Workbook from the Cloud

Copied data is pasted into the selected cells.

Step 4:

  • Now, close the Sample.xlsm workbook located on One drive.
  • Now, get the reference of Cell C9 and that is:
='https://d.docs.live.net/03e01967881debf5/Softeko/25-0056-1688/[Sample.xlsm]Sheet'!C9

Reference from a Closed Excel Workbook from the Cloud

Here, we referred to a workbook saved on the cloud.

Read More: How to Link Two Workbooks in Excel


4. Use Defined Name for Reference from Another Workbook

In this section, we will show how to refer to any workbook using the defined name.

Step 1:

  • First, define the name of the source data.
  • Go to the Formulas tab.
  • Then choose the Define Name option from the Define Name drop-down.

Use Defined Name for Reference from Another Workbook

Step 2:

  • Now, we give a name and select the cell range.
  • Then press OK.

Use Defined Name for Reference from Another Workbook

Step 3:

  • Now, close the source file and enter the destination file.
  • Go to Cell B5 and manually input the below code:
='C:\Users\Alok\Desktop\25-0056-1688\[Closed.xlsm]Sheet1'!Fruit

Use Defined Name for Reference from Another Workbook

Step 4:

  • Then press Enter.

Here, we get data from the closed workbook using the defined name reference.

Read More: How to Create Reference with Another Workbook in Excel


5. Apply VBA Macro to Refer to a Workbook Without Opening It

We can also use VBA macro to refer to any workbook without opening that file.

Step 1:

  • Open the destination file.
  • Go to the Developer tab.
  • Then choose Record Macro.
  • Named the macro as Referencedata.
  • Then press OK.

Apply VBA Macro to Refer to a Workbook Without Opening It

Step 2:

  • Write the below code on the command 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:

  • Then press F5 to run the code.

Here, one thing needs to mention only data will be imported from the referred workbook, the format will not be copied.


Problems with Referencing Multiple Excel Workbooks

Here we will discuss some problems with referencing workbooks.

1. Referred Data Location may Change

When a closed workbook changes its location, the referred workbook does not have any idea of the modified location. After that, if any changes are placed in the source workbook they will not reflect on the destination file.

2. Sub-links not Updated Promptly

If multiple workbooks are referred to each other then the situation becomes complicated. Like workbook 1 is referred to as workbook 2; Workbook 2 is referred to as workbook 3. Then the update of workbook 1 will not reflect on workbook 3 properly.

3. Data from Previous Version is Retrieved

Data can be retrieved only from the last saved version of a file. If you change the source file data but do not save that data will not be shown on the destination. After saving the file the changes will be shown.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

Source File:

Destination File:


Conclusion

In this article, we how to reference from another workbook without opening it in Excel. I hope this will satisfy your needs. Please give your suggestions in the comment box.


Related Articles

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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
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