INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Exporting a table to excel and including the titles

Exporting a table to excel and including the titles

(OP)
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


CODE -->

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 

RE: Exporting a table to excel and including the titles

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...

CODE

'
   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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Exporting a table to excel and including the titles

(OP)
Thanks Skip

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close