Replacing all values in a column from csv file using VBA

Question

I have a csv file with mix of string and numbers in different columns. I want to replace all "?" characters form column C.

I am trying the following:

Set wb_dst = Workbooks.Open("C:\myFile.csv")
Set ws_dst = wb_dst.Sheets(1)
values = ws_dst.Range("C1:c500").Value
values = Replace(values, "?", "")

But not sure how to dump this value back to column C without replacing values in other columns. Or, is there any other way to perform the above task or replacing certain characters only in a certain column from a csv file on local drive?

Edit: I am looking to update the values in column C for myFile.csv so I suppose I have to dump data to column C, I am not sure?

Variatus · Accepted Answer · 2021-01-11 02:43:08Z

1

It's simple. You just assign the array you took from the range back to the value of that same range after modification. However, the idea of values = Replace(values, "?", "") won't fly. You will need to modify each element of the array individually.

Sub Macro1()

    Const LookFor       As String = "?"
    Const Replacement   As String = "X"
    
    Dim Wb              As Workbook
    Dim Ws              As Worksheet
    Dim Rng             As Range
    Dim Arr             As Variant
    Dim R               As Long                 ' loop counter: rows
    
    Set Wb = Workbooks.Open("C:\myFile.csv")
    Set Ws = Wb.Worksheets(1)
    Set Rng = Ws.Range("C1:C500")
    
    Arr = Rng.Value
    For R = 1 To UBound(Arr)
        Arr(R, 1) = Replace(Arr(R, 1), LookFor, Replacement)
    Next R
    Rng.Value = Arr
End Sub

answered Jan 11, 2021 at 2:43

Variatus

14.4k2 gold badges16 silver badges35 bronze badges

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

6 Comments

Zanam Over a year ago

Does it dump data back to CSV or is your code missing that part as goal is to get the csv updated with new values for column C

Variatus Over a year ago

There is no "CVS". The CVS file was opened in Excel and the workbook created from it was assigned to the variable Wb. That workbook's Worksheet(1) was then modified. You can then decide to save the workbook or not, in xlsx, xlsx or cvs format. Record the SaveAs keystrokes with the macro recorder and add the VBA commands to the above code.at the bottom.

Variatus Over a year ago

You might also open the CVS file in NotePad and use the Edit > Replace function there to directly replace values in the CVS file without going through Excel.

Zanam Over a year ago

I never used CVS :)

Zanam Over a year ago

I also don't understand your comment regarding saveas

|

Collectives™ on Stack Overflow

Replacing all values in a column from csv file using VBA

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related