Is there a way to avoid that Excel add quotes to text when exporting a sheet?

  • Thread starter Thread starter Dario de Judicibus
  • Start date Start date
D

Dario de Judicibus

I have a sheet containg only text in non empty cells. Some text contains
quotes, some other text does not contain any quote at all. For example

+----------------+------------------+--------------------+
| sample text | | |
+----------------+------------------+--------------------+
| another one | "alien" term | |
+----------------+------------------+--------------------+
| "special" one | this or that | red, white, green |
+----------------+------------------+--------------------+
| simple text | let it be | |
+----------------+------------------+--------------------+
| few words | | |
+----------------+------------------+--------------------+

I want to export it as Unicode text (tab delimited), but I do NOT want text
to be enclosed in quotes, nor I want quotes to be doubled when inside text.
I want text to be saved AS IS. I cannot find a way, anyway. Help, please.

DdJ
 
J.E. McGimpsey said:
Take a look at

http://www.mcgimpsey.com/excel/textfiles.html#csvnoquotes
Click to expand...

OK, here is:

Public Sub TextNoModification()
Const DELIMITER As String = "CHR(9)" 'or "|", ",", etc.
Dim myRecord As Range
Dim myField As Range
Dim sOut As String

Open "Test.txt" For Output As #1
For Each myRecord In Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
With myRecord
For Each myField In Range(.Cells(1), Cells(.Row,
Columns.Count).End(xlToLeft))
sOut = sOut & DELIMITER & myField.Text
Next myField
Print #1, Mid(sOut, 2)
sOut = Empty
End With
Next myRecord

Close #1
End Sub

Two questions:

1. how can I avoid to hardcode filename, but rather use the standard save as
dialog
2. how can I ensure it is saving file as Unicode text (LE, BOM)

?

DdJ
 
Two questions:

1. how can I avoid to hardcode filename, but rather use the standard save as
dialog
2. how can I ensure it is saving file as Unicode text (LE, BOM)

?

DdJ
 
J.E. McGimpsey said:
1. Use the GetOpenFileName method

2. Use StrConv() to wrap the output string.
Click to expand...

I'm sorry but I do not know VBasic for Excel. I program by C++, Rexx or PHP.
May you tell me exactly which instructions I should add to:

Public Sub TextNoModification()
Const DELIMITER As String = "CHR(9)" 'or "|", ",", etc.
Dim myRecord As Range
Dim myField As Range
Dim sOut As String

Open "Test.txt" For Output As #1
For Each myRecord In Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
With myRecord
For Each myField In Range(.Cells(1), Cells(.Row,
Columns.Count).End(xlToLeft))
sOut = sOut & DELIMITER & myField.Text
Next myField
Print #1, Mid(sOut, 2)
sOut = Empty
End With
Next myRecord

Close #1
End Sub

Thank you in advance.

DdJ
 
Back
Top