Solved

Save as CSV in VBA

Posted on 2007-11-27
14
7,334 Views
Last Modified: 2012-06-27
Hello gurus!

When saving an excel spreadsheet as a .csv file, I get automatic text delimiters " " wrapped around the text strings.
"A_Y,AZ00999955 NO,Equity,14168300"
"A_Y,99990277171 NO,Equity,805000"


This only happen when I use VBA to save it and not when doing the task manually. Then it looks like it should:
A_Y,AZ00999955 NO,Equity,14168300
A_Y,99990277171 NO,Equity,805000

How do I avoid getting the delimiters as text-wrappers?
Thanks in advance, Wiley


objWorkbook.SaveAs "c:\bbup3.csv", xlCSVMSDOS

Open in new window

0
Comment
Question by:WileyPowers
  • 4
  • 4
  • 3
  • +2
14 Comments
 
LVL 25

Expert Comment

by:imitchie
ID: 20356901
do you only have a single column containing commas?
0
 
LVL 25

Expert Comment

by:imitchie
ID: 20356918
if you have commas in cells, Excel adds the double quote text wrappers so that whatever is consuming the CSV data knows that the commas are not delimiters and are part of a single "value"
0
 

Author Comment

by:WileyPowers
ID: 20356993
Thanks for taking interest in this query.

To answer your first question; the data are in a single column.  

I do see your point, but the double quote text wrappers are not applied when I save the file manually as a csv-file - and this is what I would like the vba script to do as well: to drop the double quote text wrappers. Is this possible?
0
 
LVL 25

Expert Comment

by:imitchie
ID: 20357115
I have been trying for the last half hour, and no combination of output save as is giving me unquoted data. I'm surprised to hear you say that it's possible.

I would have VBA copy the sheet, perform "Text to columns" on the column delimited by comma, then save that as a .CSV (which is now truly comma delimited)
0
 
LVL 26

Expert Comment

by:EDDYKT
ID: 20357252
you can record your macro when you save manually and see what's different
0
 

Author Comment

by:WileyPowers
ID: 20357256
What I'm aiming to acheive in the vba script is the following result - which will be produced if you do the following:
1) Paste the following into a worksheet in cell a1:
A_F,PO345 UI,Equity,141300
2) File -> Save As; File name:  abc; Save as type: CSV (Comma delimited) (*.csv)
3) Shut down the application. Confirm the pop-up promts.
4) Open Explorer. Locate the file. Right click on the file. Open it with Notepad.
The result of this is that there is no double quote text wrappers present.
Why can not the same result be reached when opting to perform the task in vba?
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 26

Expert Comment

by:EDDYKT
ID: 20357366
This is the output
"A_F,PO345 UI,Equity,141300"

have "" around

?>
0
 

Author Comment

by:WileyPowers
ID: 20357714
hi Eddykt -  not sure if i grasp what your saying. Point is that when i view the output file after having produced the file in the csv-format manually, then, the rows have no double quotes. And this what i aim to acheive in a vba script. Could you pls restate your suggestion?
0
 
LVL 26

Expert Comment

by:EDDYKT
ID: 20357791
that's what i told you. I follow your procedure and have "" around the output

I cannot reproduce what you said by using manual save.
0
 
LVL 25

Expert Comment

by:imitchie
ID: 20360793
Wiley, are you sure you're opening in Notepad and not Excel, which strips it's own "s when viewing? It does not look like anyone else can save to CVS without having the ""s, because it doesn't make sense to have a Comma-SEPARATED file with Commas within a single value

If you're ok with VBA, then just code this:

I would have VBA copy the sheet, perform "Text to columns" on the column delimited by comma, then save that (new sheet) as a .CSV (which is now truly comma delimited)
0
 

Author Comment

by:WileyPowers
ID: 20463151
Guru's - this problem has been difficult to communicate. However, I am positive that the advice you have been giving me is in accordance with the input I have given - but for some reason the problem remained unsolved. Coincidently I happened upon the solution to this problem while searching Ozgrid, http://www.ozgrid.com/forum/showthread.php?t=63300, which is posted by rpaulson. The solution is greatly appreciated. Thanks, Wiley.
0
 
LVL 1

Accepted Solution

by:
Computer101 earned 0 total points
ID: 21000155
PAQed with points refunded (500)

Computer101
EE Admin
0
 
LVL 12

Expert Comment

by:Jeff Darling
ID: 21323541
That solution presented on ozgrid is not a good solution if the data has any commas in it.  It will definitely produce a file that is improperly delimited.  Quotes are used in CSV files to delimit fields that have delimiter characters and the solution proposed on ozgrid does not account for delimiters in the data.
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

759 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now