Solved How to Export Current Recordset and remove certain fields to Excel (Field/Column Filtering) (1 Viewer)

raziel3

Registered User.
Joined
Oct 5, 2017
Messages
289
With a form's recordset, How to export the current viewed recordset. I am using this code but it includes all the form's unbounded fields in the export.

Code:
Private Sub bntEXPORT2EXCEL_Click()

Dim Path As String
Dim FileName As String


FileName = InputBox("Enter File Name to Save As. (Will be placed on the Desktop)", "Create File")

Path = Environ("USERPROFILE") & "\Desktop\"

    DoCmd.OutputTo acOutputForm, "Search", acFormatXLS, _
                Path & FileName & ".xls"

End Sub

Also the underlying recordset has about 10 fields, I only want 4 of them exported.
 
Perhaps one approach is to create a parameter query with only the columns you want and a criteria referring to your form's record. Then, you will be able to use the TransferSpreadsheet method against that query.
 
Using a query also gives you the ability to sort the export and to format fields like dates to eliminate time if you don't need it.
 
I found this code from btab developments useful If you want more control of the export:-


I made a demo utilizing the code. See details on my website here:-

 
You can use one line of code - TransferSpreadsheet which is probably all you need most of the time. But, if you need to do any formatting, then you need to use the long method.
 
Ok, I'm back to tackle this problem. I have the recordset

Code:
SELECT uALLPUR.PO, uALLPUR.INVDATE, uALLPUR.DUEDATE, uALLPUR.VENDISP, uALLPUR.INVOICE, uALLPUR.SUBTOTAL, uALLPUR.VAT, uALLPUR.CREDITNOTE, uALLPUR.INVTTLEXVAT, uALLPUR.INVTTL, uALLPUR.PAYID, uALLPUR.AMTPD, uALLPUR.EXPID, uALLPUR.MTHEND, uALLPUR.ENTRYDATE, EFILE([SUPPLIER],[VENDISP],[INVOICE]) AS FILE, [INVTTL]-Nz([AMTPD],0) AS AMTDUE, IIf([AMTDUE]>0,DateDiff("d",[DUEDATE],Date()),"") AS DAYSDUE, SUPPLIERS.SUPID, SUPPLIERS.SUPPLIER
FROM uALLPUR LEFT JOIN SUPPLIERS ON uALLPUR.SUPID = SUPPLIERS.SUPID
ORDER BY uALLPUR.INVDATE DESC;

The form does some filtering so building the recordset on uALLPUR may not work because I need the filtered recordset with the fields INVDATE, VENDISP, INVOICE, SUBTOTAL, VAT, INVTTL, AMTDUE exported to excel. Is it possible to rebuild the current recordset something along the lines of

Code:
ExcelExportSql = "SELECT INVDATE, VENDISP, INVOICE, SUBTOTAL, VAT, INVTTL, AMTDUE From Me.RecordsetClone"

I tried this but it does not work.

The code provided here https://www.devhut.net/ms-access-vba-export-recordset-to-excel/ is really good but for some reason if the INVIOCE field is blank it exports all the records up to that field and it does not have field filtering.
 
Are you able to share a sample db with test data?
 
All you really need to do is apply the same filter from your form to a query. You can build a generic query and then export it. You will modify the sql of the querydef before export. Call it qryExport.

dim qdf as queryDef
set qdf = currentdb.querydefs("qryExport")
qdf.sql = ""SELECT INVDATE, VENDISP, INVOICE, SUBTOTAL, VAT, INVTTL, AMTDUE from some Table WHERE " & forms("YourFormName").filter & " ORDER BY somefield"
Now you can use the transferspreadsheet to export the modified querydef with correct filter.
 
@MajP it works (y) but is there a way to do this without having to create the query? Because when I filter by VENDISP the parameter is applied to [Search].[VENDISP] which does not exist in qryExport.

[Search] is the Form's name.
 
Last edited:
I'm seeing the problem. Passing the Form's filter means that all the fields being filtered needs to exist in qryExport which defeats the purpose. So back to the question in Post#6. Here's what I'm thinking,

1. Get the form's filtered recordset
2. Convert it to a string and remove fields not needed
3. Pass that thru a new recordset method
4. Export

Step 2 is where I need help.
 
I'm seeing the problem. Passing the Form's filter means that all the fields being filtered needs to exist in qryExport which defeats the purpose. So back to the question in Post#6. Here's what I'm thinking,
Like any query those fields need to exist in the table or joined tables, but they do not have to be selected.
 
In this form I demonstrate the ability to select any fields from a form in data sheet view. Sort those fields, filter those fields, and export the results.
select.png

I am loading queries, but simply build your recordsource of you form based on a saved query. If you do that this works on any form.
 

Attachments

This works as follows.
I build the select statement based on which fields are selected
Code:
Public Function GetSelect() As String
  Dim strSql As String
  Dim strFlds As String
  Dim i As Integer
  Dim flds As Collection
  Set flds = FTL.ToItems
  For i = 1 To flds.Count
    If strFlds = "" Then
      strFlds = flds(i)
    Else
      strFlds = strFlds & ", " & flds(i)
   End If
  Next i
  If strFlds = "" Then strFlds = "*"
  strSql = "Select " & strFlds & " from " & Me.cmboQuery

  GetSelect = strSql
End Function

My fields are selected in the listbox.

Then simply pull the sort order and filter from the form and build the sql string

Code:
Public Sub Export(Optional ExportType As Long = 1)
   On Error GoTo cmdExport_Click_Error
    Dim strSql As String
    Dim strFilter As String
    Dim strSort As String
    Dim qdfTest As QueryDef
    Dim frm As Access.Form
    Set frm = Me.subFrmQuery.Form
    strFilter = frm.Filter
    strSort = frm.OrderBy
   
    strFilter = Replace(strFilter, "[exportQuery].", " ")
    strSort = Replace(strSort, "[exportQuery].", " ")
    strSql = GetSelect
    If strFilter <> "" Then
      strSql = strSql & " WHERE " & strFilter
    End If
    If strSort <> "" Then
      strSql = strSql & " ORDER BY " & strSort
    End If
    Set qdfTest = CurrentDb.QueryDefs("ExportQuery")
   ' MsgBox strSql
    qdfTest.SQL = strSql
    Select Case ExportType
      Case 1
        DoCmd.OutputTo acOutputQuery, qdfTest.Name, acFormatXLSX, , True
      Case 2
        DoCmd.OutputTo acOutputQuery, qdfTest.Name, acFormatPDF, , True
    End Select
    On Error GoTo 0
    Exit Sub

cmdExport_Click_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cmdExport_Click, line " & Erl & "."
End Sub

Also that from to list box control is completely reusable and requires a line of code to use if you want that as your selector.
 
Last edited:
See Post#6


For now I will hard code it but eventually on the form I would set up some check boxes or something for the user to select them.
Sorry, I read post #6 before, and I just read it again. However, I still don't get how fields get excluded by the user. If you're saying they "filter" the form, I take that as removing records and not fields. If you were saying exactly what you just posted above, by hard coding the fields, then that still doesn't explain how the user does it. Not saying it's not possible as @MajP just demonstrated how it can be done.

Again, if you're able to share a sample db, we might be able to provide a better answer.
 
@MajP method is exactly what I'm looking to do, removing the unwanted fields then export. I'm still trying to decipher it to get it to work with my form (my form is set up in continuous view).

I've attached my db, click on 'Reset' to see all the records.
 

Attachments

@MajP method is exactly what I'm looking to do, removing the unwanted fields then export. I'm still trying to decipher it to get it to work with my form (my form is set up in continuous view).

I've attached my db, click on 'Reset' to see all the records.
Hi. Thanks for posting a sample db. Can you tell us which fields are unwanted for the export?

Right now, your form includes the following fields:

1700847222471.png
 
Unwanted Fields:
PO, DUEDATE, CREDITNOTE, INVTTLEXVAT,PAYID, AMTPD, EXPID, MTHEND, ENTRYDATE, FILE, DAYSDUE, SUPID, SUPPLIER
 
I've just realised, I created a pop-up form that might be right up your street.

You can hide/unhide columns and export to Excel:-


More details on my website here:-


To get a free copy use code:-

VOE5P1I
 
Last edited:

Users who are viewing this thread

Back
Top Bottom