Making the same format change to multiple Excel files

calaloo722

New Member
Joined
Apr 8, 2013
Messages
12
Good morning!
I have over 4,000 excel files, all with identical formats. I need to make a change to ALL of them to change the width of column A to 2.14 and set the font size to 6.
All of the files are in the same folder on the same directory. They all have the file extension .xlsm.

I have tried writing a macro in one file to make the change. It works in that file. But when I simultaneously open another file (the macro is set to run in all open files) and trigger the macro, it shrinks every column to 2.14 and changes all font sizes to 6.

Also, I wouldn't mind not having to individually open all 4,000+ files and running the macro. A one-time processing would be nice!

Any help would be appreciated!
Thanks!
 

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
You will need to substitute the correct path for the variable fPath.
Code:
Sub setWidth()
Dim wb As Workbook, sh As Worksheet, fPath As String, fName As String
fPath = "C:\TEMP"  'Edit path
If Right(fPath, 1) <> "\" Then fPath = fPath & "\"
fName = Dir(fPath & "*.xlsm")
    Do
        On Error Resume Next
        Set wb = Workbooks.Open(fName)
        Set sh = wb.Sheets("Sheet1")
        On Error GoTo 0
            If Not sh Is Nothing Then
                With sh.Columns("A")
                    .ColumnWidth = 2.14
                    .Font.Size = 6
                End With
            End If
        wb.Close True
        fName = Dir
    Loop While fName <> ""
End Sub
 

calaloo722

New Member
Joined
Apr 8, 2013
Messages
12
Thanks!
and now I get to sound like a complete newbie: Where do I put it and how does it trigger?

I started a macro in a file (within the directory of the files I need to convert), gave it it's keyboard shortcut trigger, then ended it. Then I inserted your code, saved it and hit the keyboard shortcut. Nothing happened. But the code appears to work because I had no error message. I've just never tried to run code across multiple files.

Thanks for your help!!
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
The code should be put into the standard module1. Press Alt+F11 to open the VB editor. The large pane should be bright, if it is dark, then Insert>Module. At the top of that pane it shows whether it is is in Module1, 2, ect. or Sheet1, 2, etc. You want to make sure it is Module#(code)... and Not Sheet#(code) or ThisWorkbook(code). Then you can assign the shortcut key or create a button and assign it to a button. Or, if you only need to run it one time, on the menu bar Run>RunSub>click title>Run.
 

calaloo722

New Member
Joined
Apr 8, 2013
Messages
12

ADVERTISEMENT

Thank you, that makes sense!
However when I ran it, I got "Run-Time Error '91' Object variable or with block variable not set."

The error occurred on the line "wb.Close True"

Thank you, again!
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
Thank you, that makes sense!
However when I ran it, I got "Run-Time Error '91' Object variable or with block variable not set."

The error occurred on the line "wb.Close True"

Thank you, again!

Check your file path to be sure it is correct. If the path is correct then check the line for the fName variable to make sure the fPath is concatenated to "*.xlsm" (quotation mark-Asterisk-period-xlsm-quotation mark). You can open the VB editor, put the mouse pointer inside the procedure anywhere and left click. Then use function key F8 to step through the code. As the yellow highlight moves down the code lines the variables will be initialized. You can then hover the mouse pointer over the variable and the Tool Tip should show the value of the variable, If either fPath or fName show a value of "", then the wb variable will not set and will show a value of "Nothing", and you will get the error message that you are getting.

You had stated that all of the files have an .xlsm extension, so that is what I used. If there are no .xlsm files in that directory, then you will also get the message because fName will = "".
 

calaloo722

New Member
Joined
Apr 8, 2013
Messages
12
Sorry I haven't gotten back to you. Just busy.
The file path is correct, and the directory is full of .xlsm files.
the fName is correct, and shows the first file in the directory.

I tried stepping through as you suggested and the only issue I can see is on the line
"Set sh = wb.Sheets("Sheet1")" when I hover over wb.sheets I see:
"wb.Sheets("Sheet1") = < Object variable or With block variable not set> "

Then the step-through completely skips from "If Not sh Is Nothing Then" to "End If".

Thanks for your help!
 
Last edited:

smoopeer

New Member
Joined
Aug 6, 2014
Messages
1
Hi there JLGWhiz. Wonder if you can shed any further light on the above. I have the same problem and I'm getting the same results as calaloo72. I should point out I'm a total newbie to VB so have no idea what I'm doing. Could you illustrate your example to show what variables need changing in your script above? For instance - if I have all the files i need to change in the the directory "F:\mike's stuff" is the ONLY variable I need to change the line: fPath = "C:\TEMP" 'Edit path (and would it become: fPath = "F:mike's stuff" 'Edit path ).
And would the files need to be open when the macro is run? Any help appreciated
 

Watch MrExcel Video

Forum statistics

Threads
1,122,372
Messages
5,595,787
Members
414,021
Latest member
whyjaydee

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top