[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 203
  • Last Modified:

Merge Workbooks into tabs

Hello,
Can you please help.
I'm trying to find a macro that would:

I have about 25 - 50  Excel Workbooks (same folder) that I need to merge into one workbook,
but, I need each Workbooks to be on a separate tab.

Workbooks 1
Workbooks 2
Workbooks 3....

so, I should have 25 tabs to 50 tabs. (Workbooks 1,Workbooks 2,Workbooks 3,...)

Any help is appreciated.
thanks
0
W.E.B
Asked:
W.E.B
1 Solution
 
duncanb7Commented:
Could you turn on macro Recording on develope tab on Excel 2007  or  tools->marco on Excel 2003.  After it enable, You can do what you want  manually on the Excel sheet, and then open VBA to view those macro code that recorded  all what you did on what you want  before such as opening another Excel file, adding tab or adding chart, or drag the cell formual, etc....

Duncan
0
 
W.E.BAuthor Commented:
Hi Duncan,
I have 25-50 Workbooks. (or more)
0
 
C. Blaise MitsutamaCommented:
Wass_QA,

Are the filenames of the 25-50 files sequential (e.g., workbook1, workbook2, etc.) OR do the filenames remain the same (e.g., permafilename, alwaysthesamename, etc.) OR could the filenames change arbitrarily?

Will any of the filenames be longer than 31 characters (tab label limit) or will the first 31 characters of any filenames be the same?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
W.E.BAuthor Commented:
Hello,
They are not sequential,
the file names are less than 30 Characters.

Example
Mark 377
Route 3810
Dennis 340
Run 700

Thank you
0
 
Glenn RayExcel VBA DeveloperCommented:
The following code should do what you request:
Option Explicit
Sub Combine_XL_Files()
    Dim strMaster, strFile, strFileName, strFilePath As String
    
    Application.ScreenUpdating = False
    
    strMaster = Application.ActiveWorkbook.Name
    
    strFileName = Application.GetOpenFilename("Excel Files (*.xls*), *.xls;*.xlsx")
    If strFileName = "" Or strFileName = "False" Then Exit Sub
    strFilePath = Left(strFileName, InStrRev(strFileName, "\"))
    
    strFile = Dir(strFilePath & "\*.xls*")
    Do While strFile <> ""
        Workbooks.Open Filename:=strFilePath & "\" & strFile
        Sheets(1).Copy After:=Workbooks(strMaster).Sheets(Workbooks(strMaster).Sheets.Count)
        ActiveSheet.Name = Left(strFile, InStr(1, strFile, ".xls") - 1)
        Workbooks(strFile).Close SaveChanges:=False
        strFile = Dir
    Loop
    
    Application.ScreenUpdating = True
    Sheets("Main").Select
    MsgBox "Process complete.", vbOKOnly, "Combine Excel Files"
End Sub

Open in new window


It does not check for filename length, nor does it check for any duplicate filenames (possible if you have different Excel versions with the same name: ex., "File1.xls", "File1.xlsx", "File1.xlsm").  However, it should work given your described environment.  

Also, it only copies the first sheet of any workbook. The assumption is that each of the workbooks are single-sheet workbooks and that the resulting sheet tab names will be renamed to the workbook filename.

I've attached a macro-enabled file for your use.

Regards,
-Glenn
EE-CombineXL.xlsm
0
 
W.E.BAuthor Commented:
thank you very much Glen,
this worked,
how do I add .csv file formats?
thanks
0
 
Glenn RayExcel VBA DeveloperCommented:
Do you need to include comma-delimited (CSV) files along with Excel (xls) files?  It would require an additional process.

If you want CSV files instead of XLS files, this code would work:
Sub Combine_CSV_Files()
    Dim strMaster, strFile, strFileName, strFilePath As String
    
    Application.ScreenUpdating = False
    
    strMaster = Application.ActiveWorkbook.Name
    
    strFileName = Application.GetOpenFilename("Comma Delimited Files (*.csv), *.csv")
    If strFileName = "" Or strFileName = "False" Then Exit Sub
    strFilePath = Left(strFileName, InStrRev(strFileName, "\"))
    
    strFile = Dir(strFilePath & "\*.csv")
    Do While strFile <> ""
        Workbooks.Open Filename:=strFilePath & "\" & strFile
        Sheets(1).Copy After:=Workbooks(strMaster).Sheets(Workbooks(strMaster).Sheets.Count)
        ActiveSheet.Name = Left(strFile, InStr(1, strFile, ".csv") - 1)
        Workbooks(strFile).Close SaveChanges:=False
        strFile = Dir
    Loop
    
    Application.ScreenUpdating = True
    Sheets("Main").Select
    MsgBox "Process complete.", vbOKOnly, "Combine Excel Files"
End Sub

Open in new window


I've updated the example workbook with this second macro (and button).

-Glenn
EE-CombineXL.xlsm
0
 
W.E.BAuthor Commented:
Thank you very much.
0
 
Glenn RayExcel VBA DeveloperCommented:
You're welcome.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now