Solved

Save as CSV in VBA

Posted on 2007-11-27
14
7,373 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 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

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

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

Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

726 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