| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| VBA String Output adds Quotes when I cut and paste to Notepad | Excel Programming | |||
| Selection.Replace What and double quotes | Excel Programming | |||
| change straight quotes to curly quotes | Excel Discussion (Misc queries) | |||
| How do i get historical stock quotes using MSN Money Stock Quotes | Excel Discussion (Misc queries) | |||
| NotePad | Excel Programming | |||
Hybrid Mode
