[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 7493
  • Last Modified:

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

0
WileyPowers
Asked:
WileyPowers
  • 4
  • 4
  • 3
  • +2
1 Solution
 
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
 
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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
 
Computer101Commented:
PAQed with points refunded (500)

Computer101
EE Admin
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

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 4
  • 4
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now