Problem with exporting xls sheels to txt files separated by tabs
Hello,
I have a problem with exporting Excel sheets to txt files separated by tabs that I can't find any solutions for. The problem is with fields containing quotation marks or apostrophes (every other fields are fine). To explain the situation I'll give you my own example: Field name in Excel (before export): R.E.M. - "Stand" - 7" (PROMO) Field name in text file (after export from Excel): "R.E.M. - ""Stand"" - 7"" (PROMO)" As you can see, not only quotation marks were doubled but also they were added in the beginning and end of the whole phrase (probably to distinguish the fields). so I have the following question: "How can I properly export Excel sheet with fields that have quotation marks and apostrophes inside to avoid adding any unnecessary characters?" Thank you in advance for any help. Best regards, Piotr |
Problem with exporting xls sheels to txt files separated by tabs
I would think writing a macro to export the data exactly the way you want it
would be the way to go: Some samples: Earl Kiosterud's Text Write program: www.smokeylake.com/excel (or directly: http://www.smokeylake.com/excel/text_write_program.htm) Chip Pearson's: http://www.cpearson.com/excel/imptext.htm J.E. McGimpsey's: http://www.mcgimpsey.com/excel/textfiles.html Earl's program has a supports lots of different options. You may want to see if that works for you right out of the box. If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm Piotr Krasnicki wrote: Hello, I have a problem with exporting Excel sheets to txt files separated by tabs that I can't find any solutions for. The problem is with fields containing quotation marks or apostrophes (every other fields are fine). To explain the situation I'll give you my own example: Field name in Excel (before export): R.E.M. - "Stand" - 7" (PROMO) Field name in text file (after export from Excel): "R.E.M. - ""Stand"" - 7"" (PROMO)" As you can see, not only quotation marks were doubled but also they were added in the beginning and end of the whole phrase (probably to distinguish the fields). so I have the following question: "How can I properly export Excel sheet with fields that have quotation marks and apostrophes inside to avoid adding any unnecessary characters?" Thank you in advance for any help. Best regards, Piotr -- Dave Peterson |
Problem with exporting xls sheels to txt files separated by ta
Thank you very much for your help. That was exactly what I wanted to achieve.
For those who might have the same problem I had I am enclosing whole macro that fixs the issue (I found it somewhere on one of tutorial pages). To use it you just select those fields you want to export and then run that macro. File is saved under c:\MyOutput.txt Sub Export() Dim r As Range, c As Range Dim sTemp As String Open "c:\MyOutput.txt" For Output As #1 For Each r In Selection.Rows sTemp = "" For Each c In r.Cells sTemp = sTemp & c.Text & Chr(9) Next c 'Get rid of trailing tabs While Right(sTemp, 1) = Chr(9) sTemp = Left(sTemp, Len(sTemp) - 1) Wend Print #1, sTemp Next r Close #1 End Sub Best regards, Piotr |
All times are GMT +1. The time now is 02:37 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com