append workbooks

I'm looking for VBA code..

Ok - I've almost got your code done.

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

Dave

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

Select allOpen 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

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

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

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.

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

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 .

meant column not 'volume' in my last post

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

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.

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

Dave

Hmmm... Ok I'l try it.

I get run time error 1004

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

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

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

is it possible to just the set the columns fix to those columns ?

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

r4 worked! Ill test r5. You're the best vba excel expert!

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?

Just change the yellow cell:

1,4,7,9,10

type it in just like that...

Dave

that work again! But normally in any spreadsheet, the first row is a header and data really starts on row 2

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

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

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?

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

Ok, if the first column of a worksheet is empty, none of the worksheet is copied over.


ImportAppendWorkbooks-r6.xlsm

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

You want to delete ROWS?  

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

Dave

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

Perhaps if you loaded a sample input file, we can be more productive.

Dave

Stated if I have empty or blank entry in Col a,exclude that row in the import

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

Select allOpen in new window

ImportAppendWorkbooks-r7.xlsm

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

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.

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.

Perhaps a space?  Look at original source and let me know

no space

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.

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

Yep! One workbook , 1 worksheet all files appended to the last worksheet added, since structure and headers are the same.

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

Dave

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

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

Hi, It istill adding tabs . The masterlist should have only 1 tab and all accumulates to it.

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

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

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

Dave

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.

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

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.

Eliminating thr row headers on multiple files not a problem


I still need to see a sample output file

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.

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

How come the name range colimport doesn't work. I tried to input the columns to display but does not seem to work?

I took that out, as it was getting confusing with all the bells and whistles...

Is everything else working properly, now?

Dave

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

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.

I was thinking of placing a separare button for single file append which make it simpler

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

I was trying to build a separate button to just append workheet to the master list as an alternative.

If you say, "no" on clean up that's what it does, my friend.

Dave

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!

I thought I elminated header if you say NO to cleanup.

Let me check.

Dave

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.

what is the Import_Data sheet do with defaults of AAAA,BBBB, etc values. What is the purpose?

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

the date fiellds are pasted as values

what exactly does that mean?  what is your desire?

Dave

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

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

Dave

Will probably just format the source.

Oh - you want to paste formats - that's easy.

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

Dave

Ok - attached pastes formats as well as values, but not formulas.

Dave


ImportAppendWorkbooks-r15.xlsm

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!

I think you were pretty patient with me as well....

thanks for the points/rating when you're read

Dave

BTW this is the farthest thread I ever had in this forum but I thought it was worth the time

The expert was very patient and communicated excellently!

Id like to export the headers in Upper case if possible