replacing csv text with new text

[RAV_M]

Hi all - i'm new to this website, so apologies if i've posted in the wrong place...

I have a csv file which i need to open and then in the fourth row replace the following text

! !,0,

with

" ",0,

i.e. replace the ! with "

I need to do this in a vba macro and also once the csv file is saved not to have quotes around the quotes...

so don't want """ """,0,

which is what you get if you open the file in excel make the change and then try to save as csv file!

Any help would be really appreciated...

 

[Ruddles]

A larger sample of the data would have been quite useful but you're welcome to give this a try:-

[FONT=Courier New][SIZE=1]Option Explicit[/SIZE][/FONT]
 
[FONT=Courier New][SIZE=1]Sub EditFileChars()[/SIZE][/FONT]
 
[FONT=Courier New][SIZE=1]Dim intFH As Integer[/SIZE][/FONT]
[FONT=Courier New][SIZE=1]Dim bChar As Byte[/SIZE][/FONT]
[FONT=Courier New][SIZE=1]Dim iFilesize As Long[/SIZE][/FONT]
[FONT=Courier New][SIZE=1]Dim iChar As Long[/SIZE][/FONT]
[FONT=Courier New][SIZE=1]Dim iChanged As Long[/SIZE][/FONT]
 
[FONT=Courier New][SIZE=1]Dim iPtr As Integer[/SIZE][/FONT]
[FONT=Courier New][SIZE=1]Dim sFileName As String[/SIZE][/FONT]
[FONT=Courier New][SIZE=1]Dim sBackup As String[/SIZE][/FONT]
 
[FONT=Courier New][SIZE=1]iPtr = InStrRev(ActiveWorkbook.FullName, ".")[/SIZE][/FONT]
[FONT=Courier New][SIZE=1]sFileName = Left(ActiveWorkbook.FullName, iPtr - 1) & ".csv"[/SIZE][/FONT]
[FONT=Courier New][SIZE=1]sFileName = Application.GetOpenFilename(FileFilter:="Comma-separated files (*.csv), *.csv,Text files (*.txt), *.txt, All files (*.*),*.*")[/SIZE][/FONT]
[FONT=Courier New][SIZE=1]If sFileName = "False" Then Exit Sub[/SIZE][/FONT]
[FONT=Courier New][SIZE=1] [/SIZE][/FONT]
[FONT=Courier New][SIZE=1]iPtr = InStr(sFileName, ".")[/SIZE][/FONT]
[FONT=Courier New][SIZE=1]sBackup = Left(sFileName, iPtr - 1) & ".bak"[/SIZE][/FONT]
[FONT=Courier New][SIZE=1]FileCopy sFileName, sBackup[/SIZE][/FONT]
[FONT=Courier New][SIZE=1] [/SIZE][/FONT]
[FONT=Courier New][SIZE=1]iPtr = InStrRev(ActiveWorkbook.FullName, ".")[/SIZE][/FONT]
[FONT=Courier New][SIZE=1]Close[/SIZE][/FONT]
[FONT=Courier New][SIZE=1]intFH = FreeFile()[/SIZE][/FONT]
[FONT=Courier New][SIZE=1]Open sFileName For Random As intFH Len = 1[/SIZE][/FONT]
 
[FONT=Courier New][SIZE=1]iFilesize = LOF(intFH)[/SIZE][/FONT]
[FONT=Courier New][SIZE=1]iChanged = 0[/SIZE][/FONT]
 
[FONT=Courier New][SIZE=1]For iChar = 1 To LOF(intFH)[/SIZE][/FONT]
[FONT=Courier New][SIZE=1]Get #intFH, iChar, bChar[/SIZE][/FONT]
[FONT=Courier New][SIZE=1]If bChar = Asc("!") Then bChar = 34: iChanged = iChanged + 1[/SIZE][/FONT]
[FONT=Courier New][SIZE=1]Put #intFH, iChar, bChar[/SIZE][/FONT]
[FONT=Courier New][SIZE=1]Next iChar[/SIZE][/FONT]
 
[FONT=Courier New][SIZE=1]Close intFH[/SIZE][/FONT]
 
[FONT=Courier New][SIZE=1]MsgBox "Finished editing " & sFileName & ": " _[/SIZE][/FONT]
[FONT=Courier New][SIZE=1]   & CStr(iFilesize) & " byte" & IIf(iFilesize = 1, "", "s") & " read, " _[/SIZE][/FONT]
[FONT=Courier New][SIZE=1]   & CStr(iChanged) & " byte" & IIf(iChanged = 1, "", "s") & " changed" & Space(10), _[/SIZE][/FONT]
[FONT=Courier New][SIZE=1]   vbOKOnly + vbInformation[/SIZE][/FONT]
 
[FONT=Courier New][SIZE=1]End Sub[/SIZE][/FONT]

Let me know if it doesn't do what you want.

Important note: as this is editing a file in situ with no option to 'cancel without saving', please test on a copy of your data!

 

[Ruddles]

You might like to replace that closing MsgBox command with this slightly improved version:-

[FONT=Courier New][SIZE=1]  MsgBox vbCrLf & "Finished editing " & sFileName & ": " _[/SIZE][/FONT]
[FONT=Courier New][SIZE=1]     & CStr(iFilesize) & " byte" & IIf(iFilesize = 1, "", "s") & " read, " _[/SIZE][/FONT]
[FONT=Courier New][SIZE=1]     & CStr(iChanged) & " byte" & IIf(iChanged = 1, "", "s") & " changed" _[/SIZE][/FONT]
[FONT=Courier New][SIZE=1]     & Space(10) & vbCrLf & vbCrLf _[/SIZE][/FONT]
[FONT=Courier New][SIZE=1]     & "Original version of file backed up as " & sBackup, _[/SIZE][/FONT]
[FONT=Courier New][SIZE=1]     vbOKOnly + vbInformation[/SIZE][/FONT]