Import 8 workbooks in to one workbook with each having thier own sheet

Question

I have 8 workbooks all with one sheet and I'm trying to import them into one master workbook using VBA. This is the code I'm using, it's my first time using VBA.

Sub ImportStats()
    Dim WbDest As Workbook
    Dim wbSource As Workbook
    Dim wsSource As Worksheet
    Dim myPath As String
    Dim strFileName As String

    myPath = ThisWorkbook.path & "\stats\"

    Set weDest = ThisWorkbook
    strFileName = Dir(myPath)
    Do Until strFileName = ""
        Set wbSource = Workbooks.Open(Filename:=myPath & "\" & strFileName)
        Set wsSource = wbSource.Worksheets(1)
        wsSource.Copy after:=WbDest.Worksheets("National2")
        wbSource.Close
        strFileName = Dir()
    Loop
End Sub

Lots on here about this - have a search but see this as an example: https://stackoverflow.com/q/30575923/4961700 — Solar Mike, Dec 20 '20 at 06:35
Think about what paths you are building. Specifically for the Dir(myPath) and the ... .Open — chris neilsen, Dec 20 '20 at 06:38

VBasic2008 · Answer 1 · 2020-12-20T07:48:45.310

Looping Through Files Using `Dir`

Multiple Issues

'*** is indicating modifications of your code.
Although Dir would accept ThisWorkbook.Path & "\stats\", ThisWorkbook.Path & "\stats" (without the trailing backslash) is sufficient, which will also prevent having a double backslash (wrong) when later building the path with myPath & "\" & strFileName) (indicated in chris neilsen's comment).
Set weDest = ThisWorkbook contains a typo and should be Set wbDest = ThisWorkbook. Using Option Explicit at the beginning of each module, will force you to declare all variables and will 'find' these typos immediately.
In the line CurrentIndex = WbDest.Sheets("National2").Index, we are defining the position of the sheet in the tabs. When using the After argument, then when we add a sheet its index will be by one (1) greater than the index of the specified sheet. When we add another one, its index should be by one (1) greater than the previous one (by two (2) greater than the index of the specified sheet)... hence: CurrentIndex = CurrentIndex + 1.
With SaveChanges:=False in wbSource.Close SaveChanges:=False we are preventing Excel to show us a message (when the worksheet was somehow modified (recalculated)), to ask if the workbook should be saved before closing.

The Code

Option Explicit

Sub ImportStats()
    
    Dim WbDest As Workbook
    Dim wbSource As Workbook
    Dim wsSource As Worksheet
    Dim myPath As String
    Dim strFileName As String
    Dim CurrentIndex As Long '***

    myPath = ThisWorkbook.Path & "\stats" '***

    Set WbDest = ThisWorkbook '***
    CurrentIndex = WbDest.Sheets("National2").Index '***

    strFileName = Dir(myPath)

    Do Until strFileName = ""
        Set wbSource = Workbooks.Open(Filename:=myPath & "\" & strFileName)
        Set wsSource = wbSource.Worksheets(1)
        wsSource.Copy After:=WbDest.Sheets(CurrentIndex) '***
        wbSource.Close SaveChanges:=False '***
        CurrentIndex = CurentIndex + 1 ' ***
        strFileName = Dir()
    Loop

End Sub

Import 8 workbooks in to one workbook with each having thier own sheet

1 Answers1

Looping Through Files Using Dir

Looping Through Files Using `Dir`