Solved

Merge multiple Workbooks into one with marco

Posted on 2011-03-24
22
460 Views
Last Modified: 2012-05-11
Hello,

I have searched the previous questions and see there is multiple ways of doing this, so not sure of the simplest way for my project. I completed my project to collect time for each employee. We have 6 supervisors that will collect about 10-15 workbooks for their department. I want to find the easiest way for them to take the 10-15 workbooks and combine into 1 workbook that they can forward to payroll. The supervisors are not very savy in excel, so my thought was to use a macro to help them accomplish this.

Each workbook is identical in structure. Just different names and times they worked.
As far as what gets copied over, I can copy the entire worksheet or just a range. I would like to keep the page format and values, but dont' care about formulas if I don't need them. Example for attached worksheet range would be A1:L75.

I tried recording a macaro and use move/copy sheet, but it won't work because each worksheet has a different name.

expert-idea-for-merge.xls
0
Comment
Question by:bvanscoy678
  • 11
  • 10
22 Comments
 
LVL 41

Expert Comment

by:dlmille
ID: 35209341
Let's focus on the problem statement:

1.  You'll have 10-15 workbooks in a directory
2.  You want to combine those together into one driver workbook.  How do you want to import?  All sheets, or specific sheets?
3.  You want to be able to specify the entire worksheet or just a range - with just a range is that to be in one worksheet or separate worksheets.

You're pretty broad in your need and if you can provide some clarification, the scope of your requestion would be better understood.

I've just finished 2 similar requests that went on for a while because these types of questions weren't answered up front.

So think about it and provide additional clarificaiton as needed, with a focus on my 3 points, above.

Dave
0
 
LVL 41

Expert Comment

by:dlmille
ID: 35209352
Additional question - do you want to select the files that get imported, or are they all contained in one directory?

Dave
0
 
LVL 41

Expert Comment

by:dlmille
ID: 35209435
Here's a starting point from a prior question.  This one prompts the user for specific columns to import, and if nothing is selected then it brings the entire sheet in for every sheet found in the files selected.

Let me know what changes from here you'd like as you consider the questions I've posed.

Dave
ImportAppendWorkbooks-v1.xlsm
0
 
LVL 41

Expert Comment

by:dlmille
ID: 35209468
It would be easy enough to prompt for a range to import - but one sheet, all sheets?

Finally, this second option merges one file on top of another, all sheets.

So the possibilities are endless, please respond with your clarified scope and we'll wrap this up.

Cheers,

dave
ImportAppendWorkbooks-v2.xlsm
0
 

Author Comment

by:bvanscoy678
ID: 35209480
Hi Dave,

There is just one worksheet per workbook (I am going to delete the instruction worksheet that is in the attached example). So, just the one worksheet.
It can be the entire worksheet, I don't have to worry about the range, but if it makes the file smaller, the range would be a1:az100

I would like to bring over the entire worksheet as it is in the invidivual books., formatting et al.

I would guess that picking the files would be the best choice. Most of the people will save the workbooks into a folder per pay period.

I hope this detail helps.
thanks
0
 
LVL 41

Expert Comment

by:dlmille
ID: 35209524
My first post - ImportAppendWorkbooks-V1 is closest to that.  Please give it a try and see if you like the output.  It prompts for an import column range, but you can just not fill that out - hit OK and see what happens.

It will open each file selected, and bring the sheets over with a pasteall

Dave
0
 

Author Comment

by:bvanscoy678
ID: 35209581
I am looking at examples now.
0
 

Author Comment

by:bvanscoy678
ID: 35209601
I am using excel version 2003 at work. I got a warning that active X controls will not be opened.

when I click on the macro, nothing happens.
0
 

Author Comment

by:bvanscoy678
ID: 35209631
Okay, I got it to work via the macro, not the button. I am going to make a few copies and test.
0
 

Author Comment

by:bvanscoy678
ID: 35209686
Okay, I have a command button, but no macro assigned.
I see 3 separate macro's.

open multiple file
open single file
show_browse_directory_dialog.

Not sure how to proceed
0
 

Author Comment

by:bvanscoy678
ID: 35209719
actually there are 2 modules that contains several macro's.

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.

 
LVL 41

Expert Comment

by:dlmille
ID: 35209889
Here's a 2003 version of the same

Dave
ImportAppendWorkbooks-v1.xls
0
 
LVL 41

Expert Comment

by:dlmille
ID: 35209898

I got rid of the active-x control and added the forms control and this code behind it, in the main module

Sub Button3_Click()
    Call ClearWorksheets
    Load UserForm1
    UserForm1.Show
End Sub
0
 

Author Comment

by:bvanscoy678
ID: 35209903
Okay. I put it on my laptop to see it.

I put 3 worksheets into a folder. Selected them. It made all 3 sheets, plus and extra sheet (named it same as one with _4).

Then I got an "out of range error". It showed a bug with
sheets("control Panel"). Activate

This is what I was looking for. If I could skip the first step of asking what range, that would be perfect.

Not sure how to get it to work in excel 2003, since that is what my work has.

Thanks
0
 
LVL 41

Accepted Solution

by:
dlmille earned 500 total points
ID: 35209964
Done - changes requested completed and now - this should work in Excel 2003 as well.

Dave
ImportAppendWorkbooks-v2.xls
0
 

Author Comment

by:bvanscoy678
ID: 35210027
Yes, this worked just fine. I took the files, then renamed with with an _2.

Can I edit it so it does not asked to clear all non control panel sheets? I will always want the entire sheet.

Thank you very much for the help.
0
 
LVL 41

Expert Comment

by:dlmille
ID: 35210089
the prompt asks if you want to clear all worksheets that are present at the time you run the macro.  Are you going to NEVER delete what you've imported?

The prompt has nothing to do with whether you want to import entire sheets - that's built in.  This is just housecleaning to say, do you want me to start from scratch, or should I just keep adding tabs?

Get it?

Dave
0
 

Author Comment

by:bvanscoy678
ID: 35210227
Dave,

I really do appreciate the help. I like learning on EE, even when it is over my head like this project. Obviously I don't know much at all about code or what this does.

So, the information about the prompt and what it does is very helpful. Plus, knowing that it is a house cleaning explains what it is doing.

The little jab about starting from scratch and adding tabs, well not needed.

Not sure what I did to deserve the sarcism, but I do apologize.

Again, Thanks for the help.

Brent
0
 
LVL 41

Expert Comment

by:dlmille
ID: 35210269
My apologies - I wasn't even attempting to be sarcastic - it was a bad choice of words.  Not a jab at all.

No need to apologize - its all my bad.  Unlink most posts, I just said "try this" with no explanation - so that wasn't very good of me....

Let me rectify a bit...

Ok - the app first prompts for housecleaning, then prompts for files to select - if you hold ctrl key, you can select multiple files for import.

It opens each workbook for copy - turning off any links - then copies all sheets into the driver workbook (where the macro is running), naming those sheets after the file where they were imported. then closes that workbook.

That's it.

Here's the primary code:

 
Sub ClearWorksheets()
Dim xMsg As Integer
Dim myWks As Worksheet

Application.DisplayAlerts = False

    xMsg = MsgBox("Clear all non Control Panel Sheets?", vbYesNo, "Hit No to preserve existing sheets")
    If xMsg = vbYes Then
        For Each myWks In ActiveWorkbook.Sheets
            If myWks.Name <> "Control Panel" Then
                myWks.Delete
            End If
        Next myWks
    End If
    
Application.DisplayAlerts = True
End Sub
Sub ImportAppendWorkbooks(sRng As String)
Dim myWkb As Workbook
Dim myWks As Worksheet
Dim myCell As Range
Dim fname As Variant
Dim fnWkb As Workbook
Dim shtCnt As Integer
Dim xMsg As Integer
Dim myTempWks As Worksheet

Application.DisplayAlerts = False

        Set myWkb = ActiveWorkbook
        fname = OpenMultipleFilesFCN(True)
        Application.EnableEvents = False 'to prevent event handling by full sheet copies importing sheet code over
        If IsArray(fname) Then
            For i = LBound(fname) To UBound(fname)
                Workbooks.Open Filename:=fname(i), UpdateLinks:=2, ReadOnly:=1
                Set fnWkb = ActiveWorkbook
                shtCnt = 1
                For Each myWks In ActiveWorkbook.Sheets
                    If sRng <> "" Then
                        myWkb.Activate
                        Set myTempWks = Worksheets.Add(after:=myWkb.Sheets(myWkb.Sheets.Count))
                        For Each r In Range(sRng).Areas
                            fnWkb.Activate
                            Range(r.Address).Copy
                            myWkb.Activate
                            Range(r.Address).PasteSpecial xlPasteAll
                            Application.CutCopyMode = False
                        Next r
                        myWks.Activate
                    Else
                        myWks.Copy after:=myWkb.Sheets(myWkb.Sheets.Count)
                    End If
                    
                    myWkb.Activate
                    If shtCnt = 1 Then
                        ActiveSheet.Name = Left(Left(fnWkb.Name, InStr(fnWkb.Name, ".x") - 1), 27) 'max sht name 31 chars, leaving rooom for sheet number
                    Else
                        ActiveSheet.Name = Left(Left(fnWkb.Name, InStr(fnWkb.Name, ".x") - 1), 27) & "_" & shtCnt
                    End If
                    shtCnt = shtCnt + 1
                Next myWks
                fnWkb.Close savechanges:=False
            Next i
        End If
        Application.EnableEvents = True
        Sheets("Control Panel").Activate

Application.DisplayAlerts = True
End Sub

Open in new window


Please let me know where I can further assist.

Dave
0
 
LVL 50

Expert Comment

by:Dave Brett
ID: 35212307
While this is closed you may want to look at my article here, http://www.experts-exchange.com/A_2804.html which provides a method (and explanation) to either

i. Collate all sheets from all Excel workbooks in a single folder into a single summary worksheet
ii. Collate all sheets from all Excel workbooks in a single folder into a single summary workbook
iii. Collate all sheets from a single Excel workbook into a single summary worksheet

Cheers

Dave
0
 

Author Comment

by:bvanscoy678
ID: 35212391
Okay, I will look back at the code. Even though I am not very familiar, every little bit helps. If I use someone's code I at least like to
try and figure it out, so if something goes wrong, I can pick through it.

No biggie, I just wanted to make sure I did not do anything wrong. Tough day at work, probably just took it wrong.

Sorry. No worries.

thanks for the help.

Brent
0
 
LVL 41

Expert Comment

by:dlmille
ID: 35212403
No problem and my pleasure.  Please don't hesitate to ask follow-up questions.

and have a better day tomorrow :)

Dave
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Lync meeting or Lync conferencing is what many organizations would like to deploy to allow them save money. But companies are now giving up for various reasons, one of which is that they cannot join external meetings (non-federated company meetings)…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

743 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

15 Experts available now in Live!

Get 1:1 Help Now