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, 2023 in Others by Kithuzzz
• 38,010 points
584 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, 2023 by narikkadan
• 63,420 points

Related Questions In Others

0 votes
1 answer
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
• 23,260 points
1,868 views
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
• 63,420 points
547 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
• 63,420 points
1,192 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
875 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
3,181 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
• 23,260 points
479 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,670 points
730 views
0 votes
1 answer

Looping through a folder with Excel files and scraping date from selected cells usin VBA

You record the outcome of your comparison ...READ MORE

answered Feb 16, 2023 in Others by narikkadan
• 63,420 points
631 views
0 votes
1 answer
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