Solved

Save as CSV in VBA

Posted on 2007-11-27
14
7,391 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
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
 
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 13

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

688 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