Open all CSVs in folder and replace column?

[jbass350z]

I am using Excel 2007 and need to open all the .csv files in a folder, change the contents of every cell in a column, resave the files and close them. There are hundreds of files, so it would be best to use a loop to step through each file and to the above.

I need to change all the cells in column B to “some text”. Each .csv file has a different number of rows.

I am not very good with VBA and have tried to cut and paste a lot of code together to make this work but have not been successful.

Can someone please help ?

 

[pboltonchina]

Untetsed, so try on a copy of your data

Sub AllFiles()
    Dim folderPath As String
    Dim filename As String
    Dim wb As Workbook
  
    folderPath = "C:\SAP Imports\Sales Orders\"
    
    If Right(folderPath, 1) <> "\" Then folderPath = folderPath + "\"
    
    filename = Dir(folderPath & "*.csv")
    Do While filename <> ""
      Application.ScreenUpdating = False
        Set wb = Workbooks.Open(folderPath & filename)
         
              Dim LR As Long, i As Long
LR = Range("B" & Rows.Count).End(xlUp).Row
For i = LR To 1 Step -1
    Range("B" & i).Value = "some text" 'change to suit
Next i
    
        filename = Dir
    Loop
  Application.ScreenUpdating = True
End Sub

 

[jbass350z]

Untetsed, so try on a copy of your data

Sub AllFiles()
    Dim folderPath As String
    Dim filename As String
    Dim wb As Workbook
  
    folderPath = "C:\SAP Imports\Sales Orders\"
    
    If Right(folderPath, 1) <> "\" Then folderPath = folderPath + "\"
    
    filename = Dir(folderPath & "*.csv")
    Do While filename <> ""
      Application.ScreenUpdating = False
        Set wb = Workbooks.Open(folderPath & filename)
         
              Dim LR As Long, i As Long
LR = Range("B" & Rows.Count).End(xlUp).Row
For i = LR To 1 Step -1
    Range("B" & i).Value = "some text" 'change to suit
Next i
    
        filename = Dir
    Loop
  Application.ScreenUpdating = True
End Sub

Almost perfect. How do I save and close each .CSV file during the loop without being prompted to save it?

 

[pboltonchina]

Insert this after 'Next i

ActiveWorkbook.Close SaveChanges:=True