Opening and importing contents from several xls workbooks into one 2

[belstoy]

Hello,

I need a code to open several xls workbooks and import the data from all those workbooks into one xls template (with a row between each set of data).

File names are similiar, but could be a different number of files each run. See File names below:

RPT_BRANCH_663
RPT_BRANCH_731
RPT_BRANCH_551

Thanks,
Belstoy

 

[xlbo]

So what have you tried that doesn't work?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

"with a row between each set of data"

Why would you want to destroy a perfactly good table of data by having empty rows in it? That is not very good table design.

You need to rethink your process and consider adding a column to identify the SOURCE workbook.

Those empty rows will bite you.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[belstoy]

The design of the template is per a client's specs.

There needs to be an empty row between each branch's data in excel.

On the most basic level, I need to know how to open several files, no matter how many there may be, without having to identify them by their exact name.

 

[SkipVought]

Based on WHAT? Something like a Folder path?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[belstoy]

Yes, the files would all be in the same folder and there would be no other files in the folder.

 

[PHV]

Have a look at the Dir function.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

Try something like this. Put in your folder path...

Sub test()
    Dim fs, f1, ws As Worksheet, lRow As Long
    
    Set ws = ActiveSheet
        
    Set fs = CreateObject("Scripting.FileSystemObject")
    For Each f1 In fs.getfolder("C:\Documents and Settings\ii36250\My Documents\_Download\_TEST").Files
        Application.DisplayAlerts = False
        With Workbooks.Open(f1.Path)
            lRow = ws.UsedRange.Rows.Count + ws.UsedRange.Row + 1
            .Sheets(1).UsedRange.Copy
            ws.Cells(lRow, 1).PasteSpecial xlPasteAll
            .Close
        End With
        Application.DisplayAlerts = True
    Next
End Sub

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[belstoy]

Worked like a charm!! Thanks Skip. Brilliant!