Syntax for saving an exported Excel file as csv

[Chris Freeman]

I have a function that is exporting an Excel file from the database, and
everything works fine. I want to, in the Access code, open the file and save
as a csv file. So far I can SaveAs with a csv extension, but it does not
change the format to CSV, like SaveAs from within Excel:

D = Format(Date, "mmddyy")
File = "APECS_Address_Import.csv"
Folder = "\\APECS Conversion\"
FileName = Folder & File

Set xlx = CreateObject("Excel.Application")
'hide the file saveas function from user
xlx.Visible = False
Set xlw = xlx.Workbooks.Open(FileName)
Set xl = xlw.Worksheets(1)
xl.Name = "APECS Update"
xlw.SaveAs (Filename)
xlx.DisplayAlerts = False
xlw.Close
xlx.Quit

When I track the SaveAs code via an Excel macro, I have:

ActiveWorkbook.SaveAs FileName:= _
"\\apecs conversion\APECS_Address_Import.csv", FileFormat:=xlCSV _
, CreateBackup:=False

I see that I'm not incorporating the FielFomat:=xlCSV, but can't figure out
how to incorprate it. I tiried:

xlw.SaveAs (Filename,FileFormat)

but this was a code violation.

Any suggestions?

TIA

 

[Douglas J. Steele]

"\\apecs conversion\APECS_Address_Import.csv" isn't a valid UNC. You need
\\server\share\file, not just \\server\file

 

[Chris Freeman]

Doug,
Yes, I know, but as I'm a contractor at a client site, they don't like me
posting server info in public forums.

How do I apply the FileFormat in this saveas function, or is there another
way to do a SaveAs on an Excel file to convert it from Excel to csv format?


--
Chris Freeman
IT Project Coordinator

"\\apecs conversion\APECS_Address_Import.csv" isn't a valid UNC. You need
\\server\share\file, not just \\server\file

 

[Douglas J. Steele]

I can understand not posting personal information, but when you change it,
at least make it valid!

Try:

xlw.SaveAs FileName:= _
"\\apecs conversion\APECS_Address_Import.csv", FileFormat:=xlCSV _
, CreateBackup:=False


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Doug,
Yes, I know, but as I'm a contractor at a client site, they don't like me
posting server info in public forums.

How do I apply the FileFormat in this saveas function, or is there another
way to do a SaveAs on an Excel file to convert it from Excel to csv
format?

 

[Chris Freeman]

Sorry Doug,
That syntax causes an Error: SaveAs method of Workbook class failed.

This was the same thign I tried before, but I can't get the SaveAs name and
format to work together.

Any other ideas?

 

[Douglas J. Steele]

Hmm. I'm sure I've done it successfully, but unfortunately I don't have time
to test at the moment.

You sure you have a valid UNC for the file name, and that you have
permissions on the folder?

 

[Chris Freeman]

Doug,
Yeah, the server path is correct, and I can run this without the Fileformat
parameter, and the file name does changes, and it is saved, but saved as an
Excel file with a csv extension. Unfortunately, it works like just changing
the ext on an Excel, so if you try to open in open, you get all of the
extraneous coding that goes along with it, and not clean text file.

i even tried adding the Fileformat as a separate line item:
xlw.SaveAs Fileformat:=xlCSV

but it does not save the formatting.

 

[Douglas J. Steele]

Hmm. I just cobbled together a quick example, and it saved as a normal CSV
file for me using essentially

xlw.SaveAs FileName:= _
"\\apecs conversion\APECS_Address_Import.csv", FileFormat:=xlCSV _
, CreateBackup:=False

Office 2003

 

[Chris Freeman]

Doug,
Maybe its my computer setup or some other reason that I'm unable to
discover, but in reviewing a couple of other threads, I came across a
discussion in 2007 that led me to this page:
http://www.pcreview.co.uk/forums/thread-1924416.php

Which led me to this code change: xlw.SaveAs Fileformat:=6

This table held the key:

xlWorkbookNormal -4143
xlHtml 44
xlWebArchive 45
xlXMLSpreadsheet 46
xlCSV 6
xlCurrentPlatformText -4158
xlUnicodeText 42
xlTextPrinter 36

xlCSVMac 22
xlCSVMSDOS 24
xlCSVWindows 23
xlTextMac 19
xlTextMSDOS 21
xlTextWindows 20

So now I'm finally able to do the proper SaveAs function.

 

[Douglas J. Steele]

I'm guessing that you don't have a reference set to Excel, and that you
haven't told Access to require that all variables be defined.

The former isn't that bad (although it would explain why xlCSV doesn't work
for you): the latter is, in my opinion, absolutely essential. Without it,
you'll never know if you haven't defined xlCSV, and Access will merrily
accept it as 0. Every code module should have Option Explicit at the top
(usually the second line, although it can be the first should you want). To
have Access automatically add that line to all future modules, go into the
VB Editor, select Tools | Options from the menu bar, and, on the Editor tab,
ensure "Require Variable Declaration" is checked. While it may seem like a
lot of work to ensure everything's been declared, once you get used to it,
it'll save you literally hours of debugging effort.

 

[Chris Freeman]

Doug,
You're absolutely correct. I checked the references before and saw that i
had the MS Office 11.0 object library, just assumed that covered it. and we
know what assuming leads to...

so once i added the MS Excel 11.0 object library everything fell into place.
thanks for the nudge.

 

[Douglas J. Steele]

Glad you got it working. Hopefully you've also told Access to insist that
all variables have been declared too. See how much effort you could have
saved if you had? <g>