MS Access VBA to Save Excel XLS as CSV 1

[Knicks]

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

 

[SkipVought]

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,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Knicks]

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

 

[SkipVought]

... 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...

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,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Knicks]

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

 

[Knicks]

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

 

[SkipVought]

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,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Knicks]

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

 

[Knicks]

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

 

[SkipVought]

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,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[SkipVought]

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

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[SkipVought]

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 = [b]FORMAT(r.Value, “00000000”)[/b]
   Next
End With

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Knicks]

The leading zeros are there right up to the conversion. See the attached XLS file which occurs at the SAVE portion of the code

 

[SkipVought]

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,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Knicks]

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/knowledg...orted-csv-data-get-converted-to-weird-formats

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.

 

[SkipVought]

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,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Knicks]

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.

 

[SkipVought]

Faq68-7375

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,

_{Just traded in my OLD subtlety...
for a NUance!}