CODEWITHSUNDEEP
excelvbamergefilepath
Ryan S
Ryan S

Reputation: 55

Select Multiple Workbooks, Merge First tab in each workbook to NEW tabs in same workbook, Rename tab with Filename

Goal:

  1. Select multiple workbooks with file finder (working)
  2. Consolidate first Sheet in each workbook to NEW tabs in the same workbook (Error)
  3. Rename new tabs with the Filename of the workbook it came from (working)

Sources:

I've scoured the internet looking at different code and haven't found one that has done all of this, I'm sure its out there somewhere. I have found a couple of different Stack pages that are similar and have tried to combine them.

  1. VBA script to consolidate multiple excel sheets into one sheet

  2. Combine first sheet of multiple workbooks into one workbook

Full Code:

Sub OpeningFiles()

Dim SelectedFiles As FileDialog
Dim NumFiles As Long, FileIndex As Long
Dim TargetBook As Workbook
Dim Path As String

'prompt user to select a file or multiple files
Set SelectedFiles = Application.FileDialog(msoFileDialogOpen)

With SelectedFiles
    .AllowMultiSelect = True
    .Title = "Pick the files you'd like to consolidate:"
    .ButtonName = ""
    .Filters.Clear
    .Filters.Add ".xls files", "*.xls"
    .Show
End With

'check to see if user clicked cancel
If SelectedFiles.SelectedItems.Count = 0 Then Exit Sub

'start the loop over each file
NumFiles = SelectedFiles.SelectedItems.Count
For FileIndex = 1 To NumFiles

'set a reference to the target workbook
Set TargetBook = Workbooks.Open(SelectedFiles.SelectedItems(FileIndex))

'consolidate
    Do While TargetBook <> ""
        Set Path = TargetBook.Path
        Workbooks.Open TargetBook:=Path & TargetBook, ReadOnly:=True
        With ActiveWorkbook
            .Worksheets(1).Copy after:=ThisWorkbook.Sheets(1)
            ThisWorkbook.Sheets(2).Name = .Name
        End With
        Workbooks(TargetBook).Close
        TargetBook = Dir()
    Loop
    
    TargetBook.Close SaveChanges:=False
Next FileIndex


End Sub

Issue:

Do While TargetBook <> ""
    Set Path = TargetBook.Path
    Workbooks.Open TargetBook:=Path & TargetBook, ReadOnly:=True

Something is wrong here when I combined the codes and I'm not sure what. I think I need to reference the file path of the selected files, but I am not sure how to do that the way this code is set up. I am getting an object required error on Set Path. I'm not even sure if I need to do that or if there is some other way to structure Workbooks.Open TargetBook:=Path & TargetBook, ReadOnly:=True where it doesn't need the path object.

Upvotes: 2

Views: 635

Answers (2)

Tim Williams
Tim Williams

Reputation: 166521

From @SJR's comment: like this -

'...
NumFiles = SelectedFiles.SelectedItems.Count
For FileIndex = 1 To NumFiles

    'open workbook for a "With" block
    With Workbooks.Open(SelectedFiles.SelectedItems(FileIndex))
        .Worksheets(1).Copy after:=ThisWorkbook.Sheets(1)
        ThisWorkbook.Sheets(2).Name = .Name
        .Close False 'no save
    End With

Next FileIndex
'...

Upvotes: 1

Ryan S
Ryan S

Reputation: 55

For anyone who may stumble upon this later, here is the working code:

Sub OpeningFiles()

Dim SelectedFiles As FileDialog
Dim NumFiles As Long, FileIndex As Long
Dim TargetBook As Workbook
Dim Path As String

'prompt user to select a file or multiple files
Set SelectedFiles = Application.FileDialog(msoFileDialogOpen)

With SelectedFiles
    .AllowMultiSelect = True
    .Title = "Pick the files you'd like to consolidate:"
    .ButtonName = ""
    .Filters.Clear
    .Filters.Add ".xls files", "*.xls"
    .Show
End With

'check to see if user clicked cancel
If SelectedFiles.SelectedItems.Count = 0 Then Exit Sub

'start the loop over each file
NumFiles = SelectedFiles.SelectedItems.Count
For FileIndex = 1 To NumFiles

'set a reference to the target workbook
Set TargetBook = Workbooks.Open(SelectedFiles.SelectedItems(FileIndex))

'consolidate
        With TargetBook
            .Worksheets(1).Copy after:=ThisWorkbook.Sheets(1)
            ThisWorkbook.Sheets(2).Name = .Name
        End With
   
    TargetBook.Close SaveChanges:=False
Next FileIndex


End Sub

Upvotes: 0

Related Questions