How to import data from multiple Excel files to one Excel file

**rafeemd** · 11-11-2011, 03:35 AM

Hi All,

I Have an urgent task, I am a new learner of Excel VBE.

I have mupltiples of Excel files having one sheet.

What i need to do it is
when the user clicks on the button
the data will be imported from a folder where the files are stored basing on one filed having curent date.

The records which are having current date will be copied to the current file.

I am attaching the sample excel file. all files are like this.

Thanks a lot in advance.

**JBeaucaire** · 11-11-2011, 11:15 AM

'WORKBOOKS TO 1 SHEET STACKED
Here's a macro for collecting data from all files in a specific folder.

Workbooks to 1 Sheet

The parts of the code that need to be edited are colored to draw your attention.

This macro will do an "import all data" process. We can tweak that to filter the source workbooks before copying in.

TO be clear, you want a macro to:

1) Open a file in a specific folder
2) Filter the data based on the dates in column E, filtering for today's date
3) Any rows with today's date are then copied into the master file being compiled
4) Close the file and repeat with all files in that same folder

**rafeemd** · 11-12-2011, 07:43 AM

Thank you so much JBeaucaire for your help and early reply.

it works fine.

but i need to filter the data on one column without opening the file and that filtered data only copy to the master sheet at the end of the row (used range).

if possible pl. send the code for the above. i tried autofilter feature but not works fine.

Any help this regard will be highly appreciated.

Thank You.

**JBeaucaire** · 11-12-2011, 12:15 PM

It works fine

....oh, did you try the original macro, or did you add your own tweaks I was discussing above? The original macro wasn't ready for all that, so we still need to customize the "This is the section to customize, replace with your own action code as needed" section.

You can't filter data without opening a file. Autofilter will work fine as long as their are no fully blank rows in your data.

**rafeemd** · 11-13-2011, 05:02 AM

Thank You

How to filter records with more than two criterias on one column.

I would like to filter records on column update, it contains Update, Delete, Insert.
I need to do is, to filter records those containing these three values.

example

A B C
abc xyz Update
cd ik Delete
obx pl Insert
ck pl Insert
xxx yyy
axy 111

In above, filter records which are containing Insert, Delete, Update values.
without using Non Blanks command.

thank you for your support.

**JBeaucaire** · 11-13-2011, 07:53 AM

How to filter records with more than two criterias on one column.

1) Upgrade to a newer version of Excel

2) In Excel 2003 you would need to add a new column specifically to add your new "multi-criteria" test. So, if the column to test is C, then you could put this ins D2 and copy down:

=OR(D2={"Update","Delete","Insert"})

Then you can filter that column on TRUE to see all those rows only.

**rafeemd** · 11-14-2011, 12:14 AM

Thank You JBeaucaire.

To work manually, it works fine.

But, I dot his through VBA.

Thanks

**JBeaucaire** · 11-14-2011, 12:20 AM

In VBA the answer is the same. Use the manual steps above to construct or record a basic macro. Then edit the macro to set the "range" that it filters on large enough to cover all your needs and voila! You've made your own macro.

**rafeemd** · 11-15-2011, 12:47 AM

Hi
Thank you for you response and help.

I have one doubt how to get the data from multiple workbooks
I have 10 Excel files with different column headings and different number of columns.

Can I merge these files into one sheet, depending on the heading names.
the header row number also will vary from file to file.

I am attaching one example file.
The heading names will be coded as in sheet2

I need to do is these files will be merged into one sheet with matching the heading name.

1.
Agent Krish
Country India

Liable code Customer code Customer Name City
849054 849054 Philip Banglaor
849054 849054 Philip Banglaor

2.
Agent karna
Country UP

Ord code Consg code ConsgName City PDName Qty
849054 849054 Philip Banglaor sss 100
849054 849054 Philip Banglaor xyz 1000
849054 849054 Dodge Banglaor

Out put will be like this

Agent Country Liable code Customer code Customer Name City
Krish India 849054 849054 Philip Banglaor
Krish India 849054 849054 Philip Banglaor
Karna UP 849054 849054 Philip Banglaor sss 100
Karna UP 849054 849054 Philip Banglaor xyz 1000
Karna UP 849054 849054 Dodge Banglaor

How can i get it through VBA (Macro) only.

Any help in this regard will be highly appreciated.

Thanks in advance