I have an Excel worksheet that I want to save as a tab delimited text file. In the text file I want each entry to have a double quote (") around it.
i.e. "abc" "cde" "fgh"
When I put the double quote (") around the entries in Excel and then save it as a text file my entries in the text file look like this: """abc""" """cde""" """fgh"""
What do I have to do in the Excel cells so that I don't get the triple double quotes in the output but only single double quotes?
Thanks in advance
Exporting : Saving excel file to a text file - trou
-
-
-
Braveheart,
It sounds backward, but try using 3 or 4 quotes (cant remember which) when writing the string.
like """abc""" """cde""" """fgh"""
If that still fails, why not insert the character 34 using Chr(34).
-
My macro adds the double quote (") as Chr$(34), so that the Excel cell values show as "abc". It is when the file is saved as a .txt file that the extra double quotes appear as """abc""". I have tried all kinds of combinations to get "abc" in the text file that is saved from Excel without any luck. I really don't want to have to edit the text file if I don't have to. Any one with suggestions or is it a Mr. Gates bug in his software?
-
Are you reading the created text file with a text file reader? or are you checking it by opening it back into Excel? What happens if your Excel cells have no quotes, just abc, when you do your save as? Or is your macro writting the data directly to file?
-
I'm reading it with Notepad. The original file that I start with is a text file that was derived from a schematic editor. When you read this file into Notepad it has single-double quotes ("abc") around each entry. I open this file with Excel - which posts each entry into it's own cell without the (") - (abc). I then run my macro which includes putting the (") around each cell entry ("abc"). I then do a Save As - Text (Tab Delimted) (*.txt). It is at this point when I read this file into Notepad that the triple- double quotes apear ("""abc"""). The intention is to read this file back into the schematic editor to update it with all of the Excel edits. Hope this helps to describe my problem. To fix it at the moment, I read the text file into Word - Do A Replace All on """ with " and then save it back out to the text file.
-
-
Hmm, I reproduced the problem. It may not work for you, but if you save the file as formatted text (space delimited) , a .prn file, the quote marks you put in stay as put.
-
I tried the .prn output. You have to be very careful, if the column is not autofitted you loose data on the output. Even then there is no guarantee as a couple of the columns I had to widen further than the autofit to get the full data in the cells. I'll guess I'll stay with the .txt file and edit the quotes in Word. Don't edit the quotes in Notepad. you get to see each individual change on the screen and it takes forever to do. I'll close this out as solved as I have a work around. Thanks for you looking at it.
-
It might be a little late, but I had the same problem until today thanks to Ozgrid user FWind. What we discovered in my office is that if you cut the cells out of Excel and paste then into Notepad and then save as a txt file the double quotes don't show up. Here it the code we are using. I read this before and thought maybe this would help you.
Sub exportdata()
Cells.Select
Selection.Copy
Shell "notepad.exe", vbNormalFocus
SendKeys "^V"
SendKeys "%fa"
End Sub -
Arce2ee,
This site always amazes me with the talent that helps others with their problems. Thank you very much for your input, you've solved a messy work around for me.
braveheart
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!