• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 84
  • Last Modified:

Can you batch edit (change) 100 identical spreadsheets?

I have 100 or so identical spreadsheets in a directory. Identical layout but different entries in each spreadsheet. I need to format a number of cells to "shrink to fit." Is there a way to batch edit that many spreadsheets?

In the alternative, I created a macro to do a single spreadsheet. How do I save it in the directory so that anyone bringing up a spreadsheet in that directory can run that macro?
Bill Golden
Bill Golden
  • 2
2 Solutions
Panagiotis ToumpaniarisSystem EngineerCommented:
You can use VBA to do that.

This code will set shrink to fit to true for the first column.
Sub test()
Range("A:A").ShrinkToFit = True
End Sub

Open in new window

To make this work for many workbooks follow the guides here : https://www.rondebruin.nl/win/s3/win010.htm

hope it helps
Just use the vbs to do it.

The VBS script like below codes(not test yet):
  Dim xpp, book, fso, files
  Set xpp = CreateObject("Excel.Application")
  Set fso = CreateObject("Scripting.FileSystemObject")
  Set files = fso.GetFolder("Your Folder which store 100+ Excel Files").files
  For Each f In files
    book = Application.Workbooks.Open(f)
    ' change the sheet name and range as you want
    book.Sheets("Sheet1").Range("A:A").ShrinkToFit = True
    Set book = Nothing
  Set files = Nothing
  Set fso = Nothing

Open in new window

Panagiotis ToumpaniarisSystem EngineerCommented:
Both ways work.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now