Issue with filenames using FileSystemObject
 

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

Issue with filenames using FileSystemObject
 

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



" wrote:

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

Issue with filenames using FileSystemObject
 

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)




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

Issue with filenames using FileSystemObject
 

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?

On Aug 17, 4:41 pm, "Jim Cone" wrote:
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, USAhttp://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)


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