asked on

How to appned data from multiple excel files by vba code ?

Book3.xlsx Book2.xlsx Output.xlsx Book1.xlsxExperts,

I have one folder and it has 300 excel files and each file has "Data Collection" tab and i am looking for to combine in one excel file. Each file has two section. 1. Application (Unique app) 2. App Server mapping .

I would like to combine App Server Mapping one on one table. I am attaching sample files and one output file too. And it's also good we can merge Application info columns to App-Server mapping.

Is it possible someone to create VBA code for me ?

Thanks in Advance and please let me know if you need more details.

Subodh Tiwari (Neeraj)

Please find the attached workbook with the code. You can rename it as per your choice.
Place this macro workbook in the same folder where all 300 workbooks are placed.
Open the macro workbook and then click on the button Merge Data on Mapping Sheet to extract the data from all the 300 workbooks.

Since you have 300 workbooks so code may take some time, so be patient while the code is running.
Also if you look at the status bar of the excel window, you will know the progress of the work i.e. code is extracting data from which workbook etc.
Merged-Data.xlsm

sharepoint0520

ASKER

Hi,

Thank you so much very quick response. I tried it and look like it's working but Type column not updating and i am getting again header on row 21 and 22 if i use Book1 , Book2 and Book3 what i attached in original question.

One more thing..

Is it possible you to merge Application columns to Application Server mapping so we will have only one tab. Application columns must be repeating .

Is it possible we will have two separate code . One you already designed and one is merge one if possible.

By the way Thanks a lot.

Subodh Tiwari (Neeraj)

I forgot to update the Type column. Tweaked the code to take care of that.
Also the code assumes that all your workbooks have the same structure.
For example, Book1 and Book 2 have header rows in rows 19 and 20 while your Book3 has header rows 20 and 21.
At least you will have to make sure that the header rows are same in all the workbooks.
Merged-Data-v2.xlsm

sharepoint0520

ASKER

Hi,

I have 300 files and difficult to check one by one to check row start with 18 or 19. Is it possible to use Hostname or any field to start pulling data ? If not then i will make sure it start with any particular row.

One more thing..

Is it possible you to merge both tables together like a Master table ? I will still keep whatever you designed for separate tables.

Thank You Sir.

Subodh Tiwari (Neeraj)

Okay. I have tried to make it dynamic in the sense that now it will find the header Host Name in col. B and copy the range accordingly. See if that works.
Merged-Data-v3.xlsm

sharepoint0520

ASKER

Hi,

Sorry to tell you but v3 is not working properly. Application Name header becomes CC and Type is not populating and some unexpected result. I am attaching 5 files with v3. I am not able to attach zip file.

Note : when i open any version its asking about to fix links.
1.xlsx
2.xlsx
3.xlsx
4.xlsx
5.xlsx
Merged-Data-v3.xlsm

ASKER CERTIFIED SOLUTION

Subodh Tiwari (Neeraj)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sharepoint0520

ASKER

Thank you so much . It worked perfectly. Thanks a lot for your time and effort.

Subodh Tiwari (Neeraj)

You're welcome. Glad to know that it worked for you. :)

sharepoint0520

ASKER

Hi,

I would like to use Worksheet name as first column in both tab and Type column also in Application tab. Is it possible to update or you i should create new question for it ?

Thanks