James Altenbach
asked on
Retrieving data from a closed workbook
My problem is that I don't know how to reference a closed workbook when the path dynamically changes but the file, worksheet, and cell location are consistent. A hard-coded example of what I'm trying to do is here:
Sheet4.Cells(1, 22) = "= 'P:\Laser\Service Reports\L2100\CT&R\" & "[Collect Tool & result.xlsm]Report'!B4" 'this works when path is hard-coded
HOWEVER...my path will change. I know what the path is through a variable that I define prior to the line of code above. The path location will be known with variable strFolder. Put simply, I'm looking to do something like below, but it errors out.
Sheet4.Cells(2, 22) = strFolder & "[Collect Tool & result.xlsm]Report'!B4"
Is there a method to do this?
Thank You in advance
Sheet4.Cells(1, 22) = "= 'P:\Laser\Service Reports\L2100\CT&R\" & "[Collect Tool & result.xlsm]Report'!B4" 'this works when path is hard-coded
HOWEVER...my path will change. I know what the path is through a variable that I define prior to the line of code above. The path location will be known with variable strFolder. Put simply, I'm looking to do something like below, but it errors out.
Sheet4.Cells(2, 22) = strFolder & "[Collect Tool & result.xlsm]Report'!B4"
Is there a method to do this?
Thank You in advance
In the statement with the fixed path, you are putting a formula in the cell. In the statement with the variable path, you are putting a string in the cell.
You might want to try:
I also suggest assigning the formula to a string variable before putting it in the cell. That way, you can easily inspect the text of the formula in the Debugger if there is a runtime error.
You might want to try:
Sheet4.Cells(2, 22).Formula = "=" & strFolder & "\[Collect Tool & result.xlsm]Report'!B4"I also suggest assigning the formula to a string variable before putting it in the cell. That way, you can easily inspect the text of the formula in the Debugger if there is a runtime error.
ASKER
I get a "Run-time error '1004' error with your suggestion.
what is the contents of StrFolder? if it is only the path then you want to make sure it includes the trailing backslah, and the "=" sign at the start.
basically get your strFolder to contain this
= 'P:\Laser\Service Reports\L2100\CT&R\
= 'P:\Laser\Service Reports\L2100\CT&R\
ASKER
My strFolder already looks like 'P:\Laser\Service Reports\L2100\CT&R\
ASKER
With Martin's suggestion I get the line below pasted into the cell, instead of the contents of cell B4 of said worksheet.
'P:\Laser\Service Reports\L2100\CT&R\\[Colle
'P:\Laser\Service Reports\L2100\CT&R\\[Colle
you need the leading = sign so it is accepted as a formula, and not a string
You need the equals sign.
Sheet4.Cells(2, 22).Formula = "=" & strFolder & "[Collect Tool & result.xlsm]Report'!B4"ASKER
Even with the = sign, I get the Run-time error. My exact line of code is below:
Sheet4.Cells(2, 22).Formula = "=" & strFolder & "[Collect Tool & result.xlsm]Report'!B4"
Sheet4.Cells(2, 22).Formula = "=" & strFolder & "[Collect Tool & result.xlsm]Report'!B4"
Try storing the formula in a string variable, then use Debug.Print to display it in the Immediate Window. Compare that to the hard-coded formula. And if they look identical, check for things like non-breaking spaces (ASCII 160) versus regular spaces (ASCII 32)
I see in your previous post you have a double bacckslash in your strFolder (right after CT&R)
you should have only one
\CT&R\\
\CT&R\
you should have only one
\CT&R\\
\CT&R\
ASKER
From the Immediate window using Debug.Print...
=P:\Laser\Service Reports\L2100\CT&R\[Collec
Looks OK, I believe...
=P:\Laser\Service Reports\L2100\CT&R\[Collec
Looks OK, I believe...
I'm not completely following this thread any more but shouldn't there be quotes?
="P:\Laser\Service Reports\L2100\CT&R\[Collec
="P:\Laser\Service Reports\L2100\CT&R\[Collec
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Wow, thank you all so much! Finally got it with the code below. I had to add the apostrophe in quotes between the = and strFolder words.
Sheet4.Cells(1, 22).Formula = "=" & "'" & strFolder & "[Collect Tool & result.xlsm]Report'!B4"
Thanks again!
Sheet4.Cells(1, 22).Formula = "=" & "'" & strFolder & "[Collect Tool & result.xlsm]Report'!B4"
Thanks again!
this would have the same results
Sheet4.Cells(1, 22).Formula = "='" & strFolder & "[Collect Tool & result.xlsm]Report'!B4"
notice the appostrophe is together with the = sign
Sheet4.Cells(1, 22).Formula = "='" & strFolder & "[Collect Tool & result.xlsm]Report'!B4"
notice the appostrophe is together with the = sign
ASKER
Ah yes, thanks again. I'm still a bit new to this and learning much every day.
I added the backslash since I assume strFolder doesn't have one.
or
Sheet4.Cells(2, 22) = strFolder & "\" & "[Collect Tool & result.xlsm]Report'!B4"