Exporting a table to excel and including the titles 1

[irethedo]

I have a table that I export to Excel but it doesn't export the names of the fields in
my table at the top of the spreadsheet.

This is most likely one option that I left out of my code but have been searching for
how to do this and have not been able to find the answer...

I have included my code below and would appreciate anyone setting me straight on what I left out...

Thanks

   Set objXL = CreateObject("Excel.Application")
    Set db = CurrentDb
    Set rs1 = db.OpenRecordset("NW_Excel_tbl", dbOpenSnapshot)
    
    If rs1.RecordCount > 0 Then      ' if this is an empty table then don't bother...
         .Visible = True
          Set objWkb = .Workbooks.Open(Out_File)
       
         On Error Resume Next
         Set objSht = objWkb.Worksheets("NW")         'RSP
         objWkb.Worksheets("RSP").Activate
         lngLastRow = objSht.Cells.Find(What:="*", _
           After:=objSht.Range("A1"), _
           LookAt:=2, _
           LookIn:=-4123, _
           SearchOrder:=1, _
           SearchDirection:=2, _
           MatchCase:=False).Row
        End With
        
        lngLastRow = lngLastRow + 1
        With objSht
         .Range("A" & lngLastRow).CopyFromRecordset rs1
          With .Rows(lngLastRow & ":" & lngLastRow + rs1.RecordCount)
           .Font.Bold = True
           .Font.Color = vbBlack
           .Font.Name = "Calibri"
           .Font.Size = 11
          End With
        End With
    End If
    Set rs1 = Nothing
    Set objSht = Nothing

 

[SkipVought]

Hi,

Your code is written such that data already in the sheet will be retained and new data added at lngLastRow.

So you need code that only ONE TIME, do this...

'
   Dim fld As Object, iCol as Integer
   For Each fld in rs.Fields
       objSht.Cells(1, iCol + 1).Value = fld.Name
       iCol = iCol + 1
   Next

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[irethedo]

Thanks Skip