Modifying CSV files from a local folder-VBA

Question

I am trying to rearrange the order of the columns in csv files in a folder on my local drive.

At the moment, from a tutorial, I have found a way to loop through the files. I wanted to cut a column and re insert in a different column. When running this code, Excel is crashing. It seems to be going through duplicate files.

I expected the columns to have moved in all the files in the folder. But they didn't move. And excel is crashing, looks like it's duplicating the files when hitting CTRL + G and running the code.

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

Please help.

move the *csv from the fileDirectory to the DIr . eg fileDirectory = "C:\...\" and fileName = Dir(fileDirectory & "*csv") — CDP1802
– CDP1802, Commented Jan 16, 2023 at 15:35
You should also fully qualify your Columns ranges with something like fileToOpen.Sheets(1).Columns("R").Cut... — CLR
– CLR, Commented Jan 16, 2023 at 15:48

Shai Rado · Accepted Answer · 2023-01-16 17:58:45Z

1

You need to fully qualify your Columns object with a Worksheet object.
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

edited Jan 16, 2023 at 17:58

answered Jan 16, 2023 at 16:40

Shai Rado

33.7k7 gold badges32 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Shai Rado Over a year ago

@CDP1802 thanks, missed the part it's a .csv file

ASH Over a year ago

There are four objects that you need to reference, or Excel won't know what you want to do. Excel Object, Workbook Object, Worksheet Object, and Range Object. See the link below for more details. excelmacromastery.com/vba-objects

Collectives™ on Stack Overflow

Modifying CSV files from a local folder-VBA

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related