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

[raziel3]

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.

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.

 

[theDBguy]

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.

 

[Pat Hartman]

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.

 

[Uncle Gizmo]

I found this code from btab developments useful If you want more control of the export:-

Export A Table Or Query To Excel | BTAB Development

Copy this code into a standard (not form or report) module. Name the module something other than this function name and make sure to set a reference to DAO if you don't already have one. Public Function SendTQ2Excel(strTQName As String, Optional strSheetName As String) ' strTQName is the name of...

btabdevelopment.com

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

Make Excel Sheets From Access Table - Nifty Access

Excel Sheets From Access Table If you've ever faced the challenge of exporting data from Microsoft Access into Excel, you’re not alone! One of the most common requests is to organize data by categories, like exporting car makes into separate Excel sheets or even separate Excel files. I’ve...

www.niftyaccess.com

 

[Pat Hartman]

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.

 

[raziel3]

Ok, I'm back to tackle this problem. I have the recordset

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

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.

 

[theDBguy]

Are you able to share a sample db with test data?

 

[MajP]

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.

 

[raziel3]

@MajP it works 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.

 

[raziel3]

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.

 

[theDBguy]

2. Convert it to a string and remove fields not needed

I don't understand what fields are not needed. How is this determined?

 

[raziel3]

I don't understand what fields are not needed.

See Post#6

How is this determined?

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.

 

[MajP]

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.

 

[MajP]

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.

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.

 

[MajP]

This works as follows.
I build the select statement based on which fields are selected

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

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.

 

[theDBguy]

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.

 

[raziel3]

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

 

[theDBguy]

@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:

 

[raziel3]

Unwanted Fields:
PO, DUEDATE, CREDITNOTE, INVTTLEXVAT,PAYID, AMTPD, EXPID, MTHEND, ENTRYDATE, FILE, DAYSDUE, SUPID, SUPPLIER

 

[Uncle Gizmo]

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

Show-Hide-Export to Excel - Nifty Access

Show/Hide & Export to Excel What started out as an answer to a question on Expert Exchange, took on a life of its own and became a pop-up form which allows you to select the columns you want to display in a datasheet. Now, this is not at all a groundbreaking technology! It's actually already

www.niftyaccess.com

To get a free copy use code:-

VOE5P1I