|
Replace quotes in notepad
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 |
Replace quotes in notepad
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" wrote in message ... 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 |
Replace quotes in notepad
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" wrote: 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" wrote in message ... 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 |
Replace quotes in notepad
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" wrote in message ... 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" wrote: 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" wrote in message ... 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 |
Replace quotes in notepad
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" wrote: 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" wrote in message ... 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" wrote: 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" wrote in message ... 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 |
Replace quotes in notepad
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" wrote in message ... 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" wrote: 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" wrote in message ... 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" wrote: 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" wrote in message ... 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 |
Replace quotes in notepad
Peter
Still using Excel-97, the new code works perfectly. Thanks for your help. Michael "Peter T" wrote: 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" wrote in message ... 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" wrote: 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" wrote in message ... 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" wrote: 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" wrote in message ... 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 |
| All times are GMT +1. The time now is 01:21 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com