Data from closed workbook

[gordsky]

Hi all,

I currently have a line of code which will pull data from a closed workbook without opening it. Its part of a loop that goes through each file in the directory.
The code is

range("A" & i).Formula = "=INDEX('" & MyPath & "\[" & f.Name & "]HrsWrk'!D:D,14,1)"

This works by putting the formula into Cell A(i) which in turn returns the value from the closed book.

My Question is rather than putting the formula into the cell and have that pull the data is it possible to alter the code slightly so that the value goes into A(i) rather than the formula

 

[gitmeto]

Is the number of files in the directory dynamic? How many files are you looking to pull data from?

 

[gordsky]

Is the number of files in the directory dynamic? How many files are you looking to pull data from?

hi yes the number of files is dynamic. The line above is part of a loop which runs through the directory and then it pastes the formula into column a with file name as part of the formula for each file. What i would like is to modif the formula slightly so the value goes into column a rather than a formula to pull the value

 

[Alex Blakenburg]

Try:

range("A" & i).Value = Evaluate("=INDEX('" & MyPath & "\[" & f.Name & "]HrsWrk'!D:D,14,1)")

 

[gordsky]

Try:

range("A" & i).Value = Evaluate("=INDEX('" & MyPath & "\[" & f.Name & "]HrsWrk'!D:D,14,1)")

thanks for the reply, that throws back a #ref in the cell.

 

[Alex Blakenburg]

Hopefully you get some others to buy in, I couldn't get it to work on a "Closed" workbook.
For a quick and dirty solution, just add this line "after" your line as follows:

range("A" & i).Value = range("A" & i).Value

 

[gordsky]

Hopefully you get some others to buy in, I couldn't get it to work on a "Closed" workbook.
For a quick and dirty solution, just add this line "after" your line as follows:

range("A" & i).Value = range("A" & i).Value

thanks for trying, I had already thought of the dirty solution but was hoping to do it another way

 

[RoryA]

You could use:

range("A" & i).Value = Executeexcel4macro("INDEX('" & MyPath & "\[" & f.Name & "]HrsWrk'!C4,14,1)")

Note that you need to use R1C1 referencing

 

[gordsky]

RoryA - thanks however this gives the #ref error also

 

[RoryA]

How long is the file path?