Solved

append workbooks

Posted on 2011-03-04
81
683 Views
Last Modified: 2012-05-11
I have 5 workbooks with same structure.Each workbook has
1 worksheet
I'd like to append all 5 into 1 worksheet in 1 workbook
0
Comment
Question by:zachvaldez
  • 41
  • 40
81 Comments
 

Author Comment

by:zachvaldez
Comment Utility
I'm looking for VBA code..
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
Ok - I've almost got your code done.

I'll put it in Excel 2003 so others can use...

Dave
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
Here's your solution.  The application appends ALL sheets in the workbooks selected (since you say there's only one, then it will import one, but you have future use of this tool on many sheets, as needed).

Hit the macro button on the attached .xls (Excel 2003) sheet and you'll be prompted to select one to many files.  It will then open each one, then copy over the sheets, closing each one at a time with savechanges = false.

Here's the main code:
Sub ImportAppendWorkbooks()
Dim myWkb As Workbook
Dim myWks As Worksheet
Dim myCell As Range
Dim fname As Variant
Dim fnWkb As Workbook

Application.DisplayAlerts = False
        Set myWkb = ActiveWorkbook
        fname = OpenMultipleFilesFCN(True)
        If IsArray(fname) Then
            For i = LBound(fname) To UBound(fname)
                Workbooks.Open Filename:=fname(i), UpdateLinks:=2, ReadOnly:=1
                Set fnWkb = ActiveWorkbook
                For Each myWks In ActiveWorkbook.Sheets
                    myWks.Copy after:=myWkb.Sheets(myWkb.Sheets.Count)
                Next myWks
                fnWkb.Close savechanges:=False
            Next i
        End If
Application.DisplayAlerts = True
End Sub

Open in new window


And, for fetching the files, I used a source I've long forgotton, but found a reference here:  http://vbadud.blogspot.com/2007/04/browse-folder-select-folder-thru-shell.html and may have made some modifications over time to my use.  Suffice it to say everywhere I looked for the source, folks were sharing the code but not citing the original (apologies to whomever created this neat shared code!)

Dave


ImportAppendWorkbooks-r1.xlsm
0
 

Author Comment

by:zachvaldez
Comment Utility
Is it possible to save the worksheet, as the actual filename. anyway there is only 1 worksheet per workbook
It displays Sheet(1),Sheet(2),...
It would be preferable if the sheets ae named as 3333(1),484894(2)... when it loops
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
Ok - the sheet name will be the FILE NAME_sheet Number, and if sheet number = 1, it will just be the FILE NAME.  That way it will work with 1 or multiple sheets.

How does that sound?

See attached,

Dave
ImportAppendWorkbooks-r2.xlsm
0
 

Author Comment

by:zachvaldez
Comment Utility
That is great! In populating the master workbook, is it possible to just select the columns to go to the master workbook. All these selected columns are identical for all the worksheets that will go to the master.xls
Currently I have 8 columns in each worksheet but only 3 columns are relevant to be in the master.
Is it possible? The loop has to be modified to select these columns. Thanks for your help.
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
Ok - I added a userform to prompt you for columns to copy over.  Select any cells you want, you can use control and shift keys - to get every column you want - they don't have to be contiguous.  E.g., A:C, or A1:B5, D2 (would copy over A:B, and D:D), etc.

Let me know if this works for you.  I did this fairly quickly and there's probably an optimization step I could use, but I got it by copy/paste column by column (really range column area by range column area) going back and forth between source and destination sheet.  I thought I could do it with one command but it didn't like discontigous copy/pastes, so... Anyway, seems to run pretty quick.

If you don't select any columns to copy over, it copies the entire sheet.  Only entire sheet copies will copy over any controls, etc., as opposed to range copies.  Also, unsure how Named Ranges copy over with this approach, but works fine on entire sheet copies.

How does it work for you?

Dave
ImportAppendWorkbooks-r3.xlsm
0
 

Author Comment

by:zachvaldez
Comment Utility
The selections are fix so a prompt is not necessary. Volume 1,4,7 for each worksheet(only 1 wkst per workbook)are fix to go to the master file .
0
 

Author Comment

by:zachvaldez
Comment Utility
meant column not 'volume' in my last post
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
Does it work for you when you select columns 1, 4, 7?  you failed to mention the columns and implied SELECT in your prior post.

Please advise if its functioning for you.

Cheers,

Dave
0
 

Author Comment

by:zachvaldez
Comment Utility
When I click the button,it does not provide the ability to browse for the files. It just display the question for yes or no then the user form. I think we miss the step for that.
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
Select cols on the. Blank workbook with the macro as a template for cols desired then you'll be prompted for the files

Dave
0
 

Author Comment

by:zachvaldez
Comment Utility
Hmmm... Ok I'l try it.
0
 

Author Comment

by:zachvaldez
Comment Utility
I get run time error 1004
0
 

Author Comment

by:zachvaldez
Comment Utility
Location of error

Private Sub cbOK_Click()
Dim rng As Range

    If rCols.Value <> "" Then
 Set rng = Range(rCols.Value) >>> Error 1004 happens here


    End If
    If Not rng Is Nothing Then
        Call ImportAppendWorkbooks(rng.EntireColumn.Address)
    Else
        Call ImportAppendWorkbooks("")
    End If
End Sub
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
That's very interesting.  So what are you selecting?  I'm having a hard time reproducing that error.

After selection, just select what's on the userform and post it here so I can see.

Sorry about that.

Dave
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
Let's try this once more, then I can take a different approach if this is not conceptually working for you.

I've turned events off during the process to avoid any code firing as a result of acting on imported workbooks (just in case :)

Also, if you HOVER over the range selection dialogue box, there's a popup help tip to remind you what to do - select ranges which represent columns to import.

Finally, if the selection is for some reason not readable by the APP, there's more graceful error detection and a popup reminding you what to do to select columns for import.

Dave
ImportAppendWorkbooks-r4.xlsm
0
 

Author Comment

by:zachvaldez
Comment Utility
is it possible to just the set the columns fix to those columns ?
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
Sure - let's do that - that's columns 1, 4 7  - I'll put them in the worksheet so you can change them, but they'll be fixed and no prompting.

Cheers,

Dave
ImportAppendWorkbooks-r5.xlsm
0
 

Author Comment

by:zachvaldez
Comment Utility
r4 worked! Ill test r5. You're the best vba excel expert!
0
 

Author Comment

by:zachvaldez
Comment Utility
Ok that's good, what if I want to add col , 9 and 10 in the mix?
I got an error when i tested it. Which part of code is change to have it adjusted?
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
Just change the yellow cell:

1,4,7,9,10

type it in just like that...

Dave
0
 

Author Comment

by:zachvaldez
Comment Utility
that work again! But normally in any spreadsheet, the first row is a header and data really starts on row 2
0
 

Author Comment

by:zachvaldez
Comment Utility
when I run it on a spreadsheet with data, it produces 3 wkts in the workbook.
eg. name of workhset is 77777
it added 77777_2 , 77777_3
then 77777 and 77777_2 jhave the data identical therefore 77777_2 is duplicate and 77777_3 is blank
0
 

Author Comment

by:zachvaldez
Comment Utility
that work again! But normally in any spreadsheet, the first row is a header and data really starts on row 2 >>> this is taken cared of Just ignore this quesiton.
But I do have concern to the result see above comment
0
 

Author Comment

by:zachvaldez
Comment Utility
Question about filtering. In column 1, there are cells  that are blank. Is it possible to filter it that only the ones with data in col 1 will go to the master file?
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
The Application copies COLUMNS not ROWS.  All Columns with column numbers as indicated. Not sure your comment on rows...

It currently copies all worksheets over, with the columns indicated.

I think you are pushing this question well beyond the original question and for 300 points as well.

However, anything is possible.  Think about what you want.  

Dave
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
Ok, if the first column of a worksheet is empty, none of the worksheet is copied over.


ImportAppendWorkbooks-r6.xlsm
0
 

Author Comment

by:zachvaldez
Comment Utility
I thought a deleting rows routine can be included within the macro.
It may be inconsistent to my original question but I notice certain rows in COlumn A are blank and I  want to exclude them in the import to the master file.
Thanks
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
You want to delete ROWS?  

This is getting confusing.  Please elaborate what you want to do.

Dave
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
If you explain exactly what you want to do, this it should be possible to model...  Please differentiate ROWS from COLUMNS.

My understanding is you want to import worksheets, where you specifiy which COLUMNS to import.  You had stated if you have an EMPTY column A , then you don't want to pull anything over.  Is this all correct as I review the posts, above, it seems so.  Please validate.

THEN, talk to me about rows, on top of all this.

Cheers,

Dave
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
Perhaps if you loaded a sample input file, we can be more productive.

Dave
0
 

Author Comment

by:zachvaldez
Comment Utility
Stated if I have empty or blank entry in Col a,exclude that row in the import
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
Ok - I reverted back.  Now it imports all sheets, after importing it filters out rows where column A row is blank.

See filter code, below.

Dave
Sub filterNonColAData()
Dim mySheet As Worksheet
Dim myRow As Range

    For Each mySheet In Application.Worksheets
        If mySheet.Name <> "Control Panel" Then
            mySheet.Activate
            For Each myRow In Range("A1", Range("A" & Rows.Count).End(xlUp))
                If myRow.Value = "" Then
                    myRow.EntireRow.Delete
                End If
            Next myRow
        End If
    Next mySheet
    Sheets("Control Panel").Activate
End Sub

Open in new window

ImportAppendWorkbooks-r7.xlsm
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
This updated to test for data in Column A - if not, the worksheet is ignored.  That way if you have only data in one sheet, that's all it reads.

How's this working for you?

Dave
ImportAppendWorkbooks-r8.xlsm
0
 

Author Comment

by:zachvaldez
Comment Utility
I'm trying to think what you mean Worksheet is ignored'
All worksheets selected will be imported to the master file but already filtered ('rows with blank in Col A are removed)There is only 1 worksheet in every workbook imported to the master workbook.
0
 

Author Comment

by:zachvaldez
Comment Utility
for some reason, if there are 2 blank cells in a row in col A, it only deletes the first one and leave the second blank in the worksheet and jumps back in the loop.

            For Each myRow In Range("A1", Range("A" & Rows.Count).End(xlUp))
                If myRow.Value = "" Or myRow.Value = Empty Then
                    myRow.EntireRow.Delete
                End If
            Next myRow

I added Empty but still get same results.
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
Perhaps a space?  Look at original source and let me know
0
 

Author Comment

by:zachvaldez
Comment Utility
no space
0
 

Author Comment

by:zachvaldez
Comment Utility
I think it would be a lot more useful if the worksheets are appended since they have all same headers and sructure into 1  worksheet in a masterlist.
0
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

 
LVL 41

Expert Comment

by:dlmille
Comment Utility
Ok.  We've gone from importing spreadsheet tabs, to only certain columns on those tabs, to filtering non-blank rows.

Let's try to get this in one shot...

1)  Import files based on initial selection
2)  Only import the active sheet on those files (as there's only one sheet in each file)
3)  Append each file to the bottom of the last import - such that all input is in one worksheet tab.

Is this correct?

Dave
0
 

Author Comment

by:zachvaldez
Comment Utility
Yep! One workbook , 1 worksheet all files appended to the last worksheet added, since structure and headers are the same.
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
So - no need to just grab certain columns or delete rows - that can be done after all the input, right?

Dave
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
Ok - great - I want you to get an excellent solution and apologies for all the gyrations and my apparent lack of understanding.  When I read the original question, again, I see that you want all in one worksheet, but didn't understand that originally.  And, when we went down the other path (of copying sheets or columns in sheets), I think we went off on a tangent that we both supported :)

Hopefully, you'll find this much better.  I commented out the call to filter based on column A, but you can eliminate that column in the Control Panel code page.

Dave
ImportAppendWorkbooks-r9.xlsm
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
Ok -here's a more cleaned up version, with rows filtered out if no data in column A (searching for "" still).  Only one tab is created and data imported into it.  If you don't clear at the start, it will create a newly named tab so you can have multiple imports on different tabs.

Cheers,

Dave
ImportAppendWorkbooks-r10.xlsm
0
 

Author Comment

by:zachvaldez
Comment Utility
Hi, It istill adding tabs . The masterlist should have only 1 tab and all accumulates to it.
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
Make sure you are running the latest version - as the code only supports one tab add at the beginning and there is no tab adding through all the file imports.

Dave
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
Yep - just checked and v10 does not create additional tabs for each file.  If you import manually one tab at a time it will.  But if you select all the files you want imported at the start, it puts that result all in one tab.

Try my last post - here it is, again.

Dave
ImportAppendWorkbooks-r10.xlsm
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
When you select file - select all of them at once?  Or are you running the macro again and again for each file?

Dave
0
 

Author Comment

by:zachvaldez
Comment Utility
2  observations.

When I select single file, that is when it adds a new tab and does not cumulate to the list.

When I select mulitple tabs, it creates 1 tab like you mention but now since thee are huge vacant rows between files(worksheets) it adds that too. Is there a way it would truncate the blank rows on each worksheet as it is imported so it is nicer to look.
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
When you select one file - it should only add a new tab (there is no cumulation as its only pulling data from ONE sheet as you specified the input sheets have only ONE sheet).  There obviously would be no cumulation would there not?  Its only one tab, right?

When you select multiple FILES? (versus tabs?)  It has a routine to clean blank rows built in after all the imports.

Please update test data showing what your output looks like.

The attached doesn't add tabs at all, the tab is already created.  Run this and send me your output file.

Cheers,

Dave
ImportAppendWorkbooks-r11.xlsm
0
 

Author Comment

by:zachvaldez
Comment Utility
Also when I do mulitiple selects, the headers are imported as well.
So what I got is a worksheet then a huge rows of empty rows, then the next worksheet with header and data and again if I select 3 inputs, a huge rows of empty rows again follow.
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
Eliminating thr row headers on multiple files not a problem


I still need to see a sample output file
0
 

Author Comment

by:zachvaldez
Comment Utility
I think if you can just show me how not to display the headers during the import, I would be Ok. I will just manually remove the blank rows from the source.
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
Ok - only the first file uses the header, all else copy from row 2 to used range of the then active sheet when the file is open.

the fact that it wasn't deleting rows for you could be two fold problem - one looking at only A:A column for rows to delete (could have no data in column A but data in other columns same row, so I'm using Used Range to determine last row.

The second as a result of deleting looking at rows from top down...Needs to be from bottom up!

I changed the filter algorithm to delete from bottom row up as I think that was causing the problem - Excel gets out of whack when you're traversing rows and start deleting in the middle of that - so I create an array of addresses to delete, and after examination of the input sheet, it deletes from the last row up.

Hope this helps!  :)

Dave
ImportAppendWorkbooks-r12.xlsm
0
 

Author Comment

by:zachvaldez
Comment Utility
How come the name range colimport doesn't work. I tried to input the columns to display but does not seem to work?
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
I took that out, as it was getting confusing with all the bells and whistles...

Is everything else working properly, now?

Dave
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
Since everything is now imported, its a simple process to copy desired columns, delete the rest, and paste back.

Please advise if this is working now for you.

Dave
ImportAppendWorkbooks-r12.xlsm
0
 

Author Comment

by:zachvaldez
Comment Utility
Hi, it works when done with processing multiple files selected, however  when trying to append single file(workbook/wkst) to the master list,this is what happens..

1.When I click the button, it ask me to clear import data. If I select 'No', it routes me to select another workbook  file. when I click open, it appends t the newly selected worksheet to the master  BUT
it displays the headers as well along with the 3 columns . HOWEVER,n the previous worksheet that was there already TO WHERE IT WAS APPENDED, the 2 columns dissapeared. Only Column 1 remained.
0
 

Author Comment

by:zachvaldez
Comment Utility
I was thinking of placing a separare button for single file append which make it simpler
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
Or - how about importing all the files you need to import, then hitting the button to delete all columns except those you want?  Headers only on first file import.

This way, you'll see the datasets that are being import, confirm the columns to retain and hit one button to get there.   Make sense?

See attached:

Cheers,

Dave
ImportAppendWorkbooks-r13.xlsm
0
 

Author Comment

by:zachvaldez
Comment Utility
I was trying to build a separate button to just append workheet to the master list as an alternative.
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
If you say, "no" on clean up that's what it does, my friend.

Dave
0
 

Author Comment

by:zachvaldez
Comment Utility
This works after testing. I can append single file to the master too but when the file gets appended to the master list, I have to delete the header becuase it pasted it too.
NO problem with mulit selections. works perfectly on it!
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
I thought I elminated header if you say NO to cleanup.

Let me check.

Dave
0
 

Author Comment

by:zachvaldez
Comment Utility
In mulitple selections, works perfect., but if you select a single file and say 'No' ( to maintain the list) and continue the process, it adds the data but with a header. I think in this way, the pasting  should start from A2...row.
0
 

Author Comment

by:zachvaldez
Comment Utility
what is the Import_Data sheet do with defaults of AAAA,BBBB, etc values. What is the purpose?
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
Nothing - that was just the result of my last test import to make sure my code is working :).  Let it clean up on your first import.

Dave
0
 
LVL 41

Accepted Solution

by:
dlmille earned 500 total points
Comment Utility
Ok - apologies - I needed to set flag to true/false if import data tab is cleared or not - and that determines whether header is posted.

Try this one and advise.

Dave
ImportAppendWorkbooks-r14.xlsm
0
 

Author Comment

by:zachvaldez
Comment Utility
the date fiellds are pasted as values
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
what exactly does that mean?  what is your desire?

Dave
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
As you have never given me input data, either you have to specify more what you want as I can't seem to do the mind reading thing haha!

Dave
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
you would like to paste all data in as formulas?  what's the implication of deleting columns on formulas in your source data?

Dave
0
 

Author Comment

by:zachvaldez
Comment Utility
Will probably just format the source.
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
Oh - you want to paste formats - that's easy.

Is there anything else you want before we close this out?

Dave
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
Ok - attached pastes formats as well as values, but not formulas.

Dave


ImportAppendWorkbooks-r15.xlsm
0
 

Author Comment

by:zachvaldez
Comment Utility
You're the best! But I appreciate more your patience than your superved vba skills which is beyond question
Just to let you know I spent a lot of time going thru and learning your code and techniques and I hope I'll pick it up someday . Thanks amigo!
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
I think you were pretty patient with me as well....

thanks for the points/rating when you're read

Dave
0
 

Author Comment

by:zachvaldez
Comment Utility
BTW this is the farthest thread I ever had in this forum but I thought it was worth the time
0
 

Author Closing Comment

by:zachvaldez
Comment Utility
The expert was very patient and communicated excellently!
0
 

Author Comment

by:zachvaldez
Comment Utility
Id like to export the headers in Upper case if possible
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
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;…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

744 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

8 Experts available now in Live!

Get 1:1 Help Now