I am using
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
In the template....
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: