VBA error on copying workbooks from file location

[lleung89]

Hi,

I've written a vba code which essentially should find all excel files in a specific folder location and paste the data onton one master worksheet. The code seems to work until it gets to the latter part of the vba code were there is a runtime error on a specific line. It's been baffling me all morning/afternnon and I'm wondering if you guys have any thoughts as to what I'm doing wrong? The bit of the code I'm having problems with is highlighted in Red.
Sub FileSearch()
Dim iCount As Long
With Application.FileSearch
.NewSearch
.LookIn = "Folder Location"

If .Execute > 0 Then
For iCount = 1 To .FoundFiles.Count


Application.EnableEvents = False
Set importWorkbook = Workbooks.Open(Filename:=.FoundFiles(iCount))
Application.EnableEvents = True
MyName = ThisWorkbook.Name
Sheets("Master Data").Select
MyName = ActiveWorkbook.Name
Range("A2:AQ2000").Select
Selection.Copy

Windows("TEST COPY Master Invoice Queries.xls").Activate

Range("A65536").Select
Selection.End(xlUp).Select

currentrow = ActiveCell.Row


ActiveSheet.Cells(currentrow + 1, 1).Select

ActiveSheet.Cells(currentrow + 1, 6) = MyName

ActiveSheet.Paste

Set importWorkbook = Workbooks.Open(Filename:=.FoundFiles(iCount))

ActiveWorkbook.Close
Windows("TEST COPY Master Invoice Queries.xls").Activate
Sheets("Sheet1").Select
Next iCount
End If
End With
End Sub

[PAB]

Have you tried running the code without the line...


Set importWorkbook = Workbooks.Open(Filename:=.FoundFiles(iCount))

If so, what did it produce?

[lleung89]

Sorry for the late reply,


Have you tried running the code without the line...


Set importWorkbook = Workbooks.Open(Filename:=.FoundFiles(iCount))

If so, what did it produce?

I deleted the line and when I re-runned my macro it came up with another error, upon investigation I deleted the following lines;

[CODE]ActiveWorkbook.Close
Windows("TEST COPY Master Invoice Queries.xls").Activate
Sheets("Sheet1").Select[CODE]

The code works but I'm left with an issue of hundred of opened excel spreadsheets. Now I just need to figure out how to do a loop macro to close the excel spreadsheets when it's been copied

[snb]

You are using Excel 2003 or earlier, because application.filesearch has been removed from Excel since 2007 (9 year ago).

So you'd better get accustomed to more robust methods.
I'd suggest

Assuming the files reside in folder "G:\OF\"


Sub M_snb()
sn=split(createobject("wscript.shell").exec("cmd /c Dir G:\OF\*.xls* /b/s").stdout.readall,vbcrlf)

for j=0 to ubound(sn)-1
with getobject(sn(j))
.saveas "G:\OF\file_" & format(j,"0000") & ".csv" ,24
.close 0
end with
next
createobject("wscript.shell").run "cmd /c copy G:\OF\file_*.csv G:\OF\all.csv"

workbooks.open "G:\OF\all.csv"
End Sub