Pull Data from Folder of Workbooks

Good Morning All,

I have a whole group of workbooks. Is there a way to make a new workbook that will look in the folder containing all of the other customer's books and pull information from them? In other words, I want a workbook where in Column A would contain all of the Customer's Names (Cell Info!G6), Column B would contain the Insurance Company (Cell Info!M14), etc. Is there a way to to that? Thanks in advance.

Please Login or Register  to view this content.

Hello mv835,

This macro is basically an expanded version of Bob's. This one allows you setup the macro to retrieve the data. The data is read from the cell addresses that are in the variable AddxList. This data is then copied as a single row into the destination workbook (the workbook running the macro). You didn't say if you are using Excel 2007 or not, so you can change the file extension from "xls" to "xlsx". The source wowrksheet name is CellInfo and the summary sheet name is Sheet1. These can be changed as well. Change the file path by changing the variable FileFolder.

Please Login or Register  to view this content.

Adding the Macro
1. Copy the macro above pressing the keys CTRL+C
2. Open your workbook
3. Press the keys ALT+F11 to open the Visual Basic Editor
4. Press the keys ALT+I to activate the Insert menu
5. Press M to insert a Standard Module
6. Paste the code by pressing the keys CTRL+V
7. Make any custom changes to the macro if needed at this time.
8. Save the Macro by pressing the keys CTRL+S
9. Press the keys ALT+Q to exit the Editor, and return to Excel.

Sincerely,
Leith Ross

Sorry, forgot to mention I'm using Excel 2007. Also forgot to mention something else; the data comes from 2 different sheets. I have the Customer Name - Info!G6, Insurance Company - Info!M14, and then the Style of shingle and price from sheet InsScope. I have 4 different line items, each for a different shingle. On the information sheet I put what kind of shingle, then on the estimate sheet it fills in the quantity automatically on the line item depending on what item is selected on the information sheet.

Please Login or Register  to view this content.

So I'd like the worksheet, next to the customer name and insurance company, to display the shingle type based on whats selected from the information sheet, and the total for that row. Hopefully I explained that clearly enough.... it's confusing... trust me, I know!

Hello mv835,

You're right it is confusing. Can you post a sample of what the final sheet should look like? I am using Excel 2003. If you post any example worksheets, you would need to save them Excel 2003 format so that I and others can view them.

Sincerely,
Leith Ross

OK, I attached it. There's actually many more sheets, but I deleted all the ones that aren't necessary for what I'm trying to do. Info is the info sheet, InsScope is where the price is supposed to come from, and Sheet1 is supposed to be the new workbook, I just put it in that one so I didn't have to attach two files.

Hello mv835,

Thank you for posting that example. A picture is worth a thousand words. I have 2 questions though. I see a roof type, style of shingle and series of shingle. I guessing when say "shingle type" you are referring to the "roof type", is that correct? The cost, again I am assuming, is the "Total", yes? Let me know so I can correct the macro.

Sincerely,
Leith Ross

For the roof style, you could use Info!P22, which is the year of standard warranty (which corresponds with the 20yr, 30yr, etc shingle on the InsScope (D3-D6))... those cells are actually different on my workbook, i just took out all the fluff to make the file size smaller :P The price I need isn't the total, but rather the unit price for that particular shingle.

I have AIM (HolyKrapItsU) if it's easier for you... thanks again for all of your help... I'm actually trying to learn this stuff believe it or not....

Hello mv835,

I updated the macro. You will need to change the variable FileFolder to the folder path you will be using.

Please Login or Register  to view this content.

Sincerely,
Leith Ross

K, it got the first customer and insurance company, but it hit an error when going to the InsScope worksheet, and I think I know why, but I can't seem to figure out how to fix it.

Please Login or Register  to view this content.

In the book I sent you, they started at the top, but in the actual customer books the item descriptions are D21 thru D24, and the prices that coordinate are J21 thru J24.

Hello mv835,

I made the change to columns "D" and "J" so the macro uses rows 21 -24.

Please Login or Register  to view this content.

Sincerely,
Leith Ross

When I run the Macro, I get an error and when I click Debug, this is where it's showing the error:

Please Login or Register  to view this content.

Also, how can I make that macro run when I open the book? Thanks again for all of your help.

--Mike

Hello Mike,

Can you tell me either the error number or the error description you are getting?

Sincerely,
Leith Ross

Run-time error '91':

Object variable or With block variable not set

Hello Mike,

Sorry about that. I just got up and evidently not not fully awake. This should work.

Please Login or Register  to view this content.

Sincerely,
Leith Ross

Mr. Ross,

Still receiving the same error. I copied the code you gave me, and changed the xlsx to xlsm, changed the path to the folder I'm using, and the column from K to J, but still get the run-time error 91.

Please Login or Register  to view this content.

Thanks again for all of your help.

--Michael Vitale

Hello Mike,

Another typo. If I could only type after all this time, life would much easier. This affliction by the way is an endless source of amusement for my daughter. This is correct.

Please Login or Register  to view this content.

Sincerely,
Leith Ross

I guess she's gonna have an amusing day, because I have another laugh for her :P

It's pulling the customer name and Insurance Company, but not the stuff from the InsScope sheet. Here, I'm gonna attach a dummy sheet with exactly the data....

WAIT I bet I know what the issue is, LOL. OK, this may sound odd, but the data from InsScope pulls from another sheet (for example, D21 is actually "=Estimate!D21" as they're the same sheet). The reason for this is that it defaults to what the estimate sheet says, but the value can be changed if needed. Would that be the issue? I'll send you a sheet for you to see what I'm saying lol.

I leave for work in about an hour and a half, so if you don't hear back from me til late late tonight or tomorrow morning, you'll know why Thanks again for all of your help, you have no idea how much I appreviate it.

-Michael Vitale

Hello Mike,

You forgot I don't have Excel 2007. Unless you save it in Excel 2003 format, I can't open the workbook.

Sincerely,
Leith Ross

You're absolutely right, I completely forgot. Sorry.

Hello Mike,

The layout of the files starts with data in column "B", unlike your example which started in "A". That's why the results were wrong. I think we have it worked out now. Here is the revised macro based on the actual file layout.

Please Login or Register  to view this content.

Sincerely,
Leith Ross

Sorry I haven't written back sooner.... That's perfect! Thanks again for all of your help