Excel Output Format Problem

[TheScullster]

Hi all

This is a sticky one for a guy with such a small brain, but here goes......

We have a drawing control database originally created in Access2000. It is
split and now has tables on Win2003 Server with front ends Office 2003 on
WinXP.
The problem is that within this database, there is a facility to create an
Excel spreadsheet as a report.
When running this report, the first column of drawing numbers are being
translated as dates in the spreadsheet, rather than being left in number
form. A typical example of a drawing number is 01-2510. This is shown in
the spreadsheet as Jan-10.

The code which extracts this data is given below:

------------------------------------------------------

Case 3
Dim objxl As Excel.Application
Dim bbjwkb As Workbook
Dim objsht As Worksheet
Dim i As Integer
Set objxl = New Excel.Application
objxl.Visible = True
Set objwkb = objxl.Workbooks.Add
Set objsht = objwkb.Worksheets(1)
With objsht
..Cells(1, 1) = "Please Paste the following into your Document"
.Cells(2, 1) = "DwgNo"
.Cells(2, 2) = "Title"
.Cells(2, 3) = "Revison"
.Cells(2, 4) = "Size"
i = 0
Dim varItem As Variant
For Each varItem In lstResult.ItemsSelected
i = i + 1
.Cells(2 + i, 1) = lstResult.Column(0, varItem)
.Cells(2 + i, 2) = lstResult.Column(1, varItem)
.Cells(2 + i, 3) = lstResult.Column(4, varItem)
.Cells(2 + i, 4) = lstResult.Column(8, varItem)
Next varItem
End With

-----------------------------------------------------------

Is it possible to declare the data as numeric or text within the code as it
is passed to Excel, to avoid this problem?

Thanks in anticipation

Phil

 

[Douglas J. Steele]

Don't know for certain, but try

..Cells(2 + i, 1) = Chr(39) & lstResult.Column(0, varItem)

Chr(39) is a single quote ('), which Excel treats as meaning that the input
should be treated as text

 

[TheScullster]

Don't know for certain, but try

.Cells(2 + i, 1) = Chr(39) & lstResult.Column(0, varItem)

Chr(39) is a single quote ('), which Excel treats as meaning that the
input should be treated as text

--

Thanks Doug that works a treat.

Phil