Running a Macro over multiple excel files and saving them in a new directory

Question

I know a very similar form of this question has been asked many times before, but I'm having some problems getting it to work with my macro.

Basically, what I need is to have a Macro that will run over several hundred files all located in the same folder, and then "save" the new files (without replacing the old files) in a separate folder/directory. Obviously without having to open each file individually and running the Macro over and over again.

I've tried a few different suggestions but they have all either resulted in drastically unnecessary changes to my fully functioning macro, or have ended in substantial errors.

The location of the original files is--> F:\Reports_Complete\Reports_201308_2014\
. . . The folder only contains files that the Macro needs to run on, and they are all .csv files (the final files should be .xls). . .

The location of the files after the Macro runs should be--> F:\Reports_Complete\

As opposed to copying the entirety of my several hundred line Macro, here are the important lines:

In the beginning of the Macro:

ActiveWorkbook.SaveAs Filename:="F:\Reports_Complete\EXT872_VTDT_" & Range("B2").Text & ".xls"

........................

At the end of the Macro:

With ActiveWorkbook
'MsgBox .FileFormat, , .FullName
Application.DisplayAlerts = False
.SaveAs .FullName, xlNormal
Application.DisplayAlerts = True
'MsgBox .FileFormat, , .FullName
End With

I would be extremely grateful for some help, and if possible without jumping over any steps or procedures ;-)

Thanks again!!!

EDIT

Sub ProcessFiles()
Dim Filename, Pathname As String
Dim wb As Workbook

Pathname = ActiveWorkbook.Path & "\Files\"
Filename = Dir(Pathname & "*.csv")
Do While Filename <> ""
    Set wb = Workbooks.Open(Pathname & Filename)
    DoWork wb
    wb.Close SaveChanges:=True
    Filename = Dir()
Loop
End Sub

..........

Sub DoWork(wb As Workbook)
With wb
'Here is where my code is
End With
End Sub

I've tried using the above code, but it isn't working for me. Is there another way to make my macro work across an entire folder?

Oh, and for reference the Macro is saved to my PERSONAL.XLSB file that is in a different directory from the files that need to be edited. It's called Macro6. — Operationpbj, Jun 23 '14 at 16:11
Also, let me understand: is this macro performing any action on the files, or do you simply want to create a new named copy of the file in another directory? — David Zemens, Jun 23 '14 at 16:12
that's not helpful -- which question are you answering? Also, since you're relying so heavily on `Select` and `Activate`, it is really hard to make any recommendations because I have no idea what has been `Selected`... — David Zemens, Jun 23 '14 at 16:14
just reverser the process mentioned in here --[spiceworks - exceltocsv](http://community.spiceworks.com/scripts/show/987-automate-excel-to-csv-conversion) — , Jun 23 '14 at 16:15
The original files contain several thousand lines of unsorted data in a single sheet. The current Macro sorts the data and creates/divides it among about 7 sheets. Basically I need to add some snippet of code that will apply the Macro I already have over an entire folder of .csv files. And of course save the new files in a specific directory with a new extension — Operationpbj, Jun 23 '14 at 16:15
Don't point me to another link. Describe step by step what the macro should do. 1) Open file. 2) change number format of Range(B2), 3) Save as new filename. Is that correct? — David Zemens, Jun 23 '14 at 16:15
And does the macro currently work *as expected* for a single file? — David Zemens, Jun 23 '14 at 16:17
Yes, it absolutely does work as expected. My issue is that I need it to run for every file in a specific folder concurrently, without me having to open each file and individually run the Macro. — Operationpbj, Jun 23 '14 at 16:19
There are probably hundreds of relevant links here about how to loop/iterate files within a specified directory. What have you tried so far? — David Zemens, Jun 23 '14 at 16:20
First, thank you very much for helping me! Second, I've tried: Sub ProcessFiles() Dim Filename, Pathname As String Dim wb As Workbook Pathname = ActiveWorkbook.Path & "\Files\" Filename = Dir(Pathname & "*.xls") Do While Filename <> "" Set wb = Workbooks.Open(Pathname & Filename) DoWork wb wb.Close SaveChanges:=True Filename = Dir() Loop End Sub — Operationpbj, Jun 23 '14 at 16:20
code doesn't render in comments. Please revise your question to include that code snipped. — David Zemens, Jun 23 '14 at 16:21

score 1 · Answer 1 · edited May 23 '17 at 12:17

This is a simple method to loop over the files in a specified folder.

This assumes that your current macro acts on the ActiveWorkbook and already does:

Formats the value in Range("B2")
Saves the file as new file to a specified destination

Try:

Sub LoopFile()
'Enable reference to Microsoft Scripting Runtime if you want to use early binding
    Dim fso as Object  'Scritping.FileSystemObject
    Dim fldr as Object 'Scripting.Folder
    Dim file as Object 'Scripting.File
    Dim wb as Workbook

    Set fso = CreateObject("Scripting.FileSystemObject")
    Set fldr = fso.GetFolder("F:\Reports_Complete\Reports_201308_2014\")

    For Each file In fldr.Files
        'Open the file
        Set wb = Workbooks.Open(file.Path)
        '## You will need to modify this line to refer to the correct
        '    module name and macro name:
        Application.Run "Personal.xlsb!Module1.Macro6"

        'Close the file (it was saved in Macro6 already)
        wb.Close
    Next

    Set file = Nothing
    Set fldr = Nothing
    Set fso = Nothing
End Sub

NB I normally do not recommend relying on ActiveWorkbook but I'm not going to sift through hundreds of lines of your code to optimize it. I will recommend that you read THIS on how (and why) to avoid Select and Activate methods.

Running a Macro over multiple excel files and saving them in a new directory

1 Answers1