Modifying all (XL) files in a folder

mhenk

Well-known Member
Joined
Jun 13, 2005
Messages
591
Hi guys, I have some formatting changes I need to make to 20 large excel files, all located in the same folder (some .xlsm, some .xlsx):

1) Delete Rows 1:42
2) Reformat all cells to numeric form "general"
3) Save as CSV file (same filename, new extension).

This is easy enough to record and do for one file, how can I loop through all (XL) files in a folder and do the same thing?

Thx.
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Hi guys, I have some formatting changes I need to make to 20 large excel files, all located in the same folder (some .xlsm, some .xlsx):

1) Delete Rows 1:42
2) Reformat all cells to numeric form "general"
3) Save as CSV file (same filename, new extension).

This is easy enough to record and do for one file, how can I loop through all (XL) files in a folder and do the same thing?

Thx.
Does the folder contain any Excel files other than those you want to modifiy? If so, is there something in the workbook names that distinguist the twenty files from the other files? Also, will the woirkbook hosting the code be in the same folder? If not, you would need to have the path for the twenty workbooks spcified.
 
Upvote 0
Does the folder contain any Excel files other than those you want to modifiy? If so, is there something in the workbook names that distinguist the twenty files from the other files? Also, will the woirkbook hosting the code be in the same folder? If not, you would need to have the path for the twenty workbooks spcified.

There are no other excel files in the folder, and the workbook can be in the same folder or not, whatever's simpler!
 
Upvote 0
This assumes a single sheet workbook or only sheet 1 applies for each workbook in folder. if more sheets should be modified per workbook, the code will need to be modified to accomodate those. The code is untested except for compiling, so post back with error messages and line highlighted when clicking the debur button, if you get errors on the initial run.
Code:
Sub modToCSV()
Dim fName As String, fPath As String, wb As Workbook
fPath = ThisWorkbook.Path
If Right(fPath, 1) <> "\" Then fPath = fPath & "\"
fName = Dir(fPath & "*.xl*")
    Do While fName <> ""
        If fName <> ThisWorkbook.Name Then
            Set wb = Workbooks.Open(fPath & fName)
            With wb.Sheets(1)
                .Rows("1:49").Delete
                .Cells.NumberFormat = "General"
            End With
            wb.SaveAs fPath & wb.Name & ".csv", xlCSV
            wb.Close False
        End If
        fName = Dir
    Loop
End Sub

This is written with the assumption that the host workbook will be in the same folder with the other workbooks.
 
Last edited:
Upvote 0
Hi guys, I have some formatting changes I need to make to 20 large excel files, all located in the same folder (some .xlsm, some .xlsx):

1) Delete Rows 1:42
2) Reformat all cells to numeric form "general"
3) Save as CSV file (same filename, new extension).

This is easy enough to record and do for one file, how can I loop through all (XL) files in a folder and do the same thing?
Try using this code:
Code:
Sub Mod_All()
    ChDir ("C:\Path\To\Your\Folder")
    fName = Dir("*.xls?")
    Do Until fName = ""
        'do your three steps with fName
        fName = Dir
    Loop
End Sub
 
Upvote 0
are they 2007 (xml) or do you have 2003 and before which aren't
 
Upvote 0

Forum statistics

Threads
1,213,565
Messages
6,114,337
Members
448,568
Latest member
Honeymonster123

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
Back
Top