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

 

[gordsky]

RoryA, many thanks for your code. Works perfectly with a slight moderation. I discovered that one of the files in the directory (files are named after staff members) had an apostrophe in the name which appeared to be causing the #ref. Ive modified the code so the line before yours is

mylook = Replace(f.Name, "'", "''")

and then replaced f.name in your code with mylook
really greatful for your help

 

[gitmeto]

Look at Power Query. There is an option to scan the directory which could be loaded into a worksheet. This can be done without opening the workbook.