Hi,

I have some code (below) that is supposed to take each file in the
current directory and create some cells in a worksheet based on
specific ranges within those files.

If you follow the code, I am expecting to see the filename itself in
column A of the worksheet TempWork and the a formula to show the value
of the range CLIENT_NAME from that file in column B.

When it works, I see something like this:

Cell A1 has the value mymysteryclient.xls
Cell A2 has the formula ='C:\MyFolder\mymysteryclient.xls'!
CLIENT_NAME

When it fails, I get an error message "Run-time error '-2147417848
(80010108)': Method 'Formula' of object 'Range' failed".

When I debug and look at the values in the cells, I see this:

Cell A1 has the value myotherclient.xls
Cell A2 has the value ='C:\DOCUME~1\username\LOCALS~1\Temp\Temporary
Directory 1 for zipfilecorrespondingtomycurrentfile.zip
\myotherclient,xls'!CLIENT_NAME

Now the temporary folder does not exist and I am not loading my Excel
spreadsheet from that temporary directory.

Any ideas what is going on?

Alternatively, is there any way within VBA to set a local variable to
the value of the range in the other spreadsheet? There's no real
reason why I need the reference formula - I just need the value.

Thanks
UKMatt

Set fso = CreateObject("Scripting.FileSystemObject")
Pathtxt = ThisWorkbook.Path
Set sourceFolder = fso.GetFolder(Pathtxt)
i = 1

For Each Fls In sourceFolder.Files
FileName = CStr(Fls.Name)
If FileName < ThisWorkbook.Name And StrConv(Right(FileName, 3),
vbLowerCase) = "xls" Then
ThisWorkbook.Worksheets("TempWork").Range("A" & i) = FileName

ThisWorkbook.Worksheets("TempWork").Range("B" & i).Formula =
"='" & FileName & "'!PRO_NUM"
Worksheets("TempWork").Range("C" & i).Formula = "='" &
FileName & "'!CLIENT_NAME"
End If
Next