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

New Member
Joined
Aug 16, 2023
Messages
1
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.

VBA Code:
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;

VBA Code:
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âõå¾¼"
 
Last edited by a moderator:

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Am no VBA expert but this looks wrong to me

Code:
            Open DestFileName For Binary As #1"

Shouldnt that be

Code:
            Open DestFileName For Input As #1"
 
Upvote 0

Forum statistics

Threads
1,222,045
Messages
6,163,583
Members
451,846
Latest member
ajk99

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top