Retrieve Cell Value from another workbook using a reference cell location

Question

So I've done some digging and found the pieces I think are necessary to achieve my goal but I am having trouble putting the pieces together in the correct order. I have achieved referencing a cell value from a different workbook using its file location and copying that value to my active workbook.

My problem comes when I want that file location to come from a reference cell within my active workbook, NOT from a file location embedded in the code itself. The idea is that other users can simply copy their file location to the reference cell and the macro pulls the necessary values from that file.

Here is my code that works using with the location coming from the code itself

Private Sub CommandButton1_Click()
 Dim XL As Excel.Application

 Dim WBK As Excel.Workbook

 Set XL = CreateObject("Excel.Application")

 Set WBK = XL.Workbooks.Open("R:\Branches\XXX\XXX\XXX\MASTER LOAN MEMO 8-14.xlsx")

 ActiveWorkbook.Sheets("Sheet1").Range("H3") = WBK.Sheets("Master Input").Range("B58").Value

 WBK.Close

 Set XL = Nothing

 End Sub

Here is my modified version to try to accomplish what I explained earlier:

Private Sub CommandButton2_Click()
Dim XL As Excel.Application

Dim WBK As Excel.Workbook

Dim Location As String

Location = ActiveWorkbook.Sheets("Sheet2").Range("B2").Value

Set XL = CreateObject("Excel.Application")

Set WBK = XL.Workbooks.Open(Location)



ActiveWorkbook.Sheets("Sheet1").Range("H3") = WBK.Sheets("Master Input").Range("B58").Value



WBK.Close

Set XL = Nothing
End Sub

I'm getting a Runtime Error 1004 stating the file location cannot be found. Any help would be much appreciated.

Slightly off topic but if you are within Excel's VBA, there is no need to create an Excel.Application. — user4039065
I suspect that the Range("B2").Value is some sort of concatenated string using ampersands and lookup functions. Copy and paste the hard-coded string into Notepad with a non-proportional font like Courier New with Word Wrap off. Paste the value from the constructed string in B2 directly below it. Compare the two. — user4039065
Just for reference, can you post the value that you are putting in the B2 cell? — D_Zab

Dale Matt Dale Matt · Accepted Answer · 2015-01-23T02:19:37

I can't replicate the problem EXCEPT when I use a deliberately bad file location (i.e., a location that doesn't exist in my environment). Since it looks like your directory structure can be many levels deep, and since you're referencing a drive letter instead of a server name, perhaps the location is mis-spelled? Try moving the file you're looking for to your C drive, change the value in Sheet2.B2 accordingly and test that.

BTW, I'm not having problems copying values that have use various functions, ampersands and concatenations.

Retrieve Cell Value from another workbook using a reference cell location

1 Answers