How to include a header when exporting a datasheet from MS Access to Excel

Does anyone know how I can export a datasheet to include a header (I need to add a key and a logo to the header)

I can add a header in design view but it doesn't show when I go to datasheet view.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

No.  Use TransferSpreadsheet to export the data and the column headings and then use OLE automation to open the spreadsheet and add the header and logo that way.


Create an Excel template with all the headers and logos you need.  Then in Access copy the template as a new workbook and then transfer the data TransferSpreadsheet using a named range.

Here's code I use to do the second option.
Private Sub cmdRun_Click()
''' requires reference to Microsoft Scripting library

    Dim FSO As New FileSystemObject
    Dim db As DAO.Database
    Dim qd As DAO.QueryDef
    Dim rs As DAO.Recordset
    Dim sBEPath As String
    Dim sBEFile As String
    Dim SourceFile As String
    Dim TargetFile As String
    Dim appExcel As Excel.Application      'Excel Object
    Dim wbkNew As Excel.Workbook        'Workbook Object
    Dim wksNew As Excel.Worksheet       'Sheet Object
    Dim wbkTemplate As Excel.Workbook   'Workbook Object for Template

On Error GoTo ErrProc
    Set db = CurrentDb()
    Set qd = db.QueryDefs!qNamedRanges_Dashboard
''''    sBEPath = Mid(Me.txtFile, 1, InStrRev(Me.txtFile, "\"))
    sBEPath = Me.txtPath
    SourceFile = Mid(Me.txtFile, InStrRev(Me.txtFile, "\") + 1)
    If Left(SourceFile, 9) = "Template_" Then       'remove "template_" from beginning if it is there
        sBEFile = Mid(SourceFile, 10)
        sBEFile = SourceFile
    End If
    sBEFile = Left(sBEFile, InStrRev(sBEFile, ".") - 1)       'remove file extension
    TargetFile = sBEPath & sBEFile & "ProdID_" & Me.cboProductionID & "_" & Format(Now(), "yyyymmdd_hhnnss") & ".xlsx"       'format new file name
    Set FSO = CreateObject("Scripting.FileSystemObject")
    FSO.CopyFile SourceFile, TargetFile, True
    Set rs = qd.OpenRecordset(dbOpenDynaset, dbSeeChanges)
    Do Until rs.EOF
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, rs!QueryName, TargetFile, 0, rs!NamedRange
    Set appExcel = New Excel.Application
    Set wbkNew = appExcel.Workbooks.Open(TargetFile)
    Set wksNew = appExcel.Worksheets("QueuePerformance")
    With appExcel
        .Range("C3").Value = Me.cboProductionID
        .Range("B2").Value = Date
        .Range("C2").Value = Format(Now(), "hh:mm")
    End With
    appExcel.Visible = True
    Me.txtOutputFileName = TargetFile
    MsgBox "Export Complete", vbOKOnly
    Exit Sub
    Select Case Err.Number
        Case 53
            MsgBox Err.Number & "--" & Err.Description & "--"
        Case 94
            MsgBox "Please select the template file.", vbOKOnly
            Resume ExitProc
        Case Else
            MsgBox Err.Number & "--" & Err.Description, vbOKOnly
            Resume ExitProc
    End Select
    Resume ExitProc
End Sub

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
LillyCAuthor Commented:
Great, thank you : )
You're welcome.  Please don't forget to close the question if this is your answer.  The code example exports several sets of data hence the transfer loop.
LillyCAuthor Commented:
Thanks Pat
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.