Open all files in a folder and delete a column from each?

[TheWennerWoman]

We have some files sent to us by a third party and have a macro that opens each and does some magic.

This month, a new column was added which has broken our macro.

How easy is it to open all files in a specific folder, delete column C from Sheet1 and then save the file down again?

The offending sheet is called "Sheet 1" (space between Sheet and 1).

Thanks in advance as always.

 

[Anfinsen]

Open a new workbook and insert this code into a module. Run it using F5, after you have revised the code to your folder path.

Sub DeleteColumnC()
    Dim folderPath As String
    Dim filename As String
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim lastCol As Integer
    Dim i As Integer
    
    ' Specify the folder path containing Excel files
    folderPath = "C:\Your\Folder\Path\" ' Update this with your folder path
    
    ' Loop through each file in the folder
    filename = Dir(folderPath & "*.xlsx")
    Do While filename <> ""
        ' Open the workbook
        Set wb = Workbooks.Open(folderPath & filename)
        
        ' Delete column C from Sheet1
        Set ws = wb.Sheets("Sheet1")
        lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
        For i = lastCol To 1 Step -1
            If ws.Cells(1, i).Value = "C" Then
                ws.Columns(i).Delete
                Exit For
            End If
        Next i
        
        ' Save the workbook
        wb.Save
        wb.Close
        
        ' Move to the next file
        filename = Dir
    Loop
    
    MsgBox "Column C deleted from Sheet1 in all files in the folder.", vbInformation
End Sub

 

[TheWennerWoman]

Open a new workbook and insert this code into a module. Run it using F5, after you have revised the code to your folder path.

Sub DeleteColumnC()
    Dim folderPath As String
    Dim filename As String
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim lastCol As Integer
    Dim i As Integer
   
    ' Specify the folder path containing Excel files
    folderPath = "C:\Your\Folder\Path\" ' Update this with your folder path
   
    ' Loop through each file in the folder
    filename = Dir(folderPath & "*.xlsx")
    Do While filename <> ""
        ' Open the workbook
        Set wb = Workbooks.Open(folderPath & filename)
       
        ' Delete column C from Sheet1
        Set ws = wb.Sheets("Sheet1")
        lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
        For i = lastCol To 1 Step -1
            If ws.Cells(1, i).Value = "C" Then
                ws.Columns(i).Delete
                Exit For
            End If
        Next i
       
        ' Save the workbook
        wb.Save
        wb.Close
       
        ' Move to the next file
        filename = Dir
    Loop
   
    MsgBox "Column C deleted from Sheet1 in all files in the folder.", vbInformation
End Sub

Thanks for that, much appreciated.

It doesn't delete column C though.

On your FOR....NEXT loop, isn't that looking for a value of "C" in the cell rather than the column letter itself? I've F5'd through your code and it never hits

ws.Columns(i).Delete

 

[Anfinsen]

Try this instead.

Sub DeleteColumnC()
    Dim folderPath As String
    Dim filename As String
    Dim wb As Workbook
    Dim ws As Worksheet
    
    ' Specify the folder path containing Excel files
    folderPath = "C:\Your\Folder\Path\" ' Update this with your folder path
    
    ' Loop through each file in the folder
    filename = Dir(folderPath & "*.xlsx")
    Do While filename <> ""
        ' Open the workbook
        Set wb = Workbooks.Open(folderPath & filename)
        
        ' Delete column C from Sheet1
        On Error Resume Next ' In case column C does not exist
        wb.Sheets("Sheet1").Columns("C").Delete
        On Error GoTo 0
        
        ' Save and close the workbook
        wb.Save
        wb.Close False ' Close the workbook without saving changes (since we already saved)
        
        ' Move to the next file
        filename = Dir
    Loop
    
    MsgBox "Column C deleted from Sheet1 in all files in the folder.", vbInformation
End Sub

 

[Joe4]

I believe that this line of code:

        wb.Sheets("Sheet1").Columns("C").Delete

will need to be changed to this:

        wb.Sheets("Sheet 1").Columns("C").Delete

based on this from the original question:

The offending sheet is called "Sheet 1" (space between Sheet and 1).