• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 571
  • Last Modified:

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

0
printmedia
Asked:
printmedia
  • 3
  • 3
1 Solution
 
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
 
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
printmediaAuthor Commented:
Your code says "rowNumber" what do I put there.
0
 
Rey Obrero (Capricorn1)Commented:
0
 
printmediaAuthor 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

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

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