Open files with a loop

Question

In a folder I have different Excel Workbooks like this:

Exercise1
Exercise2
Exercise3
...
ExerciseN

I want to open all files (all .xls) in the folder in the same Excel workbook on different sheets.

I suppose it would be something like this, but not:

Sub Macro1()
    For i = 1 To ?? 
        Workbooks.Open Filename:="C:\Exercisei.xls"
    Next i
End Sub

Examples of how to list and loop through all files of a given type in a specified folder here: http://stackoverflow.com/questions/3017318/vba-getting-list-of-all-files-with-esy-extension-in-a-directory — nekomatic, Feb 13 '15 at 11:42
did you want to copy these worksheets from opened workbooks to the source workbook? You can't just open 2 separate workbooks as 1 workbook - you either copy the spreadsheets or open as 2 separate workbooks. hope that makes sense — , Feb 13 '15 at 12:24

score 3 · Answer 1 · answered Feb 13 '15 at 12:05

This example is for 2 .xls files:

Sub qwerty()
    Dim master As Workbook, awb As Workbook
    Dim N As Long
    Dim s1 As String, s2 As String, i As Long
    N = 2
    Set master = ThisWorkbook
    s1 = "C:\TestFolder\Examplez.xls"

    For i = 1 To N
        s2 = Replace(s1, "z", CStr(i))
        Workbooks.Open Filename:=s2
        Set awb = ActiveWorkbook
        ActiveSheet.Copy after:=master.Sheets(master.Sheets.Count)
        awb.Close
    Next i
End Sub

Each worksheet is copied to the master workbook and then that Example.xls is closed.
You would change the folder name and adjust the number of files to process.

Dubison · Accepted Answer · 2015-02-13T13:02:13.497

2

Following code is a simple sub routine to open all excel files in a defined folder. You can adjust your file names in variable. I have simplified the macro defined here Files in a Directory

Sub openfiles()
Dim directory As String, fileName As String, sheet As Worksheet, i As Integer, j As Integer
Application.ScreenUpdating = False
directory = "c:\test\"
fileName = Dir(directory & "*.xl??") '<-- you can use * and ? wild cards here.
    Do While fileName <> ""
    Workbooks.Open (directory & fileName)
    fileName = Dir()
    Loop
Application.ScreenUpdating = True
End Sub

edited Feb 13 '15 at 13:02

answered Feb 13 '15 at 11:54

Dubison

750
5
12

as far as i can see this only opens workbooks and I think the OP probably wants to not only open the workbooks but copy-paste all the sheets from those opened workbooks to the source workbook. – Feb 13 '15 at 12:23
@vba4all thanks for pointing out. I just read the post again, I think you are right, that was an interesting way to identify the problem :) – Dubison Feb 13 '15 at 12:34
btw the link that I gave includes getting worksheet names from the opened documents. If he wants he can tune that code to copy sheets to his original file. I guess. – Dubison Feb 13 '15 at 12:36
you also forgot to include `Dir` within the cycle to go through all of them anyway... – Raugmor Feb 13 '15 at 12:50
@Raugmor thanks for the feedback. I have updated the code accordingly. Now it is working fine for me. As I mentioned in the post this is a very basic way, may not be the best way to do it. – Dubison Feb 13 '15 at 13:04
[link](http://www.xlorate.com/vba-examples.html#Loop%20Through%20Folder) Use this code as your base to loop through folders, then edit the action each time it opens the workbook. – Davesexcel Feb 14 '15 at 09:29

Open files with a loop

2 Answers2