Changing contents of many Excel spreadsheets
Changing contents of many Excel spreadsheets
(OP)
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?
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?
RE: Changing contents of many Excel spreadsheets
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,
for a NUance!
RE: Changing contents of many Excel spreadsheets
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!
RE: Changing contents of many Excel spreadsheets
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 - www.re-tug.com
RE: Changing contents of many Excel spreadsheets
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!
RE: Changing contents of many Excel spreadsheets
RE: Changing contents of many Excel spreadsheets
For...Next, Do...Loop, Do While...Loop
Skip,
for a NUance!
RE: Changing contents of many Excel spreadsheets
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.
RE: Changing contents of many Excel spreadsheets
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.