Thread: Excel File Import

So how does that data IN these workbooks relate? Writing any macro to import data will require thinking through HOW the data from each workbook will be analyzed.

1) Name of workbooks?
2) Type of data in each book and how it's supposed to be found and correlated?
3) How the data in the Summary workbook should layout?
4) Is this a one-time event or does the macro need to be able to run again and spot information it already has...and update/ignore when it finds matches?

Easiest would be if you uploaded:
A) A sample of the desired layout of this summary workbook you're trying to create
B) Enough sample workbooks to demonstrate all the various ways the other books might appear, enough to give enough info that an approach can be figured for finding and collating the data appropriately.

Click GO ADVANCED and use the paperclip icon to upload those sample docs.

You don't mind the sheets being merged headers and all, yes?

What's the path to the files?

Any chance you'll upload a couple of sample files to make it easy to verify?

Try this then. Be sure to correct the path variable colored in red:

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

This will cycle through all the files in that folder, though i put in the VT filter, probably safest if you don't have any extra files in there.

Pasted it where? Make sure it's in a standard module. (Insert > Module)

What is the final path you used? Perhaps you need the final "\" on it, although it works for me either way.

I setup 3 sheets in a folder and it imports them all.

What is the window you pasted the code into called? (you're killing me here...)

Wow...a pdf. Isn't Book1 an empty spreadsheet? Could just upload that...

Anyway...You have TWO Sub titles, takeout the "Sub Test()" entry...and if there are two "End Sub" entries at the bottom fix that, too.

Hm...maybe add the final \ at the end of the Lookin path.

Make sure Book1 is active, then in the VBEditor, use F8 to scroll through the code one line at a time...when you get to "PasteAll" line, F8 past it and then switch to the active book and see if it pasted anything.

I just ran the macro several more times, only dif is the folderpath and it imported every file in the folder.