Edit CSV file adding double quote via macro

[MrRed_07]

Hello, i have created an excel file where there is a button and when the user presses it, it prompts a window for the user to select a CSV file to be edited.
After the file is selected i make some changes and save it to a new csv file that works fine, but i struggle to add double quotes (") to the K column. The data in the K column are numbers of this format : 0000012345.
I have tried using this :

 Dim k As Range
 For Each k In Range("K2", Range("K" & Rows.Count).End(xlUp))
        k.Value = Chr(34) & k.Value & Chr(34)
 Next k

but the result when i open the new csv file are in this format : """0000012345""". I want to be like this : "0000012345".

Any idea how may i do it ?
Thanks in advance.

 

[rlv01]

Something like this perhaps

    Dim k As Range
    Dim S As String

    For Each k In Range("K2", Range("K" & Rows.Count).End(xlUp))
        S = Trim(Replace(CStr(k.Value), Chr(34), ""))
        k.Value = Chr(34) & S & Chr(34)
    Next k

 

[MrRed_07]

Something like this perhaps

    Dim k As Range
    Dim S As String

    For Each k In Range("K2", Range("K" & Rows.Count).End(xlUp))
        S = Trim(Replace(CStr(k.Value), Chr(34), ""))
        k.Value = Chr(34) & S & Chr(34)
    Next k

still tha same outcome

 

[MrRed_07]

The content of the CSV file that opens to be edited looks like this :

CITY NAME;5278 90** **** 4303;0021417951;1;975;18/07/2023;48,00;-0,29;47,71;19/07/2023;0000062439

After the edit i want to look like this :

CITY NAME;5278 90** **** 4303;0021417951;1;975;18/07/2023;48,00;-0,29;47,71;19/07/2023;"0000062439"

 

[rlv01]

but the result when i open the new csv file are in this format : """0000012345""". I want to be like this : "0000012345".

I think I misunderstood what you wanted. Excel is going to save to csv as either 0000012345 or """0000012345""", depending on cell contents and format. Unless someone comes along with an easier suggestion, to get "0000012345" in a CSV file you may have to write code to build your ownsave routine to read each row and write a delimited line to a text file.

Are you sure you need to go to all that trouble? For example, if you open the file with excel, it will import both of these lines the same way

CITY NAME;5278 90** **** 4303;0021417951;1;975;18/07/2023;48,00;-0,29;47,71;19/07/2023;0000062439
CITY NAME;5278 90** **** 4303;0021417951;1;975;18/07/2023;48,00;-0,29;47,71;19/07/2023;"0000062439"