How do i open a csv file created in excel and do a find and replace of this file and save as a new file without strange characters ??

[Birds20]

i have the following code which is able to save within excel a csv addint tilda characters to each appropriate cell and doing a .SaveCopyAs to a new filename.

i am trying within the same vba code to open this new file and replace the tilda and comma(since it adds it to the new filen) with just a tilda.

However when i tried to save it, the file has a bunch of unreadable characters.

With fd
        .Filters.Clear
        .Title = "Choose your file to add tilde character"
        .AllowMultiSelect = False
       
        .InitialFileName = "F:\"
        .Filters.Add "Filter", "*.xlsx"
       
        'Find last row of data and loop from A-P and Ad ~ to end of cell, then save it with the tilda, then reopn #2 and replace ~, with just ~
        'Dim i As Long
        'For i = 1 To Workbooks.Count
        '    Debug.Print Workbooks(i).FullName
        'Next i
        Set destWbk = ActiveWorkbook
       
        If .Show = True Then
            MsgBox "Please select OK to start the process", vbOKOnly, "Starting"
            MyPath = .SelectedItems(1)
            Position = InStr(1, MyPath, "xlsx", vbBinaryCompare)
            Set srcWbk = Workbooks.Open(.SelectedItems(1))
            lr = srcWbk.Sheets("Your Jira Issues").Cells(Rows.Count, 2).End(xlUp).Row
            Set trng = srcWbk.Sheets("Your Jira Issues").Range("A1:P" & lr)
            Set drng = destWbk.Sheets("Sheet1").Range("A" & 1)
            trng.Copy drng
           
            'Close the workbook that we copied the data from
            Workbooks(2).Close SaveChanges:=False
            Set srcWkb = Nothing
           
            'Get the last row that has data and add in the tilda character to each cell on each line
            lr = Cells(Rows.Count, "A").End(xlUp).Row
            For r = 1 To lr
                Cells(r, "A") = Cells(r, "A") & "~"
                Cells(r, "B") = Cells(r, "B") & "~"
                Cells(r, "C") = Cells(r, "C") & "~"
                Cells(r, "D") = Cells(r, "D") & "~"
                Cells(r, "E") = Cells(r, "E") & "~"
                Cells(r, "F") = Cells(r, "F") & "~"
                Cells(r, "G") = Cells(r, "G") & "~"
                Cells(r, "H") = Cells(r, "H") & "~"
                Cells(r, "I") = Cells(r, "I") & "~"
                Cells(r, "J") = Cells(r, "J") & "~"
                Cells(r, "K") = Cells(r, "K") & "~"
                Cells(r, "L") = Cells(r, "L") & "~"
                Cells(r, "M") = Cells(r, "M") & "~"
                Cells(r, "N") = Cells(r, "N") & "~"
                Cells(r, "O") = Cells(r, "O") & "~"
                Cells(r, "P") = Cells(r, "P") & "~"
            Next r
           
            'This is the new name of the file as a .csv
            DestFileName = Mid(MyPath, 1, (Position - 1)) & "csv"
            strOutputFileName = Mid(MyPath, 1, (Position - 2)) & "2.csv"
            Application.ActiveWorkbook.SaveCopyAs Filename:=DestFileName
            Application.ActiveWorkbook.Saved = True
           
            'Open the csv file and find replace tilda and comma with just tilda then close it
            Open DestFileName For Binary As #1
            txtData = Space$(LOF(1))
            Get #1, , txtData
            Close #1
           
            strData() = Split(txtData, vbCrLf)
           
            For i = LBound(strData) To UBound(strData)
                Debug.Print strData(i) ' Replace(txtData, "~,", "~")
            Next i
End If
End With

EDIT:
I added these lines;

Open DestFileName For Input As #1

Do Until VBA.EOF(1)
Line Input #1, strContents
Debug.Print strContents
Loop
Close #1

I am seeing characters like "PK!kã^€Ç[Content_Types].xml ¢( ¬TËNÃ0¼#ñ‘¯(vá€jÚ# °µ·©c[^SÚ¿g“–
P ö'±wfvìq8o\1ÃD6øJœËž(Ðë`¬ŸTâõå¾¼"

 

[Special-K99]

Am no VBA expert but this looks wrong to me

            Open DestFileName For Binary As #1"

Shouldnt that be

            Open DestFileName For Input As #1"