Macro to combine Excel worksheets and keep duplicate rows

[DK5050]

Hello, I'm learning Access right now and it's pretty empowering! The Macro creation system is easy but I need some help to make this work.



I have 5 to 10 Excel .xlsx files in a single folder.
Each Excel file has a Table on the first sheet, with the same headers.
I need to combine these tables into one "Master" table which creates a new "Master" Excel file.

So far, I have only been able to create an Excel Macro which uses multiple external connections and copy/paste, but I want to utilize Access's power to make it a one-click shortcut that anyone could do.
I want the new method to work regardless of how many Excel files are in the source folder.

I need to keep all rows, and all duplicates, all tables. I will combine them based on their left-most column A which is labeled [ID Number].
The reason is because each source file is worked on by a different person. They may have the same [ID Number] in their files by accident. So as a feature, I need to spot this and remove it by consolidating their files.
I know that Access can do multiple outer-joins (and basic Excel VBA needs extensive programming to do so) but I can't develop a macro to work the way I need it to. This needs to be a simple one-click solution.

Sadly, I cannot use "Power Query" for Excel because I am restricted to Internet Explorer 8. I have no option to upgrade to IE 9 or above.

Thank you! I appreciate all input.

[June7]

I don't understand what you want - the thread title says 'keep duplicate rows' but your narrative says 'remove'.

What exactly needs to be 'one-click solution'? Is this a process that will need to be repeated regularly?

If you are wanting to consolidate data and improve efficiency - why have Excel involved at all? If you want to retain Excel and just consolidate multiple sheets into one sheet, why involve Access?

[DK5050]

I don't understand what you want - the thread title says 'keep duplicate rows' but your narrative says 'remove'.

What exactly needs to be 'one-click solution'? Is this a process that will need to be repeated regularly?

If you are wanting to consolidate data and improve efficiency - why have Excel involved at all? If you want to retain Excel and just consolidate multiple sheets into one sheet, why involve Access?

The people who use these Excel files are not tech-savvy. I don't have the time to build them an Access Database either.
We just want an easy way to combine all our files at once, to easily do data analysis and see how things are progressing.

Also, we want to keep duplicate rows (when the same [ID Number] appears in multiple excel files) because that helps us spot and avoid mistakes.

So I made an Excel macro to do it, but they will have to rebuild the macro every time they decide to add/remove Excel files from the folder!

I know that Access can import data from Excel. So here's what I'm really looking to do:

1. Create Access macro
2. Access imports Excel data (table on first sheet of every Excel file) of all Excel .xlsx files in a source folder.
3. Access then joins all this data into one large table
   
   • All tables have same header
   • All duplicate rows are retained (multiple outer joins)
   
   
4. Access then spits out all this data as a single excel table in a new "Master" Excel file .xlsm (to allow macro use)
   
   • Access asks you where to save this file
   
   
5. Access then auto-exits itself

I am puzzled with how to do this using the Access macro-creation wizard.

[June7]

You want code that iterates through files in folder and imports each Excel file into table then exports all to a single Excel sheet?

I don't use macros, only VBA. Macros in Access are very different from 'macros' in Excel. Macros in Excel are really VBA procedure. I doubt Access macros can accomplish what you want, need VBA.

Here is sample code that iterates through files in a folder http://www.allenbrowne.com/ser-59.html

Whether or not developing a programmed solution will be faster than building Access database is dependent on your programming skills.

[DK5050]

Hi, yes I guess that is what I'm looking for.

Your link led to this, it creates an Access table from the iterated files:
http://www.allenbrowne.com/ser-59alt.html

I can then make an Access Macro which runs that code, and then exports the resulting table as an Excel File!

But here's the thing: what data does it pull exactly? All the data?
So it may not work if I just want to pull the first sheet of each Excel file only?

[June7]

I think all that procedure does is create records of the file names in a table, not import data from sheet, whereas the link I referenced listed the file names to a listbox.

You would have to adapt the code so that instead of just grabbing the file name, it runs code to import data from each file. Explore http://www.accessmvp.com/KDSnell/EXCEL_Import.htm

[DK5050]

Hey, that's a great resource, thank you!

That will be a challenge to develop but it would be worth it.
I could also modify it to ask for a source folder full of Excel Files, right?
This is just in-case someone suddenly wants to start shifting files around the shared server.

[June7]

Could use FileDialog for user to navigate to folder or file location. http://support.microsoft.com/kb/824272