copying table to excel with fieldnames 2

[legos]

The following code takes the data contained in a table and puts into an excel spreadsheet. The only problem is that none of my field names are included in the first row of the excel file. the first row remains blank.
Dim mydate
mydate = Date
Dim objXL As Excel.Application
Dim objWkb As Excel.Workbook
Dim objSht As Excel.Worksheet
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim intLastCol As Integer
Const conMAX_ROWS = 20000
Const conSHT_NAME = "Feature"
Const conWKB_NAME = "F:\logorders\order.xls"
Set db = CurrentDb
Set objXL = New Excel.Application
Set rs = db.OpenRecordset("Select * FROM cambeorders WHERE Now()-orderdate<=7 And priority = false", dbOpenSnapshot)
'Set rs = db.OpenRecordset("Select * from cambeorders WHERE orderdate < #" & Date & "# AND orderdate > #" & DateAdd(ww, -2, Date) & "#", dbOpenSnapshot)
With objXL
.Visible = True
Set objWkb = .Workbooks.Open(conWKB_NAME)
On Error Resume Next
Set objSht = objWkb.Worksheets(conSHT_NAME)
If Not Err.Number = 0 Then
Set objSht = objWkb.Worksheets.add
objSht.Name = conSHT_NAME
End If
Err.clear
On Error GoTo 0
intLastCol = objSht.UsedRange.Columns.count
With objSht
.Range(.Cells(1, 1), .Cells(conMAX_ROWS, _
intLastCol)).ClearContents
.Range(.Cells(1, 1), _
.Cells(1, rs.Fields.count)).Font.Bold = True
.Range("A2").CopyFromRecordset rs
End With
End With
objWkb.Close True
Set objSht = Nothing
Set objWkb = Nothing
objXL.Quit ' NOTE - changed sequence
Set objXL = Nothing
Set rs = Nothing
Set db = Nothing

Durible Outer Casing to Prevent Fall-Apart

 

[PHV]

Take a look at the Name property of the Recordset.Field object.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[RoyVidar]

No, I think the copyfromrecordset method of the range object only contains the records. Prior to the copyfromrecordset, you could try something like this:

[tt]dim lngCount as long
for lngCount=0 to rs.fields.count-1
.cells(1,lngCount+1).value=rs.fields(lngCount).name
next lngCount[/tt]

Roy-Vidar

 

[legos]

that worked great thanks RV

Durible Outer Casing to Prevent Fall-Apart

 

[PHV]

No

You said No to what ?

 

[RoyVidar]

Perhaps not the best of wording, but "No, I think the copyfromrecordset method of the range object only contains the records" - as a confirmation the copyfromrecordset method does not contain/expose the field names which needs to be retrieved thru the fields collection of the recordset.

If you're asking if it's some reference to your reply, then no to that too, I did not see it before hitting submit;-)

Roy-Vidar