Problem with exporting xls sheels to txt files separated by tabs

[Piotr Krasnicki]

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]

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]

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