Script to Open all Excel Files in a Directory

[kpierce]

Hello -

I am needing a script in Excel that I can press a button and it will ask for a folder name, then read through all the XLS files in the folder one by one and step through the individual tabs writing a segment from each tab to another spreadsheet

For example: I have 123.XLS, 456.XLS and 789.XLS all in one folder
123.XLS
Sheet1
Sheet2
Sheet3
456.XLS
SheetA
SheetB
789.XLS
SheetX
SheetY
SheetZ

MACRO/Script would reside in ABC.XLS in another folder so it is not read.

I would like this script to first open 123.XLS, finding it has 3 sheets. I would for example to read A1 and place it back into ABC.XLS, then go to sheet2 and read A1 and place it back into ABC.XLS, then sheet3 and read A1 and place it back into ABC.XLS

Then go to the next XLS file.

I hope this makes sense.

thanks.

 

[AlphaFrog]

Sub Consolidate_Files()

    Dim strFile As String, strPath As String
    Dim wsDest As Worksheet, wbSource As Workbook, ws As Worksheet
    
    Set wsDest = ActiveSheet
    
    With Application.FileDialog(msoFileDialogFolderPicker)
        .Title = "Select Folder"
        .InitialFileName = "C:\Temp\"  ' Default folder
        .Show
        If .SelectedItems.Count Then
            strPath = .SelectedItems(1) & Application.PathSeparator
        Else: Exit Sub  ' user canceled
        End If
    End With
    
    strFile = Dir(strPath & "*.xls*")
    
    Application.ScreenUpdating = False
    Do Until strFile = ""
        Set wbSource = Workbooks.Open(strPath & strFile)
        For Each ws In wbSource.Worksheets
            Application.StatusBar = "Copy from: " & strPath & strFile
            ' Copy from each sheet cell A1 and
            ' paste it to the destination sheet
            ' next empty cell in column A
            ws.Range("A1").Copy _
                wsDest.Range("A" & Rows.Count).End(xlUp).Offset(1)
        Next ws
        wbSource.Close SaveChanges:=False
        strFile = Dir()
    Loop
    Application.StatusBar = "Done"
    Application.ScreenUpdating = True
    
    MsgBox "Consolidation Complete"
    
End Sub

 

[kpierce]

Awesome... Thank you so much.............

 

[kpierce]

This works great except what I would like to write the results into another sheet.

I have tried several things with no luck.

This switches to the "Combined" sheet but it still writes to the other sheet with the command button.

Any thoughts?


set wsDest.ActiviteSheet
Sheets("Combined").Activate

I have all the code you provided into a single 'command button' macro. I don't know if that makes a difference.

thanks again.

 

[AlphaFrog]

Set wsDest = Sheets("Combined")