×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Changing contents of many Excel spreadsheets

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?

RE: Changing contents of many Excel spreadsheets

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Changing contents of many Excel spreadsheets

(OP)
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!

RE: Changing contents of many Excel spreadsheets

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 - www.re-tug.com

RE: Changing contents of many Excel spreadsheets

Quote:

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Changing contents of many Excel spreadsheets

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

RE: Changing contents of many Excel spreadsheets

You are familiar with loops, yes?

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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Changing contents of many Excel spreadsheets

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.

RE: Changing contents of many Excel spreadsheets

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login



News


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close