Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

append workbooks

Posted on 2011-03-04
81
Medium Priority
?
728 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 41
  • 40
81 Comments
 

Author Comment

by:zachvaldez
ID: 35040766
I'm looking for VBA code..
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35041051
Ok - I've almost got your code done.

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

Dave
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35041271
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:zachvaldez
ID: 35058186
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 42

Expert Comment

by:dlmille
ID: 35058704
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
ID: 35065774
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 42

Expert Comment

by:dlmille
ID: 35065974
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
ID: 35068257
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
ID: 35069773
meant column not 'volume' in my last post
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35070048
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
ID: 35070252
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 42

Expert Comment

by:dlmille
ID: 35070466
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
ID: 35070582
Hmmm... Ok I'l try it.
0
 

Author Comment

by:zachvaldez
ID: 35070646
I get run time error 1004
0
 

Author Comment

by:zachvaldez
ID: 35070924
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 42

Expert Comment

by:dlmille
ID: 35071066
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 42

Expert Comment

by:dlmille
ID: 35071351
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
ID: 35071472
is it possible to just the set the columns fix to those columns ?
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35071696
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
ID: 35071867
r4 worked! Ill test r5. You're the best vba excel expert!
0
 

Author Comment

by:zachvaldez
ID: 35071995
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 42

Expert Comment

by:dlmille
ID: 35072025
Just change the yellow cell:

1,4,7,9,10

type it in just like that...

Dave
0
 

Author Comment

by:zachvaldez
ID: 35072322
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
ID: 35072809
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
ID: 35072987
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
ID: 35073353
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 42

Expert Comment

by:dlmille
ID: 35073662
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 42

Expert Comment

by:dlmille
ID: 35073756
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
ID: 35074191
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 42

Expert Comment

by:dlmille
ID: 35074800
You want to delete ROWS?  

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

Dave
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35074839
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 42

Expert Comment

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

Dave
0
 

Author Comment

by:zachvaldez
ID: 35075804
Stated if I have empty or blank entry in Col a,exclude that row in the import
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35076051
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 42

Expert Comment

by:dlmille
ID: 35078795
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
ID: 35081887
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
ID: 35083822
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 42

Expert Comment

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

Author Comment

by:zachvaldez
ID: 35084400
no space
0
 

Author Comment

by:zachvaldez
ID: 35084649
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
 
LVL 42

Expert Comment

by:dlmille
ID: 35085121
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
ID: 35085228
Yep! One workbook , 1 worksheet all files appended to the last worksheet added, since structure and headers are the same.
0
 
LVL 42

Expert Comment

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

Dave
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35085465
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 42

Expert Comment

by:dlmille
ID: 35085611
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
ID: 35086156
Hi, It istill adding tabs . The masterlist should have only 1 tab and all accumulates to it.
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35086254
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 42

Expert Comment

by:dlmille
ID: 35086322
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 42

Expert Comment

by:dlmille
ID: 35086328
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
ID: 35086412
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 42

Expert Comment

by:dlmille
ID: 35086447
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
ID: 35086707
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 42

Expert Comment

by:dlmille
ID: 35086796
Eliminating thr row headers on multiple files not a problem


I still need to see a sample output file
0
 

Author Comment

by:zachvaldez
ID: 35087288
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 42

Expert Comment

by:dlmille
ID: 35089431
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
ID: 35089882
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 42

Expert Comment

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

Is everything else working properly, now?

Dave
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35090018
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
ID: 35090951
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
ID: 35095780
I was thinking of placing a separare button for single file append which make it simpler
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35097340
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
ID: 35098263
I was trying to build a separate button to just append workheet to the master list as an alternative.
0
 
LVL 42

Expert Comment

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

Dave
0
 

Author Comment

by:zachvaldez
ID: 35098517
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 42

Expert Comment

by:dlmille
ID: 35098527
I thought I elminated header if you say NO to cleanup.

Let me check.

Dave
0
 

Author Comment

by:zachvaldez
ID: 35098622
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
ID: 35098708
what is the Import_Data sheet do with defaults of AAAA,BBBB, etc values. What is the purpose?
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35098755
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 42

Accepted Solution

by:
dlmille earned 2000 total points
ID: 35098827
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
ID: 35099844
the date fiellds are pasted as values
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35100076
what exactly does that mean?  what is your desire?

Dave
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35100085
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 42

Expert Comment

by:dlmille
ID: 35100175
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
ID: 35100372
Will probably just format the source.
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35100400
Oh - you want to paste formats - that's easy.

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

Dave
0
 
LVL 42

Expert Comment

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

Dave


ImportAppendWorkbooks-r15.xlsm
0
 

Author Comment

by:zachvaldez
ID: 35100810
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 42

Expert Comment

by:dlmille
ID: 35100973
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
ID: 35102734
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
ID: 35102777
The expert was very patient and communicated excellently!
0
 

Author Comment

by:zachvaldez
ID: 35109518
Id like to export the headers in Upper case if possible
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

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;…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

636 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