Unwanted Quotations Marks in Save as Tab Delimited File

[Guest]

When I use the Substitute function on a text string, and then save as a Tab
Delimited File, I get Quotations Marks at the beginnig and end of the cell
results. I don't want them.

They don't appear in Excel, but they are in the resulting file.

 

[Guest]

Check out:
http://exceltips.vitalnews.com/Page...Extra_Quote_Marks_in_Exported_Text_Files.html

 

[Guest]

Thanks for this. I had seen this, but really don't understand it.

I've been playing with this most of the night and early this morning, and it
ONLY happens when I substitute a comma. If I substitute some other
character, like an x, then it comes out fine. Problem is, the system I'm
trying to create the file for requires a comman, and I can't change that!

Thanks for your help. Any other ideas?

Don

 

[Guest]

Well, it sounds like you are stuck. According to the article, you have two
options:
1) Open the file in another program (i.e. Notepad, Word, Wordpad) and search
for the quotation marks and replace them with nothing, to get rid of them
after saving the file from Excel.
2) Create a macro by going to Tools->Macro->Macros->Record New Macro, select
Personal Macros Workbook, click OK to start recording. Then click on a
different cell (any cell) and click on the Stop Recording button that is
floating arround. Then go to Window->Unhide, select Personal.xls and click
OK/Unhide. Then go to Tools->Macro->Macros, higlight the new macro and click
Edit. This will take you to its code. Highlight everything between the line
that reads "Sub Macro1" (or whatever the macro name is) and "End Sub". Delete
the text and paste in:
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

Change the "c:\MyOutput.txt" to be the path and file name that you want to
save to. Then go to File->Close and Return to Microsoft Excel. Then go to
Window->Hide. Then cells you want to save and go to Tools->Macro->Macros,
highlight the macro and click Run.

This could be modified to export all cells and to use a button, but this
should hopefully get you started.

Help any?