Modifying CSV files from a local folder-VBA

0 votes

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
Jan 17 in Others by Kithuzzz
• 28,700 points
48 views

1 answer to this question.

0 votes

Solution 

  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
answered Jan 17 by narikkadan
• 53,160 points

Related Questions In Others

0 votes
1 answer

Exporting a table from Amazon RDS into a CSV file

Presumably, you're attempting to export data from ...READ MORE

answered Mar 3, 2022 in Others by gaurav
• 22,940 points
1,120 views
0 votes
1 answer

How to return a result from a VBA function

You must associate the value with the ...READ MORE

answered Nov 4, 2022 in Others by narikkadan
• 53,160 points
153 views
0 votes
1 answer
0 votes
1 answer

Excel VBA: Trying to read all files in folder based on cell input and output to another cell

Your array has no capacity for data ...READ MORE

answered Jan 24 in Others by narikkadan
• 53,160 points
48 views
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
530 views
0 votes
1 answer

How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

answered Dec 3, 2018 in Power BI by Upasana
• 8,620 points
2,408 views
0 votes
1 answer

Using VBA Excel to create a gramatically correct list

The Excel AND function is a logical ...READ MORE

answered Feb 9, 2022 in Others by gaurav
• 22,940 points
117 views
0 votes
2 answers

How to copy a formula horizontally within a table using Excel VBA?

Hi so basically, create an adjacent column ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,630 points
294 views
0 votes
1 answer
0 votes
1 answer

Trying to create an enclosing bookmark after pasting a picture from Excel into Word using VBA

Try this: Sub IMPORT_TO_WORD() Dim ws1 As Worksheet, ws2 ...READ MORE

answered Oct 30, 2022 in Others by narikkadan
• 53,160 points
114 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP