Changing contents of many Excel spreadsheets

[271828]

On a current project I developed a custom spreadsheet. For this thread, the details of the calcs don't matter.

Each scenario is in one spreadsheet. There are about 250 of these. (Writing a program and running these in a loop wasn't a good option or else I would've done that.)

After I got through all of these, I found out I need to add another check.

My only idea is: (1) open one spreadsheet that already has the new block of calcs; (2) open another, copy the block of new calcs into it, save, close; (3) repeat.

In 2023, it seems like there would be a better way to do this. Ideas?

 

[SkipVought]

Hi,

So many questions!

You developed a custom "spreadsheet."
Excel or what?
A sheets in a workbook separate workbooks?

"Writing a program and running these in a loop wasn't a good option or else I would've done that."
Are you the spreadsheet program grand poobah who knows all the spreadsheet programming grand poobah "tricks" to be able to eliminate that kind of option?

"After I got through all of these, I found out I need to add another check"
You developed a custom spreadsheet that requires 250 separate copies. WOW!

I remember there was a guy at the bomber plant that did work similar to me. He produced these Excel workbooks with dozens and occasionally hundreds of sheets and I was producing the same kind if stuff with ONE SHEET. Actually, it was THREE sheets: one for data, one for miscellaneous factors and one for an interactive chart. And it wasn't because he was an unintelligent guy. i just had more tricks/tools at my disposal and it wasn't VBA.

We need much more specific information.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[271828]

Sorry. LOL

Each workbook has only one worksheet, so there are about 250 workbooks.

Excel

How I put myself in this situation: At the first of the project, about a half dozen parameters had to be manually input from proprietary customer information, so I didn't see a good way to write formulas for these, generate tables that could be used with a vlookup, etc. As I got farther in, I realized I should've leaned on them harder to provide the formulas. Too late now!

 

[sticksandtriangles]

Not an excel wizard, but you could do something like this in python.

Gather all excel book file paths in a given folder, feed these books into a pandas dataframe, add new calc in python, write and save new excel file.
The newly added portion would not have the excel type formulas. There maybe some way to do this in pandas but I'm not sure.

If quick and dirty is the preferred method, this might beat opening 250 files.


S&T -

http://www.re-tug.com

 

[SkipVought]

My only idea is: (1) open one spreadsheet that already has the new block of calcs; (2) open another, copy the block of new calcs into it, save, close; (3) repeat.

Well that sure sounds to me like a fixem program loop, only

open one spreadsheet that already has the new block of calcs;

Loop thru the remaining 249 to
COPY/PASTE
SAVE
CLOSE

Hopefully, all these workbooks are in one folder, or you already have a list of each workbook path/name and can loop thru that list.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[271828]

What's a "fixem program loop"? I did a quick search and the answer wasn't obvious.

 

[SkipVought]

You are familiar with loops, yes?

For...Next, Do...Loop, Do While...Loop

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[driftLimiter]

In python or matlab/octave you can loop over each file and make edits. Would be easier if all excel files were in the same directory. I'd imagine there is a VBA way to do this as well but of that I am not certain.

You could also make a macro that performs the operations for you and then you just open each workbook run the macro and move on to the next one.

 

[3DDave]

In similar circumstances I've made programs to generate the workbooks from some data files. Rather than updating, I change the original program and re-create all the workbooks. If the operation is so irregular that doing this to begin with wasn't possible it's tough to formulate a method to build on that irregularity with a spot-fix.

I've mentioned using AutoIt elsewhere for outside the app manipulations. I expect the changes you are looking to do could be handled with SendKey, which just sends what you would type. AutoIt comes with a number of examples and each function comes with a working example to show what it does.