Exporting to Excel gives formatting error

[dmarsh16946]

Using Access 2003, have procedure to export current form's data to Excel 2003.

Works fine except Excel flags cells that are formatted as text in Access but look like numbers to Excel. Whilst they are numeric in Access they may start with 0, hence need to be text.

Ideas - it's an irritation more than a problem?

This is the code doing the exporting. It's Sortcode and AccountNo that lead to the error indicators in Excel.

'Send to Excel template
Dim objXL As Excel.Application
Dim objWkb As Excel.Workbook
Dim objSht As Excel.Worksheet
Dim rst As Recordset
Dim iRow As Integer

Set objXL = New Excel.Application
objXL.Visible = True
Set objWkb = objXL.Workbooks.Open("c:\MSRC\BACS.xlt") ' Define the template to use
Set objSht = objWkb.Worksheets("sheet1") 'sheet1 unless the template's first worksheet has been given another name

iRow = 2 ' this starts the copying in row 2, assuming the template's headers take up row 1

Set rst = CurrentDb.OpenRecordset("SELECT * FROM tblBACSData")
rst.MoveFirst

Do While Not rst.EOF

'Insert the field names to be inserted
objSht.Cells(iRow, 1).Value = rst!Sortcode
objSht.Cells(iRow, 2).Value = rst!AccountNo
objSht.Cells(iRow, 3).Value = rst!PTYID
objSht.Cells(iRow, 4).Value = rst!AcctName
objSht.Cells(iRow, 5).Value = rst!SumOfAmount
objSht.Cells(iRow, 6).Value = rst!Code
iRow = iRow + 1
rst.MoveNext
Loop

Set objSht = Nothing
Set objWkb = Nothing
Set objXL = Nothing
rst.Close

 

[SkipVought]

Hi,

You might need to change the NumberFormat property...

    With objSht
        With .Cells(iRow, 1)
            .NumberFormat = "@"
            .Value = rst!Sortcode
        End With
        With .Cells(iRow, 2)
            .NumberFormat = "@"
            .Value = rst!AccountNo
        End With
        With .Cells(iRow, 3)
            .NumberFormat = "@"
            .Value = rst!PTYID
        End With
        With .Cells(iRow, 4)
            .NumberFormat = "@"
            .Value = rst!AcctName
        End With
        With .Cells(iRow, 5)
            .NumberFormat = "$#,##0.00"
            .Value = rst!SumOfAmount
        End With
        With .Cells(iRow, 6)
            .NumberFormat = "@"
            .Value = rst!Code
        End With
    End With

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[PHV]

Why not simply something like this ?

objSht.Range("A2").CopyFromRecordset rst

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dmarsh16946]

Hi Guys

Tried both thanks and like the abbreviated version. Both still give the annoying green indicator in top left of Excel cells though.

 

[PHV]

If you don't need the leading zeroes, replace this:
Set rst = CurrentDb.OpenRecordset("SELECT * FROM tblBACSData")
with this:
Set rst = CurrentDb.OpenRecordset("SELECT Val(Sortcode) AS Sort_Code,Val(AccountNo) AS Account_No,PTYId,AcctName,SumOfAmount,Code FROM tblBACSData")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886