Get cell value from external file 1

[Divercem]

I have a series of Daily planner files that I am opening and copying the value of specific cells ($K$49 in this case represents # of hours worked) into a spreadsheet for comparison and analysis.

When I use the following snippet of code the first example puts the formula in the cell which results in data, but when I use Sum() on the hours shown I get a total of Zero. The 2nd example results in a Error 2023.

I am not a programmer, so I may not have included enough information in this post. But any help, suggestions or solutions would be most appreciated.

Thanks,

Charlie

----------------Example 1--------------------------
WorkbookName = "='" & Mid(Filename, 1, X - 1) & "[" & _
Mid(Filename, X, Len(Filename) - X + 1) & "]Charlie'"
Filename_Intime = WorkbookName & "!$K$49"
...
Range(Cells(DP_Row, Col_In), Cells(DP_Row, Col_In)).Formula = Filename_Intime

Results from Watch:
WorkbookName = "='G:\Cmills\Excel\Daily Planners\2004\May 2004\[Daily Planner 2004-05-14.xls]Charlie'"

Filename_Intime = "='G:\Cmills\Excel\Daily Planners\2004\May 2004\[Daily Planner 2004-05-14.xls]Charlie'!$K$49"

----------------Example 2--------------------------
WorkbookName = "'" & Mid(Filename, 1, X - 1) & "[" & _
Mid(Filename, X, Len(Filename) - X + 1) & "]Charlie'!$K$49"
Filename_Intime = Evaluate(WorkbookName1)
...
Range(Cells(DP_Row, Col_In), Cells(DP_Row, Col_In)).Value = Filename_Intime

Results from Watch:
WorkbookName = "'G:\Cmills\Excel\Daily Planners\2004\May 2004\[Daily Planner 2004-05-14.xls]Charlie!'$K$49"

Filename_Intime : Error 2023

 

[Bong]

Instead of "evaluate", why not use ".value"?
Also, you've defined "WorkbookName" but not "WorbookName1".

_________________
Bob Rashkin

 

[Divercem]

Bong, thanks for your response.

Very observant ) My apologies...In the 2nd example WorkbookName1 is correct. Where I messed up was I changed all instances of WorkbookName to WorkbookName1 after I copied it to the clipboard but I failed to change it here after I pasted it.

I have tried using the .value. I've tried referencing "WorkbookName.Value" and I tried "Range(Cells(DP_Row, Col_In), Cells(DP_Row, Col_In)).Value = Filename_Intime.Value", both resulted in a runtime error of 424.

I've gotten it this far by brute forcing it, so I admit some of my methods are crude, but any help or change of approach is appreciated. I'd be happy to send the code and email it if it would be helpful to see what I'm trying to do.

Thanks again

 

[PHV]

What about this (in example 1) ?
With Range(Cells(DP_Row, Col_In), Cells(DP_Row, Col_In))
.Formula = Filename_Intime
.Value = Val(.Value)
End With

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Divercem]

Thanks PHV! I'm not sure why this worked, but I can live with it.

Diver