Solved

combine 5 excel files into one worksheet

Posted on 2011-02-14
12
233 Views
Last Modified: 2012-06-21
I have 5 workbook with same structure and Id like to combine it into one worksheet.
Can you provide code?
the worksheets are in c:\test folder and Id like to save the results to c:\test2

preferably using vba
0
Comment
Question by:zachvaldez
  • 7
  • 5
12 Comments
 
LVL 2

Expert Comment

by:geoffkk
ID: 34893478
You need to say how you want the data combined, eg is the data summed or should it appear in separate columns? More info is needed.
0
 

Author Comment

by:zachvaldez
ID: 34893574
no summation is needed. I just need to append the data from each column  and in one worksheet.
0
 

Author Comment

by:zachvaldez
ID: 34893632
To make it clearer. I have five workbooks.Each workbook contain a worksheet with similar structure..# of columns. I d like to combine all the workbooks'worksheets into 1 worksheet and save it.
0
 
LVL 2

Expert Comment

by:geoffkk
ID: 34906166
The following code allows you to choose the files to be combined, just multiple select in the file dialog. It then asks for an output file. If the filenames are predetermined you can fiddle with this part but it is OK for showing the method.

The data from each file is appended to the new file. If you want to continue to append you can change the code to use an existing filename and open it instead of creating a new workbook with the chosen filename.

Sub combinefiles()
Dim flist As Variant
Dim wbIN As Workbook
Dim wbOUT As Workbook
Dim outputname As String
Dim fileno As Integer
Dim outrow As Integer
  flist = Application.GetOpenFilename("Excel files,*.xls*", 1, "Select files to combine", , True)
  If IsArray(flist) Then
    outputname = Application.GetSaveAsFilename("c:\test1\CombinedData.xls", "Excel files,*.xls*", 1, "Choose file for combined data")
    If outputname = "FALSE" Then Exit Sub
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Set wbOUT = Workbooks.Add
    With wbOUT.Worksheets(1)
      For fileno = 1 To UBound(flist)
        Set wbIN = Workbooks.Open(flist(fileno))
        wbIN.Worksheets(1).UsedRange.Copy
        outrow = .UsedRange.Rows.Count
        If outrow > 1 Then outrow = outrow + 1
        .Paste .Cells(outrow, 1)
        wbIN.Close
      Next fileno
    End With
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    wbOUT.SaveAs outputname
  End If
End Sub


Geoff
0
 

Author Comment

by:zachvaldez
ID: 34906452
My 5 files are in a folder .Which part of the code to change pointing tto the filepath?
0
 
LVL 2

Expert Comment

by:geoffkk
ID: 34915514
OK, I will assume your 5 files are contained in the folder C:\Test Folder as in the question.
To simplify matters we will assume the files are named file1.xls, file2.xls...file5.xls

You could change the code to read:
Sub combinefiles()
Dim wbIN As Workbook
Dim wbOUT As Workbook
Dim outputname As String
Dim fileno As Integer
Dim outrow As Integer
     outputname = Application.GetSaveAsFilename("c:\test1\CombinedData.xls", "Excel files,*.xls*", 1, "Choose file for combined data")
    If outputname = "FALSE" Then Exit Sub
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Set wbOUT = Workbooks.Add
    With wbOUT.Worksheets(1)
      For fileno = 1 To 5
        Set wbIN = Workbooks.Open("C:\Test Folder\file" & format(fileno) & ".xls")
        wbIN.Worksheets(1).UsedRange.Copy
        outrow = .UsedRange.Rows.Count
        If outrow > 1 Then outrow = outrow + 1
        .Paste .Cells(outrow, 1)
        wbIN.Close
      Next fileno
    End With
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    wbOUT.SaveAs outputname
  End If
End Sub

Replace .xls with .xlsx if using Excel 2007 or later.
If you want a fixed output filename, use
outputname ="C:\test2\Outputfile.xls"
instead of the line outputname=application getsaveas... (or similar name as you need)

Note that I have based this on using a separate workbook containing the VBA code. You could insert a button of the default worksheet and have it run the macro. THere are many other ways which you can work out yourself I guess.

Geoff
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

Author Comment

by:zachvaldez
ID: 34915749
Is it possible to place the VBA code in 1 of the 5 wkst and have it serve as the master. Then at the point of e,I can output in weather folder I may prefer?
0
 
LVL 2

Accepted Solution

by:
geoffkk earned 250 total points
ID: 34924940
Sure, the code can be anywhere, you just have to run the macro. You don't say how the 5 worksheets are created, but if one of them is fixed, just add a module in VBA and insert the code. You can then access the macro via (office 7) Developer tab, Macros, and select the macro or (office 2003) Tools Macros...
The detail of how you manage this is now up to you, make test copies of your files and play around. You can customise to pre-define the output file as in my last post, or make it flexible as in the first post or whatever suits you, I can only provide the basic idea and you need to work out what you want to do with it to suit your particular situation.
Good luck with it
Geoff
0
 

Author Comment

by:zachvaldez
ID: 34925615
You may know this but when I email this to the user and as user opens ita box open saying the level of security for macros to run is set to highand he has to enable it. An instruction  to set digital signatures and so and so

.
What do you think of this messages?
0
 
LVL 2

Expert Comment

by:geoffkk
ID: 34930673
Any workbook with VBA code (macros) will be subject to the macro security settings. The user will need to set their level of macro security to low or medium. If set to medium they will have to click to allow macros when the workbook is opened. If low, then all macros are allowed. This is fine if the user only uses workbooks from trusted sources.

In Office 2003 I think the setting is under Tools/Macros/Security

In Office 2007 the workbook must be saved as a .xlsm file if it contains macros. The setting for macro security is under Trust Centre.

Alternatively you can apply a digital signature to the VBA but this is outside my experience.

Geoff
0
 

Author Comment

by:zachvaldez
ID: 34931028
The digital signatures or certification stuff is beyond my mind. I'm reading in the .net about it and I don't getit. But your solution work! I have to research and ask experts regarding those issues. Thanks a lot!!
0
 

Author Closing Comment

by:zachvaldez
ID: 34931031
Very well done!
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now