I have the ff script which ran ok in Excel 2000, but does
not work at a lower version, particularly the part where
it searched for the filename. Can someone please help
me, convert it or totally revise the script?
Thanks

rnum = 1
With Application.FileSearch
.NewSearch
.LookIn = "Z:\GT05\WBG\" & Left(code, 3) & "\"
.SearchSubFolders = True
.FileType = msoFileTypeExcelWorkbooks
.Filename = "*-DeptConsol.xls"
If .Execute() 0 Then
For i = 1 To .FoundFiles.Count
Set mybook = Workbooks.Open(.FoundFiles
(i))
Set sourceRange = mybook.Worksheets
(1).Range("A1:P170")
SourceRcount = sourceRange.Rows.Count
Set destrange = basebook.Worksheets
(1).Cells(rnum, 1)
basebook.Worksheets(1).Cells
(rnum, "Q").Value = mybook.Name
With sourceRange
Set destrange = basebook.Worksheets
(1).Cells(rnum, "A"). _
Resize
(.Rows.Count, .Columns.Count)
End With
destrange.Value = sourceRange.Value

I think you will have to leave out the * and devise a workaround such as


--
Don Guillett
SalesAid Software

"M" wrote in message
...
I have the ff script which ran ok in Excel 2000, but does
not work at a lower version, particularly the part where
it searched for the filename. Can someone please help
me, convert it or totally revise the script?
Thanks

rnum = 1
With Application.FileSearch
.NewSearch
.LookIn = "Z:\GT05\WBG\" & Left(code, 3) & "\"
.SearchSubFolders = True
.FileType = msoFileTypeExcelWorkbooks
.Filename = "*-DeptConsol.xls"
If .Execute() 0 Then
For i = 1 To .FoundFiles.Count
Set mybook = Workbooks.Open(.FoundFiles
(i))
Set sourceRange = mybook.Worksheets
(1).Range("A1:P170")
SourceRcount = sourceRange.Rows.Count
Set destrange = basebook.Worksheets
(1).Cells(rnum, 1)
basebook.Worksheets(1).Cells
(rnum, "Q").Value = mybook.Name
With sourceRange
Set destrange = basebook.Worksheets
(1).Cells(rnum, "A"). _
Resize
(.Rows.Count, .Columns.Count)
End With
destrange.Value = sourceRange.Value