Exporting to Excel

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi

I am exporting 35 queries, using Access 97 to excel 97 and 2000, there are
no problems with the actual exporting. The problem is the formatting in
excel ie should be 000000020307 but when export looses all the 0 ie 20307.
In access its a text datatyp and the column consists of numerical and text
data.

the code used for this is

Private Sub cmdExpExcel_Click()
Dim dbData As Database
Dim rstData As Recordset
Dim lngRow As Long
Dim strRange As String
Dim strCriteria As String
Set dbData = CurrentDb
' Declare an object variable to hold the object
' reference. Dim as Object causes late binding.
Dim ExcelSheet As Object
Set ExcelSheet = CreateObject("Excel.Application.9")
Set ExcelSheet = CreateObject("Excel.Sheet")
' Make Excel visible through the Application object.
ExcelSheet.Application.Visible = True
' Place some text in the first cell of the sheet.

ExcelSheet.Application.cells(1, 1).Value = "MIMS PIER COMPARISON " 'Cell
A1
ExcelSheet.Application.cells(2, 1).Value = "Mims_Pier_No" 'Cell
A2
ExcelSheet.Application.cells(2, 2).Value = "Pier_Pier No" 'Cell B2
ExcelSheet.Application.cells(2, 3).Value = "Mims_Scheme_No"
'Cell C2
ExcelSheet.Application.cells(2, 4).Value = "Pier__Scheme_No"
'Cell D2
ExcelSheet.Application.cells(2, 5).Value = "Mims_Title" 'Cell E2
ExcelSheet.Application.cells(2, 6).Value = "Pier_Title"
'Cell F2
ExcelSheet.Application.cells(2, 7).Value = "MIMS_NO "
'Cell G2
ExcelSheet.Application.cells(2, 8).Value = "Estimate Status"
'Cell H2

Set rstData = dbData.OpenRecordset( _
"SELECT * FROM qry_MimsPiersComparison", dbOpenDynaset)
'Open qurey

lngRow = 3 'above is for report heading

' read through the qry_MimsPiersComparison"
With rstData
rstData.MoveFirst 'Move to first record in qry_MimsPiersComparison"

Do While Not .EOF
ExcelSheet.Application.cells(lngRow, 1).Value =
Nz(rstData.Fields(0)) 'Mims_Pier No
ExcelSheet.Application.cells(lngRow, 2).Value =
Nz(rstData.Fields(1), 0) 'Pier_Pier_No
ExcelSheet.Application.cells(lngRow, 3).Value =
Nz(rstData.Fields(2), 0) 'Mims_Scheme_NO
ExcelSheet.Application.cells(lngRow, 4).Value =
Nz(rstData.Fields(3), 0) 'Pier_Scheme_NO
ExcelSheet.Application.cells(lngRow, 5).Value =
Nz(rstData.Fields(4), 0) 'Mims_Title
ExcelSheet.Application.cells(lngRow, 6).Value =
Nz(rstData.Fields(5), 0) 'Pier_Title
ExcelSheet.Application.cells(lngRow, 7).Value =
Nz(rstData.Fields(6), 0) 'MIMS_NO
ExcelSheet.Application.cells(lngRow, 8).Value =
Nz(rstData.Fields(7), 0) 'Estimate Status

rstData.MoveNext
lngRow = lngRow + 1
Loop
End With

thank you and help
 
To prevent Excel from interpreting the strings of digits as numeric
values, prefix them with an apostrophe ', e.g.

....Cells(lngRowNum,1).Value = "'" & Nz(rstData.Fields(0))

If you want them as numbers formatted with leading zeroes, you'll need
to have your code apply the appropriate format to the Excel cells.
 
Back
Top