Save as CSV in VBA

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

WileyPowersAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Computer101Connect With a Mentor Commented:
PAQed with points refunded (500)

Computer101
EE Admin
0
 
imitchieCommented:
do you only have a single column containing commas?
0
 
imitchieCommented:
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
WileyPowersAuthor Commented:
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
 
imitchieCommented:
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
 
EDDYKTCommented:
you can record your macro when you save manually and see what's different
0
 
WileyPowersAuthor Commented:
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
 
EDDYKTCommented:
This is the output
"A_F,PO345 UI,Equity,141300"

have "" around

?>
0
 
WileyPowersAuthor Commented:
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
 
EDDYKTCommented:
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
 
imitchieCommented:
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
 
WileyPowersAuthor Commented:
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
 
Jeff DarlingDeveloper AnalystCommented:
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
All Courses

From novice to tech pro — start learning today.