Who knows excel? Queries? VBA?

I have about 70 workbooks, each of which have 17 worksheets in them. All of these are identical template documents that are locked down and everything will be in the same place. I do not own the template, I cannot make any changes, I am basically on read only (if I wasn’t, I wouldn’t be making this post). I can call on the data, but I can’t edit the sheet or copy any of the tabs. Can’t add another sheet.

I need to make a summary of all of these in another Excel document. Basically, in my summary document, each workbook will have its own row, and that row needs to be populated with data from the 17 different tabs.

Is there a smarter way to do this? I can do it manually, but that would take a long time. Person before me did it manually. Any thoughts?

The picture shows basically what I'm trying to do--I've added a row for each example to show where we'd be getting the data from. Assume they're in the same folder if that helps. 

Manually is dumb, but without any editing rights, I think you're screwed.  Any chance someone can give you permission to edit?

There may be a VBA way to do it but I am not familiar enough to know.  Do you know anything about perl?  There's a pretty good XLSX library out there that you can include that will read and write from Excel docs.  I've used that a bit to do what you're describing.

I've inquired about this with the GF, who is an Excel whiz. I'll let you know what she says. 

If you know the directories, filenames, sheetnames, and data cell locations you can do all of this from a fairly simple looping code that runs through your list of locations and posts the results to a tabulated output on a file you do have access to. Setting up the file call is probably the least intuitive part of the code in VBA. The code could live in your output file, so that you can edit it for future changes or re-run it if the contents of the sources change.

Manually, as in "copy and paste the data from each individual workbook and tab every damn time", or manually as in "set each cell in the summary to reference the correponding cell in the data sources"...?

The first one is just stupid.  The second one will be tedious, but you'll only have to do it once.

The excel whiz will be sending me a macro you can trying during her lunch break in a couple of hours. 

Duke said:

Manually, as in "copy and paste the data from each individual workbook and tab", or manually as in "set each cell in the summary to reference the correponding cell in the data sources"...?

The first one is just stupid.  The second one will be tedious, but you'll only have to do it once.

Second one, but even that is not just once--I will have another 100 of these by years end, and you still have to select the file that you're linking to at least once. Can't just type/paste it in. 

thatsnowinnebago said:

The excel whiz will be sending me a macro you can trying during her lunch break in a couple of hours. 

Wooo! Thanks to thatsnowinnebago GF!

I can't tell you off the top of my head, but I can tell you that it is fairly easily done in VBA.  If I had some more free time I'd set up a sample and try the challenge myself.

Hopefully thatsnowinnebagogf can help you

ProDarwin said:

I can't tell you off the top of my head, but I can tell you that it is fairly easily done in VBA.  If I had some more free time I'd set up a sample and try the challenge myself.

 

Hopefully thatsnowinnebagogf can help you

I figured it should be--I just haven't had to do something like this before. 

This is where I really regret not re-taking programming classes in college (I had my worst college professor in my [required for my non-IT/Comp sci major] intro to Java class, and it completely turned me off of programming)

mtn said:

thatsnowinnebago said:

The excel whiz will be sending me a macro you can trying during her lunch break in a couple of hours. 

Wooo! Thanks to thatsnowinnebago GF!

She's pretty rad. She has suggested a sumifs formula at first and provided an example: 

=sumifs(B:B,$A:$A,"criteria make sure it matches something within the column,$C:$C,,"criteria make sure it matches something within the column")+sumifs(B:B,$A:$A,"criteria make sure it matches something within the column,$C:$C,,"criteria make sure it matches something within the column")

Apparently you can set your cell references across multiple files. One of many things she's taught me about excel.

It sounds like you're building something that approaches what we programmers call an Office-Powered Clusterberkeley. If this is likely to get more complicated in the future, you should do this some other way - maybe with Access just to get away from the elaborate macros, or maybe with some kind of real database solution (which doesn't have to be expensive or complicated...could be as simple as a free SQL server and some saved queries).

GameboyRMH said:

It sounds like you're building something that approaches what we programmers call an Office-Powered Clusterberkeley. If this is likely to get more complicated in the future, you should do this some other way - maybe with Access just to get away from the elaborate macros, or maybe with some kind of real database solution (which doesn't have to be expensive or complicated...could be as simple as a free SQL server and some saved queries).

Actually, this is to go into QuickBase. We're in the process of removing the Excel docs altogether and having people submit these cases directly into QuickBase, but that is still 6-12 months off. In the meantime, for my specific application in QuickBase I upload from the summary document into QuickBase. 

So yes, you're exactly right--but in the meantime, meantime being 6-12 months, this is what we have to deal with. Some of the fun of working for an F500 company that has had at least 10 Mergers/Acquisitions in the last 10 years is that it takes a long time to get everything synced up, unsiloed and unmatrixed.

SSIS package.  If the files have the same structure, easy.

In reply to Dr. Hess :

That's if he has access to SSIS and knows SSIS, which I'm assuming he doesn't.  He'd have to loop through a directory with the files and have a parameterized Excel source, which isn't the most basic thing in SSIS.

I'd estimate that it would take a day or two to nail down the logic in something like VBA.  But then when new or refreshed files are put in the directory, it'd just be a minute or so to generate a new summary file.

It seems like the formula route would have to be updated every time a file is introduced or removed.

GameboyRMH said:

Office-Powered Clusterberkeley

*snicker*  

I have a few of those 

I just googled it for a sec.  I think it would be pretty easy to implement.   mtn if you have a few example files with junk data in them and an example 'summary', I would give it a shot this evening.

I'm a mechanical engineer, not a programmer, but I often find myself writing code in situations like this for exactly the same reason you are asking about it: there has to be a better way than manual labor.

The data tools thing is free from Microsoft. You could use access or a csv as the destination if you are SQL Server challenged. Drop a file processing container, point to the directory, add a data pump, run it.

I believe the answer is Megasquirt.

But seriously, this is exactly why I started using Access and then FileMaker Pro.  Excel is great for many things, but when you want to manipulate data on multiple tables, you need a relational database.  

Dr. Hess said:

The data tools thing is free from Microsoft. You could use access or a csv as the destination if you are SQL Server challenged. Drop a file processing container, point to the directory, add a data pump, run it.

True, I hadn't even thought of just running it out of the dev environment.  Despite that it's mostly a GUI, I still think there's a decent learning curve for what he would want to do with it.

Oh, there's a learning curve alright.  Horrible, horrible learning curve.  Mostly a flat line.  But, once you have a couple years experience with it, piece of cake.  German chocolate cake with those sprinkle things and a side of ice cream.

So, after consulting with thatsnowinnebagoGF, she needs more time to write a working macro.

"Since he needs it updated with each new spreadsheet, it will be difficult unless there's a standard row on each he wants moved. "

The fact that the OP says all of the workbooks are locked down may doom what I am about to write, but if it were ME...

I would use Access to hotlink to all of those workbooks, then 1 or more queries to munch/consolidate the data into what you want, followed by either an Access report or, alternatively link your Excel summary file up to the Access query.

I am horrible at raw VBA code, even worse with Pivot Tables, but for some reason I can do a lot of cool E36 M3 with Access. 

Sorry to have let the ball drop here. ThatsnowinnebagoGF's grandpa just died so she's been busy with that.