Issue with filenames using FileSystemObject

[marrowm]

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

 

[Joel]

When you use the set you have to make the worksheet that is using the range
active.

with workbooks("book2.xls")
.activate
.sheets("sheet2").activate
set workbookrange = .worksheets("sheet2").range("A3:B5")
end with

workbooks("book1.xls").activate

 

[Jim Cone]

You are not incrementing the i variable, so the data is being overwritten.
The modified version of your code (below) adds the line "i = i +1"
just before "End If".
However, if you are just interested in the first file found then replace the
new line with "Exit For".
(code is untested)
'--
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)
With ThisWorkbook.Worksheets("TempWork")
If Filename <> ThisWorkbook.Name And _
StrConv(Right(Filename, 3), vbLowerCase) = "xls" Then
.Range("A" & i) = Filename
.Range("B" & i).Formula = "='" & Filename & "'!PRO_NUM"
.Range("C" & i).Formula = "='" & Filename & "'!CLIENT_NAME"
i = i + 1
End If
End With
Next
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



<[email protected]>
wrote in message
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

 

[marrowm]

Thanks for the feedback - I think I mis-pasted the sample code and
omitted the increment as you (I had to modify to remove sensitive
information).

I fixed my issue as follows: My variable 'filename' was not being
declare explicitly and so was a Variant. For some reason, on
particular files this caused the error I described where the pathname
changes completely. When I declared 'filename' as a String, I did not
get the error.

Weird huh?