Scraping data from the same cells on multiple tabs in a workbook onto a single tab

[LightingRetro]

Hello,

I'm working with a company that has done all of their proposals and invoicing in excel for years, and I'm trying to get all of their information online into a CRM. The good news is that the invoices are all consistent, and the data is predictably in the same cells on each tab.

Is a macro the best way to scrape the data from workbooks with literally hundreds of tabs in order to aggregate this data? I have years 2010-2014 from which I need to extract this data.

Invoice Template.xlsxMacro Layout.xlsx

I've attached a sample of what the typical worksheet tab looks like, and then also attached the format I'd like to extract the information to.

Is a macro the best way to do this? Or is there another function that would be effective. Some of those spreadsheet I'm guessing have 500 sheets in them. Thanks!!

[Rioran]

Hello, Retro.

VBA is the only way to gather data in this case. But question "How?" is open.

If result data will have more than 300'000 rows - I strongly recommend to scrap information into Access file or, that could be even more better, to any SQL data base.

In any case, part of code that will open your basic files one by one and circling through your sheets will be the same for any "WareHouse" you will choose.

[LightingRetro]

I estimate no more than 500 rows per workbook. I can them combine that data from several years into a single csv file for import. If I understand your question correctly, that would mean I could keep this function in excel, and not need access right?

[Rioran]

Yes, Excel will be enough in this case.

One more important thing is where all of your files are located. It could be easier to write a code if it is a single folder with nothing more, than books we are parsing.

[LightingRetro]

I would have workbooks named 2010, 2011, 2012, 2013, 2014

I can put them all in one folder named as suggested if that makes it easier.

[Rioran]

May you upload 1 nonempty example worksheet FROM where info will be gathered? Just to be sure that code will work exactly as we want.

And all of your workbooks consist from identical by structure sheets without any deviations, so we may apply the same code to every sheet and workbook without filtering anything. Am I right?

[LightingRetro]

I already put a non-empty template attached above. And then the other attachment is the desired output with so references. Does this address what you're asking, or are you meaning something else?

[Rioran]

In "Invoyce_Template" I see no data but one picture. Are you gathering pictures?

[LightingRetro]

Yikes!! wrong file. THanks for the heads up on that. SampleProposal.xlsx

I'll test this to make sure it uploaded correctly. Thanks for your help, it is very much appreciated.

[Rioran]

Hello again, Retro.

There is one more question. Explain please adress system:

Please Login or Register  to view this content.

What is City, what is State and what is ZIP here?

[LightingRetro]

I had one guys tell me he could parse that data into 3 columns, but I think I will just keep it in one. I can manually change the records or use a different excel function to parse the data later. If I could transfer all of the info from that cell into just the Address cell, that would be great. I can delete the city and zip code columns for ease. Thanks!!!

[Rioran]

Hello, Retro!

I am sorry for making you wait so long. But I've finally found 1 hour to compile something for you.

Please check button in the attachment. Programm will ask you to select a folder where your files are stored. Be sure this folder doesn't contain anything more.

Elapsed time will depend on power of your PC and may take from several seconds to a minute or two.

Please Login or Register  to view this content.

[LightingRetro]

You sir, are hero status!

works perfectly, and easy to adapt to any cells that were in different places.

Thanks!!!