Modifying CSV files from a local folder-VBA

Question

I'm attempting to reorder the columns in a folder of csv files on my local computer.

I have currently discovered a method to loop through the files using a tutorial. I wanted to remove one column and replace it with another column. Excel crashes when this code is executed. It appears to be scanning duplicate files.

I anticipated that the columns would have changed in every file in the folder. But they remained still. And when you press CTRL + G and run the code, Excel crashes, seemingly duplicating the files.

Here's the code.

Option Explicit

Sub FleetMoveColumns()

    Dim fileDirectory As String
    Dim fileCriteria As String
    Dim fileName As String
    Dim fileToOpen As Workbook
    
    Application.ScreenUpdating = False
    
    fileDirectory = "C:\...\*csv"
    
    fileName = Dir(fileDirectory)
    
    Do While Len(fileName) > 0
    
        Set fileToOpen = Workbooks.Open(fileDirectory & fileName)

        Columns("R").Cut
        Columns("AB").Insert
                
        Debug.Print fileName
    
    Loop
       
    Application.ScreenUpdating = True
    
    
End Sub

Answer 1

1. You need to fully qualify your Columns object with a Worksheet object.
2. You need to place FileName = Dir within your Do While loop.

Modified code

Do While Len(FileName) > 0

    Set fileToOpen = Workbooks.Open(fileDirectory & FileName)

    ' set the worksheet object
    Set Sht = fileToOpen.Worksheets(1) ' <-- Rename "Sheet1" to your desired worksheet
    
    With Sht
        .Columns("R").Cut
        .Columns("AB").Insert
    End With

    ' clear objects
    Set Sht = Nothing
    Set fileToOpen = Nothing        

    Debug.Print FileName
        
    FileName = Dir
Loop