Go Premium for a chance to win a PS4. Enter to Win


Excel: Add double quotes on exported CSV from field

Posted on 2011-09-13
Medium Priority
Last Modified: 2012-06-27
I tried to export CSV with this command:
ActiveWorkbook.SaveAs Filename:=fileresult, FileFormat:=xlCSV, CreateBackup:=False

But the exported CSV doesn't have double quote for each cell except the "Wrap Text" ones (item.csv as attached)

Then i tried to add this command:
 Set SrcRg = ActiveSheet.UsedRange
    For Each CurrRow In SrcRg.Rows
        For Each CurrCell In CurrRow.Cells
            CurrCell.Value = Chr(34) & CurrCell.Value & Chr(34)

it visually display the double quotes, but when i open the raw source of CSV, it consist 3 double quotes, for example:

(Item2.csv as attached)

So... how to solve this problem ?

Question by:veematics
LVL 17

Accepted Solution

Shanmuga Sundaram earned 2000 total points
ID: 36534343
I dont know how to fore see your problem. the quickest option, since you know vba is to write to a text file.so try as below

Sub main()

Open "C:\results.csv" For Output As #1
Set SrcRg = ActiveSheet.UsedRange
    For Each CurrRow In SrcRg.Rows
        For Each CurrCell In CurrRow.Cells
            Print #1, """" & CurrCell.Value & """,";
        Print #1, ""
Close #1

End Sub

Expert Comment

ID: 36534353
Please see the following website if it can give you a hint.

LVL 17

Expert Comment

by:Shanmuga Sundaram
ID: 36534361
please see the excel with macro and results attached
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

LVL 46

Expert Comment

ID: 36536661
The quotes are being added to the "wrap text" fields because they contain one or more comma characters.

Since the Chr(34) character is, itself,  used as a delimiter in CSV files, they must be handled differently.  The three consecutive quote character sequences you see are the Chr(34) character that is being delimited by quote characters.

I'm not sure I fully understand what you need.  Have you considered tab-separated (delimited) values, rather than comma-separated?
LVL 53

Expert Comment

ID: 36962679
I've requested that this question be deleted for the following reason:

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.

Author Comment

ID: 36962680
Hello i was out of town for almost a month...

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
If you are a mobile app developer and especially develop hybrid mobile apps then these 4 mistakes you must avoid for hybrid app development to be the more genuine app developer.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
Introduction to Processes

926 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question