Exporting from Access to Excel - No Numbers?

eblieb

Registered User.
Local time
Yesterday, 23:35
Joined
Mar 11, 2013
Messages
11
I am using
Code:
Dim xlApp As Excel.Application
    Dim xlWB As Excel.Workbook
    Dim xlWS As Excel.Worksheet
    Dim acRng As Variant
    Dim xlRow As Integer
 
    Dim qry As QueryDef
    Dim rst As Recordset
    Set xlApp = New Excel.Application
    Set xlWB = xlApp.Workbooks.Open(Me![txtExportFile])
    Set xlWS = xlWB.Worksheets("WORKLOAD")
 
    xlRow = (xlWS.Columns("A").End(xlDown).Row)
 
    Set qry = CurrentDb.QueryDefs("queryERCIC")
    Set rst = qry.OpenRecordset
 
    Dim c As Integer
    c = 12
    xlRow = xlRow + 1
 
    Do Until rst.EOF
        For Each acRng In rst.Fields
            xlWS.Cells(xlRow, c).Formula = acRng
            c = c + 1
        Next acRng
        xlRow = xlRow + 1
        c = 1
        rst.MoveNext
        If xlRow > 1000 Then GoTo rq_Exit
    Loop
 
rq_Exit:
    rst.Close
    Set rst = Nothing
    Set xlWS = Nothing
    xlWB.SaveAs "J:\Inspection\TQAPublic\Databases\WIP\WorkloadProcessor\" & Me![txtQuarter] & "-ERCIC.xls"
    xlWB.Close acSaveNo
    Set xlWB = Nothing
    xlApp.Quit
    Set xlApp = Nothing


to export a query into an excel template. In my table that the query pulls the data from the field is defined as a number, but when I export to excel, the numbers are not stored as numbers, but as text... Is there a way to fix this?





EDIT:
I fixed it by using
Code:
Private Sub Workbook_Open()
    Range("N12:P1012").Select 'specify the range which suits your purpose
    With Selection
        Selection.NumberFormat = "General"
        .Value = .Value
    End With

End Sub

In the template....
 
Last edited:

Users who are viewing this thread

Back
Top Bottom