Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Exporting to .txt converts single quotes to double quotes
Hi All
Im making a module that will create a new spreadsheet, populate it with some fixed and variable values and save it as a text file. Unfortunately excel 2007 is saving the following line: <?xml version="1.0" encoding="ISO-885h9-1" ? as "<?xml version=""1.0"" encoding=""ISO-885h9-1"" ?" when I open it in wordpad. I have tried coding this line as = "<?xml version=" & Chr(34) & "1.0" & Chr(34) & " encoding=" & Chr(34) & "ISO-885h9-1" & Chr(34) & " ?" and = "<?xml version=""1.0"" encoding=""ISO-8859-1"" ?" and it looks fine in the spreadsheet its in, but always bad when it gets to the .txt file. I also tried separating into separate cells and formatting the cell as text. Any idea how to correct this? Thanks a lot Andrew |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Exporting to .txt converts single quotes to double quotes
Andrew P. brought next idea :
Hi All Im making a module that will create a new spreadsheet, populate it with some fixed and variable values and save it as a text file. Unfortunately excel 2007 is saving the following line: <?xml version="1.0" encoding="ISO-885h9-1" ? as "<?xml version=""1.0"" encoding=""ISO-885h9-1"" ?" when I open it in wordpad. I have tried coding this line as = "<?xml version=" & Chr(34) & "1.0" & Chr(34) & " encoding=" & Chr(34) & "ISO-885h9-1" & Chr(34) & " ?" and = "<?xml version=""1.0"" encoding=""ISO-8859-1"" ?" and it looks fine in the spreadsheet its in, but always bad when it gets to the .txt file. I also tried separating into separate cells and formatting the cell as text. Any idea how to correct this? Thanks a lot Andrew Not exactly sure what you mean by <snip Unfortunately excel 2007 is saving the following line: <?xml version="1.0" encoding="ISO-885h9-1" ? </snip I pasted this line into a cell and wrote the text file using: WriteTextFileContents ActiveCell.Value, "C:\Test.txt" Here's what I got when viewed in Notepad: <?xml version="1.0" encoding="ISO-885h9-1" ? ...which hasn't changed from what was entered in the cell. Here's the procedure to write the text file: Sub WriteTextFileContents(Text As String, FileName As String, Optional AppendMode As Boolean = False) ' A reuseable procedure to write or append large amounts of data to a text file Dim iNum As Integer Dim bIsOpen As Boolean On Error GoTo ErrHandler iNum = FreeFile() If AppendMode Then Open FileName For Append As #iNum Else Open FileName For Output As #iNum 'If we got here the file has opened successfully bIsOpen = True 'Print to the file in one single step Print #iNum, Text ErrHandler: 'Close the file If bIsOpen Then Close #iNum If Err Then Err.Raise Err.Number, , Err.Description End Sub 'WriteTextFileContents() -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Exporting to .txt converts single quotes to double quotes
On 25/05/2010 10:14, Andrew P. wrote:
Hi All Im making a module that will create a new spreadsheet, populate it with some fixed and variable values and save it as a text file. Unfortunately excel 2007 is saving the following line: <?xml version="1.0" encoding="ISO-885h9-1" ? as "<?xml version=""1.0"" encoding=""ISO-885h9-1"" ?" when I open it in wordpad. That *is* the correct encoding of an ASCII text string in a .txt file! The opening quote at the start and end of the string means that internally every use of " must use an escape code. Excel chooses to use "" for escaping in ". Other languages choose similar methods. When Excel reads it back in then it will see the correct string data. Otherwise strings would be ambiguous in their .txt representation. This way you know that true end of string is a " not followed by another. = "<?xml version=""1.0"" encoding=""ISO-8859-1"" ?" and it looks fine in the spreadsheet its in, but always bad when it gets to the .txt file. I also tried separating into separate cells and formatting the cell as text. Any idea how to correct this? There is nothing to correct. Excel for once is doing the right thing. Regards, Martin Brown |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Exporting to .txt converts single quotes to double quotes
Martin Brown laid this down on his screen :
On 25/05/2010 10:14, Andrew P. wrote: Hi All Im making a module that will create a new spreadsheet, populate it with some fixed and variable values and save it as a text file. Unfortunately excel 2007 is saving the following line: <?xml version="1.0" encoding="ISO-885h9-1" ? as "<?xml version=""1.0"" encoding=""ISO-885h9-1"" ?" when I open it in wordpad. That *is* the correct encoding of an ASCII text string in a .txt file! The opening quote at the start and end of the string means that internally every use of " must use an escape code. Excel chooses to use "" for escaping in ". Other languages choose similar methods. When Excel reads it back in then it will see the correct string data. Otherwise strings would be ambiguous in their .txt representation. This way you know that true end of string is a " not followed by another. = "<?xml version=""1.0"" encoding=""ISO-8859-1"" ?" and it looks fine in the spreadsheet its in, but always bad when it gets to the .txt file. I also tried separating into separate cells and formatting the cell as text. Any idea how to correct this? There is nothing to correct. Excel for once is doing the right thing. Regards, Martin Brown You are correct! But.., this is what happens when you use Save As and specify ".txt"! I didn't catch that right away, and so couldn't figure out why the text was being modified during the process. Obviously, this is not what the OP wants. That said, I think, for the OP's purpose, the context of the string (being XML) is incorrectly being saved to the text file by Excel<IMO. This renders the text as useless in terms of being working XML, or being imported as useable XML without having to be reformatted or otherwise manipulated/reworked. Using VB[A] file I/O to write the text to file persists the original format. Importing the text to Excel via the File Open dialog requires setting the delimited option (as opposed to fixed length option) to avoid the double quotes persisting in the result. Using VB[A] file I/O to import the text persists the text 'as it was' in the text file. -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Exporting to .txt converts single quotes to double quotes
Fixed!
http://www.mcgimpsey.com/excel/textf...#csvwithquotes the one that was needed was "Text files with no modification". Thanks McGimpsey and Associates Regards Andrew "Andrew P." wrote: Hi All Im making a module that will create a new spreadsheet, populate it with some fixed and variable values and save it as a text file. Unfortunately excel 2007 is saving the following line: <?xml version="1.0" encoding="ISO-885h9-1" ? as "<?xml version=""1.0"" encoding=""ISO-885h9-1"" ?" when I open it in wordpad. I have tried coding this line as = "<?xml version=" & Chr(34) & "1.0" & Chr(34) & " encoding=" & Chr(34) & "ISO-885h9-1" & Chr(34) & " ?" and = "<?xml version=""1.0"" encoding=""ISO-8859-1"" ?" and it looks fine in the spreadsheet its in, but always bad when it gets to the .txt file. I also tried separating into separate cells and formatting the cell as text. Any idea how to correct this? Thanks a lot Andrew |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Double quotes when exporting to tab delimited file | Excel Discussion (Misc queries) | |||
Double Quotes | New Users to Excel | |||
Double Quotes | Excel Discussion (Misc queries) | |||
Double Quotes | Excel Programming | |||
Double Quotes | Excel Programming |