Why do I get the error? Oddly enough, I can use the commented line for the
debug.print, but when I try to assign to an array it gives the error.

I'm totally confused.

Bob

Dim FName as Variant
Set fs = Workbooks.Application.FileSearch
With fs
.LookIn = "E:\Stott\Inventory"
'.FileType = msoFileTypeExcelWorkbooks
.FileName = "Input??.xls"
.Execute
End With

For i = 1 To fs.FoundFiles.Count
FName(i) = fs.FoundFiles(i)
'Debug.Print fs.FoundFiles(i)
Next i

Hi

You need to redim your array after the End With statement:

Redim fName(1 to fs.foundfiles.count)

Hopes this helps.
....
Per


On 15 Jul., 22:55, Bob Zimski
wrote:
Why do I get the error? Oddly enough, I can use the commented line for the
debug.print, but when I try to assign to an array it gives the error.

I'm totally confused.

Bob

Dim FName as Variant
Set fs = Workbooks.Application.FileSearch
* *With fs
* * * .LookIn = "E:\Stott\Inventory"
* * * '.FileType = msoFileTypeExcelWorkbooks
* * * .FileName = "Input??.xls"
* * * .Execute
* *End With

* *For i = 1 To fs.FoundFiles.Count
* * * FName(i) = fs.FoundFiles(i)
* * * 'Debug.Print fs.FoundFiles(i)
* *Next i

I tried it and it just gave a new error 'Subscript out of range.'
Like I eluded to earlier, if I commented out the line where I try to assign
FName and uncomment the debug.print, the debug print works and shows three
values as expected. While when I try to do the assignment it gives the Type
Mismatch Error 13.

Still a mystery to me.

Bob

"Per Jessen" wrote:

Hi

You need to redim your array after the End With statement:

Redim fName(1 to fs.foundfiles.count)

Hopes this helps.
....
Per


On 15 Jul., 22:55, Bob Zimski
wrote:
Why do I get the error? Oddly enough, I can use the commented line for the
debug.print, but when I try to assign to an array it gives the error.

I'm totally confused.

Bob

Dim FName as Variant
Set fs = Workbooks.Application.FileSearch
With fs
.LookIn = "E:\Stott\Inventory"
'.FileType = msoFileTypeExcelWorkbooks
.FileName = "Input??.xls"
.Execute
End With

For i = 1 To fs.FoundFiles.Count
FName(i) = fs.FoundFiles(i)
'Debug.Print fs.FoundFiles(i)
Next i

Hi

This worked for me:

Dim FName As Variant
Set fs = Workbooks.Application.FileSearch
With fs
.LookIn = "E:\Stott\Inventory"
'.FileType = msoFileTypeExcelWorkbooks
.Filename = "Input??.xls"
.Execute
End With
ReDim FName(1 To fs.FoundFiles.Count)
For i = 1 To fs.FoundFiles.Count
FName(i) = fs.FoundFiles(i)
Debug.Print fs.FoundFiles(i)
Next i

Regards,
Per

"Bob Zimski" skrev i meddelelsen
...
I tried it and it just gave a new error 'Subscript out of range.'
Like I eluded to earlier, if I commented out the line where I try to
assign
FName and uncomment the debug.print, the debug print works and shows three
values as expected. While when I try to do the assignment it gives the
Type
Mismatch Error 13.

Still a mystery to me.

Bob

"Per Jessen" wrote:

Hi

You need to redim your array after the End With statement:

Redim fName(1 to fs.foundfiles.count)

Hopes this helps.
....
Per


On 15 Jul., 22:55, Bob Zimski
wrote:
Why do I get the error? Oddly enough, I can use the commented line for
the
debug.print, but when I try to assign to an array it gives the error.

I'm totally confused.

Bob

Dim FName as Variant
Set fs = Workbooks.Application.FileSearch
With fs
.LookIn = "E:\Stott\Inventory"
'.FileType = msoFileTypeExcelWorkbooks
.FileName = "Input??.xls"
.Execute
End With

For i = 1 To fs.FoundFiles.Count
FName(i) = fs.FoundFiles(i)
'Debug.Print fs.FoundFiles(i)
Next i

Hi

Don't know why it worked for me now and not before. Must have been a brain
freeze.

Thanks much.

"Per Jessen" wrote:

Hi

This worked for me:

Dim FName As Variant
Set fs = Workbooks.Application.FileSearch
With fs
.LookIn = "E:\Stott\Inventory"
'.FileType = msoFileTypeExcelWorkbooks
.Filename = "Input??.xls"
.Execute
End With
ReDim FName(1 To fs.FoundFiles.Count)
For i = 1 To fs.FoundFiles.Count
FName(i) = fs.FoundFiles(i)
Debug.Print fs.FoundFiles(i)
Next i

Regards,
Per

"Bob Zimski" skrev i meddelelsen
...
I tried it and it just gave a new error 'Subscript out of range.'
Like I eluded to earlier, if I commented out the line where I try to
assign
FName and uncomment the debug.print, the debug print works and shows three
values as expected. While when I try to do the assignment it gives the
Type
Mismatch Error 13.

Still a mystery to me.

Bob

"Per Jessen" wrote:

Hi

You need to redim your array after the End With statement:

Redim fName(1 to fs.foundfiles.count)

Hopes this helps.
....
Per


On 15 Jul., 22:55, Bob Zimski
wrote:
Why do I get the error? Oddly enough, I can use the commented line for
the
debug.print, but when I try to assign to an array it gives the error.

I'm totally confused.

Bob

Dim FName as Variant
Set fs = Workbooks.Application.FileSearch
With fs
.LookIn = "E:\Stott\Inventory"
'.FileType = msoFileTypeExcelWorkbooks
.FileName = "Input??.xls"
.Execute
End With

For i = 1 To fs.FoundFiles.Count
FName(i) = fs.FoundFiles(i)
'Debug.Print fs.FoundFiles(i)
Next i