Export data from continuous form to Excel w/o header fields

Question

I have a continuous form, where the form header contains filter options, and the details section contains the data.

I want to be able to export this to excel. the basic VBA code works

DoCmd.OutputTo

but when I export to Excel, it also includes the form header controls for each row.

Is there any way to set a property that will exclude the form header from being included in the export? Basically, only export the form details section?

I prefer not to use a query

I have 6 unbound txt boxes in the header: - artnr - Artnr supplier - description - article status - supplier name - supplier number and i have a search button, wich holds this code:

Private Sub cmdSearch_Click()

    Dim strWhere As String
    Dim lngLen As Long

'artikel zoeken
    If Not IsNull(Me.txtSearchArtnr) Then
        strWhere = strWhere & "([Material] Like ""*" & Me.txtSearchArtnr & "*"") AND "
    End If

'artnr leverancier zoeken
    If Not IsNull(Me.txtSearchSupplArt) Then
        strWhere = strWhere & "([LiefMat] Like ""*" & Me.txtSearchSupplArt & "*"") AND "
    End If

'trefwoord zoeken
    If Not IsNull(Me.txtSearchKeyword) Then
        strWhere = strWhere & "([Materialkurztext] Like ""*" & Me.txtSearchKeyword & "*"") AND "
    End If

'artikelstatus zoeken
    If Not IsNull(Me.txtSearchStatus) Then
        strWhere = strWhere & "([Status] Like ""*" & Me.txtSearchStatus & "*"") AND "
    End If

'leverancier naam zoeken
    If Not IsNull(Me.txtSearchSupplName) Then
        strWhere = strWhere & "([Name 1] Like ""*" & Me.txtSearchSupplName & "*"") AND "
    End If

'leverancier nummer zoeken
    If Not IsNull(Me.txtSearchSupplNumber) Then
        strWhere = strWhere & "([Lieferant] Like ""*" & Me.txtSearchSupplNumber & "*"") AND "
    End If

'***********************************************************************
'Chop off the trailing " AND ", and use the string as the form's Filter.
'***********************************************************************
'See if the string has more than 5 characters (a trailng " AND ") to remove.

    lngLen = Len(strWhere) - 5
    If lngLen <= 0 Then     'Nah: there was nothing in the string.
        MsgBox "Geen criteria gevonden", vbInformation, "Geen resultaten."

    Else                    'Yep: there is something there, so remove the " AND " at the end.
        strWhere = Left$(strWhere, lngLen)

        'Apply the string as the form's Filter.
        Me.Filter = strWhere
        Me.FilterOn = True
    End If
End Sub

Why wouldn't you put the filter on a query and export the results of the query? That sounds like a much easier way to do it. — Johnny Bones
From what I have read, you can't exclude the header row for an export - only on import. One option is to do the export, then run VBA code to open the Excel file and delete row 1. — Wayne G. Dunn
it's not the header row I want to exclude. in the header of my access form I created unbound text boxes to search in all records. e.g. people can search for a keyword in an article description. when they have found what they need, which could be multiple records, they need to be able to export those results to excel. but when I do this cia the docmd.output to, the unbound text boxes each also become a column in my excel file. — Isabel2410
Can you put the unbound textboxes in the form footer instead of the header? — Mike
Here is something neat that I just found and tested... access-programmers.co.uk/forums/showthread.php?t=149974 — Wayne G. Dunn

SEM_jmc SEM_jmc · Accepted Answer · 2014-04-23T06:54:20

you write, that users can set filters, so you must have programmed something like
Me.RecordSource = "SELECT ... FROM table WHERE --here the criterias--"
Me.Requery

so you could take the SQL-Statement and use it for export, you first have to create a query

    Dim sSQL As String
    Dim qd As QueryDef

    Set qd = CurrentDb.CreateQueryDef("tmp_Query")
    qd.SQL = "Select * from T_Personal"

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "tmp_Query", "yourfile", True

    CurrentDb.QueryDefs.Delete qd.Name

wrap it into a function, so you can fire it anywhere and pass the sql and filename ...

HTH

Export data from continuous form to Excel w/o header fields

2 Answers