×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

MS Access VBA to Save Excel XLS as CSV

MS Access VBA to Save Excel XLS as CSV

MS Access VBA to Save Excel XLS as CSV

(OP)
I am using MS Access 2013 and am using VBA and late binding (No Excel Reference in VBA) to take an Excel file perform some processes and then Save As a CSV file. Everything works as desired except the Save As portion.

Dim db As DAO.Database
Dim RS As DAO.Recordset
Dim RS2 As DAO.Recordset
Dim RS3 As DAO.Recordset
Dim Grid As String
Dim GridN As Integer
Dim GridTotal As String
Dim CIISname As String
Dim objexcel_app As Object
Dim xlsExcel_wkbook As Object
Dim xlsExcel_sheet As Object
Dim xlsExcel_range As Object


Set objexcel_app = CreateObject("Excel.Application")
objexcel_app.Visible = False
Set xlsExcel_wkbook = objexcel_app.Workbooks.Open("C:\Deal\New_NMTC_TLR_Note.xls")
Set xlsExcel_sheet = xlsExcel_wkbook.sheets("New_NMTC_TLR_Note")
Set db = CurrentDb

objexcel_app.DisplayAlerts = False
xlsExcel_sheet.Cells(5, 5).EntireRow.Delete
objexcel_app.activeworkbook.Save

'Here is where the problem is, I have tried different combinations of the 2 lines below (never both at the same time). Both lines will not compile and gets hung up on the FileFormat:=xlCSV as a not recognized variable. I have also tried including FileFormat:=xlCSVWindows. That didn't work either. Any suggestions would help. I also have tried using a Procedure on the web called ConvertXls2CSV which appeared to work fine until I noticed it stripped some leading zeros of column because it was treating a string as numeric so I don't trust using it.

xlsExcel_wkbook.SaveAs FileName:="C:\deal\New_NMTC_TLR_Note.csv", FileFormat:=xlCSV, CreateBackup:=False
objexcel_app.activeworkbook.SaveAs FileName:="C:\deal\New_NMTC_TLR_Note.csv", FileFormat:=xlCSV, CreateBackup:=False



objexcel_app.activeworkbook.Close
objexcel_app.Quit


Set objexcel_app = Nothing
Set xlsExcel_wkbook = Nothing
Set xlsExcel_sheet = Nothing
Set xlsExcel_range = Nothing

RE: MS Access VBA to Save Excel XLS as CSV

Hi,

xlCSV is an Excel Constant.

Unless you have a reference set to the Microsoft Excel Application Object Library, you’ll need to use the correct numeric value for that constant.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: MS Access VBA to Save Excel XLS as CSV

(OP)
So do you mean I would put a "6" there instead?

RE: MS Access VBA to Save Excel XLS as CSV

... also
“I noticed it stripped some leading zeros”

Okay THAT constant worked!

BUT, a number FORMATTED with leading zeros is just a number.

You must CONVERT your number to a STRING using a routine like this...

CODE

Dim r As Excel.Range

With xl.ActiveSheet
   For Each r In .Range(.Cells(1,”A”), .Cells(1,”A”).End(xlDown))
      r.NumberFormat = “@“
      r.Value = r.Value
   Next
End With 


You might need to FIX myQUOTES.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: MS Access VBA to Save Excel XLS as CSV

(OP)
I have the Cstr in MS Access on the transferred Query and I have the formatting set in Excel - when it is finished in the XLS portion the leading zeros are present in the XLS saved file - as soon as it is converted to CSV, I used the constant "6", no errors or messages, but the CSV is stripping the leading zeros. If I take the XLS and manually perform a Save As from the menu bar it does not strip the leading zeros.

Did you say you thought there might be another constant number to use?

Thank you,
Charles

RE: MS Access VBA to Save Excel XLS as CSV

(OP)
Here is a portion of the data. The first example is the transferred and formatted XLS (notice the true data starts on Line 5 as this gov't report has a strange meta data on the Lines 1-4 as the intended upload file, thus I transfer to Line 5 and as part of the process Line 5 is deleted because it would of just have been another set of Column headers

Label Project Number Originator Transaction ID
Type STRING STRING
Options
Help Text
00001001 HCCS01
00001001 HCCS02
00001001 HCCS03
00001002 BVP01
00001002 BVP02
00001002 BVP03
00001004 EHTP01



Now the Converted data using the constant 6 and the active workbook Save As

Label Project Number Originator Transaction ID
Type STRING STRING
Options
Help Text
1001 HCCS01
1001 HCCS02
1001 HCCS03
1002 BVP01
1002 BVP02
1002 BVP03
1004 EHTP01


RE: MS Access VBA to Save Excel XLS as CSV

The constant is NOT the issue.

“and I have the formatting set in Excel”

WHAT formatting are you referring to and WHEN, in the process are you applying this format?

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: MS Access VBA to Save Excel XLS as CSV

(OP)
Z = xlsExcel_sheet.UsedRange.rows(xlsExcel_sheet.UsedRange.rows.Count).row
Set xlsExcel_range = xlsExcel_sheet.Range("B1:B" & Z)
xlsExcel_range.NumberFormat = "@"
objexcel_app.DisplayAlerts = False
xlsExcel_sheet.Cells(5, 5).EntireRow.Delete
objexcel_app.activeworkbook.SaveAs FileName:="C:\deal\New_NMTC_TLR_Note.csv", FileFormat:=6, CreateBackup:=False
objexcel_app.activeworkbook.Close


As you can see in the first data example (not visible but it even has the little green triangles in the left corner) above it saved as an XLS properly with the leading zeros. The second example shows the CSV file after the Save As and it being stripped

RE: MS Access VBA to Save Excel XLS as CSV

(OP)
xlsExcel_sheet.Cells(5, 5).EntireRow.Delete
objexcel_app.activeworkbook.Save
objexcel_app.activeworkbook.SaveAs FileName:="C:\deal\New_NMTC_TLR_Note.csv", FileFormat:=6, CreateBackup:=False

I also have the basic Save included. This is all performed from MS Access as part of the Transfer Spreadsheet to Excel 97-2003 XLS. Should the Save As in this code be the exact same process as if I went to the menu bar on an XLS file and performed Save As to CSV format? Because if I manually from the menu bar, it does keep leading the zeros. If I could replicate the manual process in VBA I believe it would work

RE: MS Access VBA to Save Excel XLS as CSV

Quote:

xlsExcel_range.NumberFormat = "@"

Changing the Number Format, which is necessary, HOWEVER, that ACTUALLY changes NOTHING!

To change a NUMBER 1 (for instance the number 1 is STORED as hex 01) to CHARACTER “1” that value is 31. There is a CONVERSION that NumberFormat cannot do.

The leading ZEROS must be physically in the cell, not a Format with leading ZEROS.

That’s why I posted that little code clip.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: MS Access VBA to Save Excel XLS as CSV

By some process, the data comes to exist in the sheet. What is that process?

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: MS Access VBA to Save Excel XLS as CSV

CODE

Dim r As Excel.Range

With xl.ActiveSheet
   For Each r In .Range(.Cells(1,”A”), .Cells(1,”A”).End(xlDown))
      r.NumberFormat = “@“
      r.Value = FORMAT(r.Value, “00000000”)
   Next
End With 

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: MS Access VBA to Save Excel XLS as CSV

Your column B DATA is okay.

If you SaveAs a .csv and, yes, xlCSV has a value of 6.

I don't see anything that jumps out to me.

You might try recording a macro that does the SaveAs .csv, remembering to replace the constant.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: MS Access VBA to Save Excel XLS as CSV

(OP)
Here is something interesting, if I open the CSV file in Notepad the data is unaffected. The CSV file loses the leading "0"s upon opening in Excel. Makes me wonder if whatever gov't program uploads the CSV if it then would in fact have the original correct data. As you can see the leading zeros are in the file. It is just when I open it in Excel they disappear.

I learned a bit about this at the website:
https://support.3dcart.com/knowledgebase/article/V...

partial csv notepad:

,00001001,HCCS01,,RERHCOM,TERM,ACTIVE,08/16/2013,"2,579,318.00",NEWORIGINATION,,ACQUISITION,REHABILITATION,,0.00,4.42,4.42,FIXED,0.00,NONAMORT,,84,84,NONE,SECOND,OTHER,"9,400,000.00",,"350,000.00",NO,,NO,NO,NO,YES,YES,6.50,YES,YES,OTHER,CDFI,YES,"19,344.00",YES,18,NO,300,,,YES,RE,BANKS,,ORIG,"2,579,318.00",,,,,,0.00,0.00,,0.00
,00001001,HCCS02,,RERHCOM,TERM,ACTIVE,08/16/2013,"598,255.00",NEWORIGINATION,,ACQUISITION,REHABILITATION,,0.00,4.42,4.42,FIXED,0.00,OTHER,,137,360,NONE,SECOND,OTHER,"9,400,000.00",,"350,000.00",NO,,NO,NO,NO,YES,YES,6.50,YES,YES,OTHER,CDFI,YES,"4,486.00",YES,18,NO,300,,,YES,RE,BANKS,,ORIG,"598,255.00",,,,,,0.00,0.00,,0.00
,00001001,HCCS03,,RERHCOM,DEBTEQTY,ACTIVE,08/16/2013,"1,232,427.00",NEWORIGINATION,,ACQUISITION,REHABILITATION,,0.00,4.42,4.42,FIXED,0.00,OTHER,,137,360,NONE,SECOND,OTHER,"9,400,000.00",CONVDEBT,"350,000.00",NO,,NO,YES,YES,YES,YES,6.50,YES,YES,OTHER,CDFI,YES,"9,243.00",YES,18,NO,300,,,YES,RE,BANKS,,ORIG,"1,232,427.00",,,,,,0.00,0.00,,"1,222,427.00"
,00001002,BVP01,,RECOCOM,TERM,ACTIVE,11/21/2013,"3,978,439.00",NEWORIGINATION,BOTH,TAKEOUT,,"3,920,185.32",0.00,3.38,3.

RE: MS Access VBA to Save Excel XLS as CSV

Never ever OPEN a .csv directly, because...of what you’re experiencing!!!

IMPORT the .csv into a Workbook via Date > Get external data > From Text files

In the IMPORT parsing Steps, you can specify that column B should get imported as TEXT and other DATE columns as DMY, or YMD.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: MS Access VBA to Save Excel XLS as CSV

(OP)
Thanx

The prior system used an XLS for sending to the Gov't and now their upgraded system changed to a CSV. I asked them (no response) why they would go from a more usable file in XLS that can be more easily formatted to a CSV which if I am not mistaken takes settings from an individual computer's settings. I think because the data is there I am fine at least the CSV contains the correct data - its just a little tough for an end user to truly verify without going through the import process into Excel and formatting that column. I was going to delete the XLS on finishing so as not to confuse the end user with 2 files, but I am wondering if that might be a useful copy file for review. Another reason why I prefer Access over Excel, for consistent data types in a field/column.

RE: MS Access VBA to Save Excel XLS as CSV

FAQ68-7375: Be Aware: Excel can Change Your Data

Yes, I agree. Excel is not a database, although you can use a spreadsheet like a db table and do queries very similar to Access.

But data integrity is not an Excel forte. You have to know what you’re doing.

Leading ZEROS in an all numeric string WILL be changed to a number by Excel, unless you take proper precaution (emphasis on PRE)

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close