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
I found the sollution here:
Exporting selected records to excel - hiding certain columns
DoCmd.Echo False
Me.Field1.Visible = False
Me.Field2.Visible = False
Me.Field3.Visible = False
DoCmd.RunCommand acCmdOutputToExcel
Me.Field1.Visible = True
Me.Field2.Visible = True
Me.Field3.Visible = True
DoCmd.Echo True
End Sub
it's simple and it works for me