Thread: Macro for sheet importing working differently in 2007 vs 2003

Need an extra set of eyes. I helped an OP in this thread:
http://www.excelforum.com/excel-gene...le-import.html

And he's getting no results, only getting a filename pasted into his activeworkbook. He's on Excel 2007 and I'm using 2003.

Here's the macro and it works fine for me, importing all the files in one folder and pasting them one above the other in the activeworkbook:

Code:

Sub RunCodeOnAllXLSFiles()
Dim lCount As Long, NR As Long
Dim wbResults As Workbook, wbCodeBook As Object

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False
On Error Resume Next

Set wbCodeBook = ThisWorkbook.Sheets(1)
NR = Range("A" & Rows.Count).End(xlUp).Row + 1

With Application.FileSearch
    .NewSearch
     'Change path to suit
    .LookIn = "C:\My Computer\My Documents"
    .FileType = msoFileTypeExcelWorkbooks
    .Filename = "VT" & "*.xls"

        If .Execute > 0 Then 'Workbooks in folder
            For lCount = 1 To .FoundFiles.Count 'Loop through all.
                'Open Workbook x and Set a Workbook variable to it
                Set wbResults = Workbooks.Open(Filename:=.FoundFiles(lCount), UpdateLinks:=0)
                'Copy data
                wbResults.Sheets(1).Range("A1").CurrentRegion.Copy
                wbCodeBook.Range("A" & NR).PasteSpecial xlPasteAll
                wbResults.Close SaveChanges:=False
                NR = Range("A" & Rows.Count).End(xlUp).Row + 1
             Next lCount
        End If
End With

On Error GoTo 0

Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
End Sub

I'm just looking for anything that might cause this to not work in his environment since I can't test it.

JBeaucaire,

If you can get the Op to post a copy of a data file, I will test your code in Excel 2007.

The OP said he could not, but the problem he's having is that it isn't reading the sheets anyway, it's just pasting in a sheet name. I actually saw that same thing once early on on setting up the macro, but it stopped doing that when I included the ws reference in the copy command.

On my system, ALL the files I put in the designated directly get read into the activeworkbook and stacked one on top of the other.

In fact, the VT* filter isn't actually working right, but that's ok since he said the folder would be only the files he wants read in anyway.

If you could test it with just any scrap files, it should work indiscriminately. Thanks for checking it out.

Application.FileSearch has been deprecated in XL2007.

Originally Posted by DonkeyOte

Application.FileSearch has been deprecated in XL2007.

Ouch! Tough call...why take it out? Did they give 2007 users something better?

I don't know the ins and outs as to why it was deprecated but if you google you will find lots and lots of disgruntled users and subsequent workarounds... there are no doubt some here too.

The link below has a workaround c/o Nate Oliver (MS Excel MVP):

http://www.mrexcel.com/forum/showthr...228168#1253882