run macro on all workbooks in folder

[tagteam]

Hey guys, new to the forum

I am looking for a way to run a macro on all xlsx files in the folder. I want to open the file, set one of the columns to date format and then save it and close it. Then i want to do that to all the files in that folder.

I have some rudimentary vba skills from my work in access but this is beyond me.

Any help is appreciated.
Thanks

 

[DanteAmor]

Use the following macro

Sub run_macro_on_all_workbooks_in_folder()
    '
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Application.StatusBar = False
    ruta = "C:\trabajo\books\"          'name folder
    arch = Dir(ruta & "*.xls*")
    '
    Do While arch <> ""
        Application.StatusBar = "Processing file : " & arch
        Set l2 = Workbooks.Open(ruta & arch)
        Set h2 = l2.Sheets(1)                                                'put the number of sheet
        h2.Columns("H:H").NumberFormat = "mm/dd/yyyy"    'put de column and date format
        l2.Close True
        arch = Dir()
    Loop
    Application.ScreenUpdating = True
    Application.StatusBar = False
    MsgBox "End"
End Sub

 

[tagteam]

Thank you for the reply.

I have updated the code to match my environment and set it to xlsx because they will always be xlsx files. I then assigned it to a button and open the file and click the button and it just shows me a box that says End, with a button for OK, however, the field is not updated. I have tried it with the files open and closed and neither seems to work.

I dont get any errors or anything.

Sub run_macro_on_all_workbooks_in_folder()
    '
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Application.StatusBar = False
    ruta = "MyFolderPath"          'name folder
    arch = Dir(ruta & "*.xlsx")
    '
    Do While arch <> ""
        Application.StatusBar = "Processing file : " & arch
        Set l2 = Workbooks.Open(ruta & arch)
        Set h2 = l2.Sheets(1)                                                'put the number of sheet
        h2.Columns("G:G").NumberFormat = "mm/dd/yyyy"    'put de column and date format
        l2.Close True
        arch = Dir()
    Loop
    Application.ScreenUpdating = True
    Application.StatusBar = False
    MsgBox "End"
End Sub

I tried to do some basic testing by adding

Dim FAvar As String at the top

and right after arch = Dir(ruta & "*.xlsx") I added
FAvar = arch

And at the End i added
MsgBox "the file name is " + FAvar

I was hoping I would at least see the file name but the box now just says "the file name is"

 

[DanteAmor]

You have to put the complete path, for example:

ruta = "C:\files\my workbooks\etc\"

 

[tagteam]

Sorry, to clarify , ruta = "MyFolderPath" 'name folder is set to my current folder. I am in the habit of replacing my real file paths with general ones for safety sake.

I would like it to actually just look in the current folder bc that is where the files always are and it still does not work.

If i wanted to set the code to look in the current folder can i just use:

ruta = "%cd%"

 

[DanteAmor]

Then it is like that

ruta = thisworkbook.path & "\"

Put the book with the macro in the same folder where you have the files

Try and tell me

 

[tagteam]

Thank you for the reply. It does not set it to date format, but does set it to the custom format using mm/dd/yyyy and appears to work for my import so i am happy with it.

Thank you for the help on this, it is a great little example for me to learn more from.

I appreciate the help.

 

[DanteAmor]

I'm glad to help you.