Replace quotes in notepad

[Michael]

I have an excel .txt file that when opened in Notepad sometimes adds qoutes
the beginning of the data which causes a problem when imported into another
application. What I would like to do is open the file in Notepad edit
replace " with nothing and save the file as a .txt.

Any suggestions would be greatly appreciated.

Thanks
Michael

 

[Peter T]

I don't think there is any way to automate Notepad but no need. Read the
file into memory, edit as required, and re-write the file.

Public Sub RemoveQuotes()
Dim sFile As String
Dim sText As String
Dim FF1 As Long

On Error GoTo errH
sFile = "c:\temp\testfile.txt"

FF = FreeFile
Open sFile For Input As #FF
Line Input #FF, sText
Close #FF

sText = Replace(sText, Chr(34), "")

FF = FreeFile
Open sFile For Output As #FF
Print #FF, sText

resClose:
Close #FF

Exit Sub
errH:
MsgBox Err.Description, , "An error occured"
Resume resClose

End Sub

Regards,
Peter T

 

[Michael]

Thanks for your help with this, unfortunately I am getting a VBA error
message @ Close #FF
sText = Replace(sText, Chr(34), "")
FF = FreeFile
on the replace command. Comile error: Sub or Function not defined.

I have saved the file to C:\Temp and called it testfile.

Any ideas!!

Thanks
Michael

 

[Peter T]

Can't think why you should get a compile error on the line "Close #FF"

Paste the entire routine exactly as you have it in your module

I have saved the file to C:\Temp and called it testfile.

"testfile.txt" I hope, with plenty of quotes in the text.

Regards,
Peter T

 

[Michael]

Sorry I did not make it clear where the error message occurs it is actually
on the line "sText = Replace(sText, Chr(34), "")"
Full code below
Sub RemoveQuotes()
Dim sFile As String
Dim sText As String
Dim FF1 As Long

On Error GoTo errH
sFile = "c:\temp\240908.txt"

FF = FreeFile
Open sFile For Input As #FF
Line Input #FF, sText
Close #FF

sText = Replace(sText, Chr(34), "")

FF = FreeFile
Open sFile For Output As #FF
Print #FF, sText

resClose:
Close #FF

Exit Sub
errH:
MsgBox Err.Description, , "An error occured"
Resume resClose

End Sub
Thanks for your patience
Michael

 

[Peter T]

If(?) you are using Excel-97 the "Replace" function is not recognized, to
cater for all versions replace the "Replace" line with the following

#If VBA6 Then
sText = Replace(sText, Chr(34), "")
#Else
sText = Application.Substitute(sText, Chr(34), "")
#End If

If you are using a later version the above won't correct the problem.
Instead look in Tools, References and uncheck any ticked reference marked
"MISSING"

Regards,
Peter T

 

[Michael]

Peter
Still using Excel-97, the new code works perfectly.

Thanks for your help.

Michael