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

Book3.xlsxBook2.xlsxOutput.xlsxBook1.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.
sharepoint0520Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
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
sharepoint0520Author Commented:
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)Excel & VBA ExpertCommented:
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

sharepoint0520Author Commented:
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)Excel & VBA ExpertCommented:
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
sharepoint0520Author Commented:
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
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Sorry for the late reply. I am busy in a project so won't be able to devote much time here.
Please find the attached workbook to see the max what I could offer.
You have so many merged cells which are creating problem so it would be better if you keep Mapping and Application on separate sheets.
Once the data is copied from all the workbooks into the master workbook, you can manually copy the data from application and paste it on mapping sheet otherwise I don't see any problem in having two different sheets for mapping and application.
Merged-Data-v4.xlsm

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sharepoint0520Author Commented:
Thank you so much . It worked perfectly. Thanks a lot for your time and effort.
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome. Glad to know that it worked for you. :)
sharepoint0520Author Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.