combine 5 excel files into one worksheet

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
zachvaldezAsked:
Who is Participating?
 
geoffkkConnect With a Mentor Commented:
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
 
geoffkkCommented:
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
 
zachvaldezAuthor Commented:
no summation is needed. I just need to append the data from each column  and in one worksheet.
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
zachvaldezAuthor Commented:
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
 
geoffkkCommented:
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
 
zachvaldezAuthor Commented:
My 5 files are in a folder .Which part of the code to change pointing tto the filepath?
0
 
geoffkkCommented:
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
 
zachvaldezAuthor Commented:
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
 
zachvaldezAuthor Commented:
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
 
geoffkkCommented:
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
 
zachvaldezAuthor Commented:
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
 
zachvaldezAuthor Commented:
Very well done!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.