Export to Excel (1 Viewer)

[mike60smart]

Hi Everyone

I have a Continuous Form with an Unbound Textbox to Filter for specific records.

How can I then export just the filtered list to Excel?

If I just use the following Code it exports all records.

DoCmd.OutputTo acOutputForm, "frmActiveClaims", acFormatXLSX, True

Any help appreciated

 

[theDBguy]

One option is to automate Excel and use the CopyFromRecordset method.

 

[Pat Hartman]

Output a query that uses the criteria that the form is using.

 

[MajP]

This works for me well. There may be recordsources where this does not work, but I am able to sort and filter any way I want and export the records
You can add more format types. It is a little strange becuase the Output format is not a traditional constant it is a class called (Access.Constants) and I could not declare my arguments as access.constants. Therefore I built my own enum with the select case.

Public Enum acFormatType
 acExcel = 1
 acPDF = 2
 acText = 3
 'can add more
End Enum

Public Sub Export(Frm As Access.Form, Optional OutputFormatType As acFormatType = 1)
    'pass in one of the acFormat constants

    
   ' On Error GoTo cmdExport_Click_Error
    Dim strSql As String
    Dim strFilter As String
    Dim strSort As String
    Dim qdfExport As QueryDef
    
    strFilter = Frm.Filter
    strSort = Frm.OrderBy
    
     strSql = "select * from (" & Frm.RecordSource & ")"
    If strFilter <> "" Then
      strSql = strSql & " WHERE " & strFilter
    End If
    If strSort <> "" Then
      strSql = strSql & " ORDER BY " & strSort
    End If
    Set qdfExport = CurrentDb.QueryDefs("ExportQuery")
  '  MsgBox strSql
    qdfExport.SQL = strSql
    Select Case OutputFormatType
     Case acExcel
      DoCmd.OutputTo acOutputQuery, qdfExport.name, acFormatXLSX, , True
     Case acPDF
       DoCmd.OutputTo acOutputQuery, qdfExport.name, acFormatPDF, , True
     Case acText
        DoCmd.OutputTo acOutputQuery, qdfExport.name, acFormatTXT, , True
    End Select
    
  
    On Error GoTo 0
    Exit Sub

cmdExport_Click_Error:

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

 

[MajP]

Here is another approach.

Solved - TransferText

I have a popup window that takes a recordset from the form and does optional things with it. The following gives me a Data Type conversion error Is it ok to pull out specific fields from an already existing recordset to export? Or is there a better way? Dim strSql As String Dim...

www.access-programmers.co.uk

 

[mike60smart]

Output a query that uses the criteria that the form is using.

Hi Pat
Thanks, I went with your suggestion and created a query which shows the filtered data.
It was then just a case of an Export of the filtered records.
Many thanks.

 

[Pat Hartman]

You're welcome.