Export access 2003 data to excel 2007 via VBA

Hi all.

We have an access 2003 form that when the user clicks on a button (btnExport) it will export some data from a table to excel 2007. The problem we have is that some of the columns, for example "QTY" is exporting with a date format (8/1/1900).

What is the syntax for making all the values in a column into a number?

Below is the code where we export the data to excel.

Thank you in advance!
Set rsExcel = CurrentDb.OpenRecordset("SELECT   tblmyTable.fields  FROM myTable")

    xlWs.Cells.CopyFromRecordset rsExcel


    xlWs.Rows("1").Insert

    xlWs.Cells(1).Value = "Debit #"
    xlWs.Cells(2).Value = "Cust #"
    xlWs.Cells(3).Value = "Inv #"
    xlWs.Cells(4).Value = "Inv Date"
    xlWs.Cells(5).Value = "Contract"
    xlWs.Cells(6).Value = "Vendor #"
    xlWs.Cells(7).Value = "QTY"
    xlWs.Cells(8).Value = "Sales"
    xlWs.Cells(9).Value = "Variance"

Open in new window

printmediaAsked:
Who is Participating?
 
printmediaConnect With a Mentor Author Commented:
This is what I was looking for. Thanks for your help anyway!
xlWs.Columns("Q").NumberFormat = "General"  
xlWs.Columns("S").NumberFormat = "$#,##0.00" 
xlWs.Columns("T").NumberFormat = "$#,##0.00" 
xlWs.Columns("U").NumberFormat = "$#,##0.00" 

Open in new window

0
 
Rey Obrero (Capricorn1)Commented:
you can try

 xlWs.Cells(rowNumber,7).Value = clng(rsExcel("Qty"))
0
 
printmediaAuthor Commented:
But I won't know how many rows there will be that's why I need it to be for the column instead.
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
Rey Obrero (Capricorn1)Commented:
i just showed you the way as the answer to your question,

What is the syntax for making all the values in a column into a number?
0
 
printmediaAuthor Commented:
Your code says "rowNumber" what do I put there.
0
 
Rey Obrero (Capricorn1)Commented:
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.