Solved

combine 5 excel files into one worksheet

Posted on 2011-02-14
12
237 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
Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

 
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
 

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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

786 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