Exporting Filtered Form to Excel (1 Viewer)

christine407

New member
Local time
Yesterday, 19:45
Joined
Jan 18, 2021
Messages
13
Another duplicate thread, I apologize. I've read through as many examples as I could find. Even though the task seems very similar for most users, the approach seems inconsistent.

So this command works if I want to export the entire table!
Code:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "COMTAWells", "H:\All_COM_TA_Wells.xlsx", True

The table that I'm filtering my form (gosh form vs table terminology is confusing) is COMTAWells. I probably should have titled it qryCOMTAWells, but next version perhaps. First Question, is it possible to change the title of H:\All_COM_TA_Wells.xlsx to include a date? I expect to make monthly backups, perhaps like YYYYMMDD?

Secondly, I'm having trouble implementing the code for exporting the filtered form (or filtered COMTAWells table). I've seen so many examples and I think I'm not quite following the QueryDefs and DAO characteristics to feel comfortable adding them into the script. Here is my current attempt!


Code:
Private Sub btnFiltExcelExport_Click()

'This is the variable that will contain the where condition of the SQL string
Dim strSQL As String

'This is the entire starting table before being filtered (COMTAWells), alternatively, I've seen qrySQL = "Select * from" & Me.RecordSource, but not sure which is a better approach?
qrySQL = Me.RecordSource

'This will contain the entire SQL string (including the where condition)
Dim qryExport As String

If Me.Filter = "" Then
   strSQL = ""
   qryExport = qrySQL
Else
   strSQL = Me.Filter
   qryExport = qrySQL & "WHERE" & Me.Filter
End If

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qryExport", "H:\Filtered_COM_TA_Wells.xlsx", True

End Sub
 

CJ_London

Super Moderator
Staff member
Local time
Today, 03:45
Joined
Feb 19, 2013
Messages
12,926
strongly recommend you step through the code (or use debug.print) so you can see what the sql is you are generating - you are missing a lot of spaces

depends on what your recordsource is but probably better to use

exportSQL="Select * from (" & replace(Me.RecordSource ,";","") & ") WHERE " & me.filter


with regards your querydef, create a simple select query that you can save. Save it with the name qryExport

then all you have to do is assign the sql

querydefs("qryExport").sql=exportSQL

finally, use your transferspreadsheet

for the date

"H:\Filtered_COM_TA_Wells " & format(date,"yyyymmdd") & ".xlsx"
 

MajP

You've got your good things, and you've got mine.
Local time
Yesterday, 22:45
Joined
May 21, 2018
Messages
4,778
In everyone of my database I have a Query Viewer / Exporter. This is a Form with a subform that loads any query selected from a comboox. Then I can sort and filter any query and export it.

Code:
Private Sub cmdExport_Click()
    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
    
    strSql = "Select * from " & Replace(Me.subFrmQuery.Form.RecordSource, ";", "")
    If strFilter <> "" Then
      strSql = strSql & " WHERE " & strFilter
    End If
    If strSort <> "" Then
      strSql = strSql & " ORDER BY " & strSort
    End If
    Set qdfTest = CurrentDb.QueryDefs("ExportQuery")
    qdfTest.SQL = strSql
    DoCmd.OutputTo acOutputQuery, qdfTest.Name, acFormatXLSX, , True
    
    On Error GoTo 0
    Exit Sub

cmdExport_Click_Error:

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

  End Sub
 

Users who are viewing this thread

Top Bottom