Free Microsoft Excel 2013 Quick Reference

Excel adding quotation marks when saving text file

Hi,

Excel is adding quotation marks to my my data when saving as a tab delimited text file.
I know (after some googling) that you can overcome this by using the 'PRINT' command instead of 'WRITE', but I'm not doing any VB coding so it doesn't seem to apply in my case and I wouldn't know how to do it if it did.

I'm simply manually selecting 'Save As' and creating the file that way.

Does anyone have any suggestions please? Thx

[EDIT]Solved Well....Kind of. What I've learned since making my post may help others, so I came back with a bit of an update.

For some reason, it took a while to occur to me that I could just copy and paste my desired selection into a blank text file instead; I think I assumed ti would lose the formatting, but of course it doesn't.

Anyway, I've done that now and it works fine. I can live with this for now, but since the whole point of my Excel tool is to help me automate my workflow, it kinda defeats the purpose.

So I'm still keen to learn how I can stop Excel adding the quotation marks when exporting.

During my search, I came across this: http://www.asap-utilities.com/index.php

It's an action-packed Excel utility suite that integrates seamlessly with Excel and has 300 macros and other functions. It's free for personal use and a reasonable price for commercial use. Definitely worth checking out.

I was eager to try this out to see if the files it exported contained the quotation marks and sadly, they do, so there's obviously still something I need to do with Excel to stand any chance of avoiding them.

Any leads?

p.s.
Hope I didn't break any forum rules with the link I posted. I'm sure it's probably a well-known product in this forum, which may or may not have some history here, good or bad. I wouldn't know, since I'm new to all this, but I felt it was worth mentioning.


Hi,

I use Excel 2003. For example I write test;test1 in A1 and save as the file
text(tab delimited)(*.txt). I open the saved file, it write "test;test1".
If a cell contains semicolon between text, after saving file as text(tab
delimited)(*.txt), it places quotation marks.

Why it puts quotation marks? And how can I configure excel not put quotation
marks?

Note: Replacing quotation marks in saved text file is not a solution for me.

Thanks and best regards.
Sevgi.

Hi,

I use Excel 2003. For example I write test;test1 in A1 and save as the file
text(tab delimited)(*.txt). I open the saved file, it write "test;test1".
If a cell contains semicolon between text, after saving file as text(tab
delimited)(*.txt), it places quotation marks.

Why it puts quotation marks? And how can I configure excel not put quotation
marks?

Note: Replacing quotation marks in saved text file is not a solution for me.

Thanks and best regards.
Sevgi.

Hi hope someone can help,
What I have is a double quoted, comma delimited text file that is created from an external application, every now and again we need to open the file to make corrections and this is easier if we open it via Excel using the delimiter options, the problem is when saving the file as a csv it removes the double quotes, however I have found some vba coding that adds the double quotes back, which works great, but it changes the format of the date fields from what the original file had from 09-Aug-2009 to 09/08/2009.
I suspect this may be because the file is opened before the double quotes are added and excel changes the format.

Is there any code I can add that will stop the date format from changing
I have tried the below code but with no luck
By the way the text file is used as an import to a database

This is the code so far.

Option
Explicit

Sub CSVFile()
Dim SrcRg As Range
Dim CurrRow As Range
Dim CurrCell As Range
Dim CurrTextStr As String
Dim ListSep As String
Dim FName, DName As Variant
Dim StrcsvName As String
Dim Filter As String, Title As String
Dim FilterIndex As Integer
Dim StrNewFilename As String
Dim Count1 As Integer
Dim FmtCell As String

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
'FName = Application.GetSaveAsFilename("", "CSV File (*.csv), *.csv")

'^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
' select file to open
Filter = "CSV(comma delimited)(*.csv),*.csv," & _
        "Excel Files (*.xls),*.xls," & _
        "Text Files (*.txt),*.txt," & _
        "All Files (*.*),*.*"

' Default Filter *.csv
FilterIndex = 1
' Set Dialog Caption
Title = "Select a File to Open"
' Select Start Drive & Path
ChDrive ("C")
ChDir ("C:Temp")

With Application
    ' Set File Name to selected File
    FName = .GetOpenFilename(Filter, FilterIndex, Title)
    ' Reset Start Drive/Path
'    ChDrive (Left(.DefaultFilePath, 1))
'    ChDir (.DefaultFilePath)
End With
' Exit on Cancel
If FName = False Then
    MsgBox "No file was selected."
    Exit Sub
End If
' Open File
Workbooks.Open FName
'MsgBox Filename, vbInformation, "File Opened" ' This can be removed

'^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
'FName = Application.Dialogs(xlDialogOpen).Show
StrcsvName = ActiveWorkbook.Name

If FName <> False Then
ListSep = Application.International(xlListSeparator)
    If Selection.Cells.Count > 1 Then
        Set SrcRg = Selection
    Else
        Set SrcRg = ActiveSheet.UsedRange
    End If
    
'Directory Name
Count1 = InStrRev(FName, "")
DName = Left(FName, Count1)
    
'Create new file name
StrNewFilename = InStr(StrcsvName, ".")
StrNewFilename = Mid(StrcsvName, 1, StrNewFilename - 1)
StrNewFilename = StrNewFilename & "_" & Format(Now, "dd-mm-yy_hh-mm-ss") & ".txt"

    Open StrNewFilename For Output As #1
    'Open FName For Output As #1
    For Each CurrRow In SrcRg.Rows
        CurrTextStr = ""
        For Each CurrCell In CurrRow.Cells
            CurrTextStr = CurrTextStr & """" & CurrCell.Value & """"
& ListSep
        Next
    
    While Right(CurrTextStr, 1) = ListSep
        CurrTextStr = Left(CurrTextStr, Len(CurrTextStr) - 1)
    Wend
        Print #1, CurrTextStr
    Next
        Close #1
   
With Workbooks(StrcsvName)
    .Saved = False
    .Close
End With

MsgBox "File Creation is complete." & vbNewLine & vbNewLine & "File Name:" & "
" & _
        StrNewFilename & vbNewLine & vbNewLine & "Location:" & " " & DName,
vbInformation, "File Created..."
With Application
    ' Reset Start Drive/Path
    ChDrive (Left(.DefaultFilePath, 1))
    ChDir (.DefaultFilePath)
End With
End If

    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub


Any help would be much appreciated as I am stumped

When I open text files in Excel, it adds an extra row in between each row
actually in the text file. How do I set it so that Excel opens the txt
normally?

when i copy cells from excel into another application (html form, txt editor, dreamweaver) excel adds quotation marks.

example:
cell in excel:
="<h3>"&D2&"</h3>
<a href='"&T2&"'><img src='"&R2&"'/></a>"
&V2&"</div><br />
<div class='modet'><span>"&AB2&" </span>| <a href='"&"/cat/"&K2&"/'>"&J2&"</a> | <a href='"&T2&"'>no reply</a>"

when i copy the whole cell into another application i get:
"<h3>test101</h3>
<a href='medana/'><img src='/content/'/></a>just some text</div><br />
<div class='modet'><span>18 November, 2007 </span>| <a href='/cat/'>some text</a> | <a href='/kria/'>no reply</a>"

how can i get rid of these quotation marks?
can i change excels "copy options" to avoid it?

thanks

Hi,

I want to save an excel xls file as the format of "formatted text
(space delimited)".
My question is given the excel file (from which, i know the width of
each column, and the value of each range), how do I know the number of
spaces between any two neighboring values in the saved text file? (I
want to be very specific about that number) For example, if in the
original excel file, range("A1") = "0.123" and range("B1") = "abc",
what would be the number of spaces between the "0.123" and "abc" in
the text file?

For column width I can get in vba (columns(1).width), but I guess the
number of spaces apparently also depends on the width of the specific
content within a cell. For example, for a cell with the content
"0.123" or "abc", the width of the content could be (the width of a
single digit * 4 + the width of a decimal point) or (the width of a
letter * 3). It should also depend on the font/format/size of that
cell. After I know the width of the column and the width of the
content, the number of spaces between neighboring values should be a
function of the difference in the widths in excel. But I can't find
any websites/books which would give me this kind of information.

Thanks a lot, greatly appreciate any help or tips.

Just to clarify, I am more interested in the answer to my question
above, rather than a workaround such as saving the file first, then
manually increase/decrease the spaces in the text file.

I have a data file continuously being created by software that monitors temperatures, voltages, current levels, etc. This data is stored in an ascii file called log.txt.

I created an Excel file to import the data on a daily basis. The problem is when the software updates every 30 seconds it completely shuts down the file and Excel too.

If I run through the steps manually, I don't have a problem because I get a box that make me open the file 'read-only'. But when I record a macro that follows the same exact steps, it doesn't record opening the file in read-only mode. The funny thing is, I added a command to close the text file immediately after copying and pasting the data to a different file and Excel still shuts down when the text file is accessed by the other program.

Is there a VBA command to make Excel open the file in read-only mode?

This is the code I currently use:

Workbooks.OpenText FileName:="C:LOG.TXT", Origin:=xlWindows, _ StartRow:=1, _
DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, _
Tab:=False, Semicolon:=False, _
Comma:=True, _ , Space:=False, _ Other:=False, _

Hi,

I want to save an excel xls file as the format of "formatted text
(space delimited)".
My question is given the excel file (from which, i know the width of
each column, and the value of each range), how do I know the number of
spaces between any two neighboring values in the saved text file? (I
want to be very specific about that number) For example, if in the
original excel file, range("A1") = "0.123" and range("B1") = "abc",
what would be the number of spaces between the "0.123" and "abc" in
the text file?

For column width I can get in vba (columns(1).width), but I guess the
number of spaces apparently also depends on the width of the specific
content within a cell. For example, for a cell with the content
"0.123" or "abc", the width of the content could be (the width of a
single digit * 4 + the width of a decimal point) or (the width of a
letter * 3). It should also depend on the font/format/size of that
cell. After I know the width of the column and the width of the
content, the number of spaces between neighboring values should be a
function of the difference in the widths in excel. But I can't find
any websites/books which would give me this kind of information.

Thanks a lot, greatly appreciate any help or tips.

Just to clarify, I am more interested in the answer to my question
above, rather than a workaround such as saving the file first, then
manually increase/decrease the spaces in the text file.

Need to link excel "cell" to a pre saved PDF file?? How do I do this??

Hyperlink is not the answer. I want a cell to have ## in it and then when
clicked on it will pull up the pDF I have saved in a different directory!
--
Red

Need to link excel "cell" to a pre saved PDF file?? How do I do this??

Hyperlink is not the answer. I want a cell to have ## in it and then when
clicked on it will pull up the pDF I have saved in a different directory!
--
Red

Hi,
I have to remove quotation marks in a txt-file during a certain procedure.
How can I do it using excel-macro?

I am working product feed management and submitting product feed to Google merchant center for my eCommerce vista stores. I have to convert my excel sheet to tab delimited file before submit to Google merchant center. Today, I found one critical issue with it. Tab delimited file contains quotation marks. But, excel sheet does not contain quotation mark. I want to give one example to know more about it.

Excel File:
1 Light 11" Outdoor Wall Sconce In Matte Black

Tab Delimited File:
"1 Light 11"" Outdoor Wall Sconce In Matte Black"

Excel File:
SKU Number: 52371Features & SpecificationsProducts Type:Wall SconceGlass / Shade Type:Cream GlassMaterial:Glass, MetalCollection:ChaumontUsage:InteriorStyle:Traditional / ClassicSuggested Room / Use:Indoor / OutdoorAssembly:Easy AssemblyNo.of Bulbs:1Bulbs Included:NoMax.Wattage Per Bulb :100 WVoltage:120 VoltBulb Base:A19 MediumOverall Dimensions:23" H X 11" W X 12" DWeight:25 lbs

Tab Delimited File:
"SKU Number: 52371Features & SpecificationsProducts Type:Wall SconceGlass / Shade Type:Cream GlassMaterial:Glass, MetalCollection:ChaumontUsage:InteriorStyle:Traditional / ClassicSuggested Room / Use:Indoor / OutdoorAssembly:Easy AssemblyNo.of Bulbs:1Bulbs Included:NoMax.Wattage Per Bulb :100 WVoltage:120 VoltBulb Base:A19 MediumOverall Dimensions:23"" H X 11"" W X 12"" DWeight:25 lbs"

How do I remove quotation marks from tab delimited file? Because, it is creating issue and generating errors in Google merchant center.

Hi all,

A simple query about references that include parts of the reference (eg
sheet name) in quotation marks.

When is this required?

What purpose does it serve?

Or possibly I am just confused and they are not actually used in this
context.

Thanks for viewing and/or replying.

--
heski
------------------------------------------------------------------------
heski's Profile: http://www.excelforum.com/member.php...o&userid=30581
View this thread: http://www.excelforum.com/showthread...hreadid=509210

Hello again,

I have posted already the problem to delete the code when sending a sheet via email and I have found the solution to the problem.

I tried to do the same when saving the file to specific folder, but i am facing problem.
The code that I am using now is:

Sub SaveFile()

Dim CurrentPath As String
Const PathToSaveTo As String = "C:Documents and SettingsAll UsersDocuments"
    'Store the current path
    CurrentPath = CurDir
    ''Change the path to the one we want
    'ChDrive "C"
    'ChDir PathToSaveTo
    ''or perhaps...
    SetCurrentDirectory PathToSaveTo    '"SomeServerSome Path"
    'test to see if the folder is already saved in the correct folder
    
             With ThisWorkbook
            If .Path <> PathToSaveTo Then
            .SaveAs FileName:=PathToSaveTo & .Name
                     Else
            'file is already saved in folder therefore just save it
            .Save
            
        End If
    End With
    ''Change the path back
    'ChDrive CurrentPath
    SetCurrentDirectory CurrentPath
    
End Sub
Can I add somewhere in this code the lines that will delete the code form the file?

 
Destwb.VBProject.VBComponents(ActiveSheet.CodeName).CodeModule.DeleteLines 1, _
  Destwb.VBProject.VBComponents(ActiveSheet.CodeName).CodeModule.CountOfLines


Hi all,

A simple query about references that include parts of the reference (eg sheet name) in quotation marks.

When is this required?

What purpose does it serve?

Or possibly I am just confused and they are not actually used in this context.

Thanks for viewing and/or replying.

I have an Excel 2003 SP2 file with columns that contain text that includes
double quotation marks. For example: Select "Yes" to process this form.

When I save the file as a CSV file, the double quotation marks are saved.
However, when I ftp the file to a Unix server, the double quotation marks are
removed from the file.

So....how can I preserve the double quotaion marks in the file when I ftp it
to a Unix server? The only soltuion I have found is to replace each double
quotation mark with 2 single quote marks prior to ftping it, then replacing
the 2 single quote marks with a double quotation mark on the Unix server
after ftping it.

Thanks for your help!
Shannon

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.

I need to format an Excel spreadsheet into a comma delimited text file. I have saved the file as a text file, I have suceeded at making it comma delimited. In the text import wizard I have also selected no text qualifier.

However when the file is imported everything works fine but some quotation marks pop up at the beginning and at the end. The quotations are not part of the file and I have already chosen no text qualifier. How do I get rid of the quotation marks so that they do not appear when the file is imported?

Attached is a sample of the file.

I am having a problem with the correct coding needed for a qotation mark

i wish to place a quotation mark around some text with coding
example "TEST" by using the code below this appears fine and looks fine when i save the file, however when i open that file in a .txt file i see """TEST"""

This is the coding i am using and can see it may be where the problem lies

	VB:
	
ActiveSheet.Range("C4").Value = """" & Sheets("PRODUCTS").Range("Y1").Value & """" 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
to correct it i have tried this

	VB:
	
ActiveSheet.Range("C4").Value = " & Sheets("PRODUCTS").Range("Y1").Value & " 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
but will not accept the coding ?, any help on this would be appreciated
Thanks.
Pete

I'll pay $30 (via Paypal) for this.

I have an Excel 2003 file that contains many columns of information: letters, numbers and symbols. I need each column of information saved as a text file. In the past, I have been doing this by hand with copy column, paste to Notepad, Save, copy/paste file name and repeat. This works, but I'd like to speed up the process.

I would like to open the Excel file and hit Control U. The macro starts at Column B of the worksheet. The macro takes all of the data from Column B and saves it as a text file with the file name that is located in cell B2. I want all of Column B in the file - don't remove B2 from the contents of the file. The number of rows in a column may vary. Any blank rows need to be kept.

I don't want a dialog box that asks if I want to save each time - it should be automatic. Then the macro goes to Column C and does the same thing. This process goes until it reaches Column IV and stops. A message box pops up that says "Done" or a progress bar showing completed would be fine too.

The file names that I supply in row 2 will always be unique. My columns will always go from B to IV. The information needs to appear in the text file exactly as it appears in the columns with no extra quotation marks added, commas, semi-colons, etc. (This is why I used Notepad before, but I'm sure there's a way around this issue in Excel.) If each row needs to have a space added at the end for the text file, that is fine.

I've attached an example. If you have any questions, let me know.

Thanks,

Adam

Hi, I am having a problem when I open text delimited files in excel. My data
in my files are separated by tabs, the problem is that I have blank lines
with no tabs which need to remain blank, but excel automatically inserts tabs
on these lines.

This was never a problem with office 2003, xp, 97. I deal with hundreds to
thousands of these files, so having to delete the added tabs after I save the
files in excel is extremely annoying.

Is there any way to fix this?

thanks,

finn

I'm using Excel VBA to load values from worksheet cells and then writing
these to a .csv file. My problem is that when the CSV is viewed in
NOtepad/wordpad, the text field value are enclosed in quotation marks ".

When I first did this they were'nt and I can't think what's changed. Also
not all text fields appear to have had quotation marks

I've tried applying different fomatting on the sheet and saving as .txt
rather than .csv but no difference

I need to send the file to a guy whose loader can't cope with quotation
marks but frankly for the moment I'm stumped.

Any ideas?

ps the file open line is :
Open file_path For Append Access Write As #1

Cheers

I'm trying to save an excell spreadsheet as a (tab delimited) text file.
Everytime I try to save it excel decides to add quotes to some of my cells.
How do I get it to stop doing this?
The cells it adds quotes to are cells that already have quotes in them, it
adds extra quotes around the whole cell. I really don't want that
Also cells that have numbers with commas (ie a cell with 64,4) it decides
needs quotes.
So my text file has "64,4" in it instead of 64,4 and "date "4-8-05""
instead of date "4-8-05"

i have technical preview not beta. beta come out this month they say.
anyway i see nothing different in saving to text format. no code page
selcting.

"Greg Lovern" > wrote in message
...
| For those of you using the Excel 2010 beta -- when saving as text,
| does it allow choosing the target codepage, as Word allows?
|
| For example, if I'm running English Windows, can I save a text file
| from Excel as "Japanese (Shift-JIS)" encoding?
|
| Or does it still only save as the currently active Windows codepage?
|
|
| If my question is not clear:
|
| in Word, if you save as "Plain Text (*.txt)", you get a "File
| Conversion" dialog, which allows you to select the "Other encoding"
| radio button, then choose from a long list of codepages in a listbox.
|
| Word has had this feature at least since 2003, not sure offhand about
| earlier. Just wondering if we'll have it in Excel 2010.
|
| What we're doing now is automating Word from Excel to open the text
| file we save from Excel, then save it as Shift-JIS. But besides taking
| more time, it requires Word. It would be nice to be able to do it
| directly from Excel.
|
|
|
| Thanks,
|
| Greg