Making the same format change to multiple Excel files

[calaloo722]

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!

 

[JLGWhiz]

You will need to substitute the correct path for the variable fPath.

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]

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]

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]

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]

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]

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!

 

[smoopeer]

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