Exporting Filtered Datasheet Form

[LarryB]

Good afternoon all,

I have built a DB with a series of forms and I am looking to output data to excel.

I have a main form containing some buttons and a subform which is a datasheet. I can filter the datasheet using the datasheet filtering properties.

However when exporting that datasheet, it exports the contents of the subform, which is fine when no filters are applied, but when I apply filters it does not export what I see, it exports all of the data

 DoCmd.OutputTo acOutputForm, [Forms]![MainForm]![SUBFormControlName].Form.Name, acFormatXLS, "C:\temp\exportSub.xls"

How can I export just what I see, or do I have to build a query based on my filtering and then export the query?

Further to this, if I have records displaying in my datasheet, and highlight just one row, can I export that one row? I could probably utilise the selected row ID to pull the data.

Cheers

Larry

 

[Ranman256]

qsQry1 uses the filter on the form...
then export

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "qsQry1", "c:\folder\file.xlsx", True, "sheet1"

 

[arnelgp]

the problem with query being your source is that you never know which column you are filtering, maybe 1st column, 1&3rd column, etc.

 

[Ranman256]

If you are using a query , youd have to export to excel using the menu, export, excel, ...

if you are using a form, you could copy/paste the grid to excel.

 

[LarryB]

Thanks for the replies!

Ranman, copy and past works, but it is not ideal as the formatting is all over the shop where as a direct export has them neat.

I'd like to refer back to your qsqry1. I never heard of that before.

Is qsqry1 a control on the form that is built as I filter the datasheet? How do I refer to it from the main form.

I have tried

 [Forms]![Main Form ]![Sub Form Control Name].Form![qsQry1]

#Note - I just spotted the Filter property of the subform populating with my last filter on the datasheet subform. I'm going to try and reference that to pull in my report.

The Filter property contained my query and fields with criteria. I'll let you know.

 

[LarryB]

#Note - I just spotted the Filter property of the subform populating with my last filter on the datasheet subform. I'm going to try and reference that to pull in my report.

The Filter property contained my query and fields with criteria. I'll let you know.

Well that can go out the window with my brain!

 

[LarryB]

Ripped and modified from another site but stick this behind a button on your main form. Ensure you have Microsoft Excel (x) Object Library added in the VBA (Tools) References

  Dim rs As Recordset
 Dim intCount As Integer
 ' Create the Excel object
 Set ExcelSheet = New Excel.Application
  ' Fetch the recordset
 
 Set rs = [Forms]![MainForm]![SubFormConrolName].Form.RecordsetClone
 
 With ExcelSheet
 ' Add a workbook and turn of Excel updates
 .ScreenUpdating = True
 .Visible = False
 .Workbooks.Add
 .DisplayAlerts = True
  ' Add the column headers
 For intCount = 0 To rs.Fields.Count - 1
 .Cells(1, intCount + 1).Value = rs.Fields(intCount).Name
 Next intCount
 
 ' Dump the recordset to Excel
 .Range("A2").CopyFromRecordset rs
 .Visible = True
  End With
 End Sub

 

[arnelgp]

i will post it again, just supply your main form name and your subform name on this sub, if there is no subform just passed the main form name:

Private Sub yourButtonToImportName(ByVal strMainFormName As String, Optional ByVal strSubformName As String = "")

    Dim db As DAO.Database
    Dim qrydef As DAO.QueryDef
    
    Dim strSQL As String
    Dim bolWithFilterOn As Boolean
    Dim strTempQryDef As String
    Dim strRecordSource As String
    
    strTempQryDef = "__zqry"
    
    If strSubformName = "" Then
        bolWithFilterOn = Forms(strMainFormName).Form.FilterOn
        strRecordSource = Forms(strMainFormName).Form.RecordSource
    Else
        bolWithFilterOn = Forms(strMainFormName)(strSubformName).Form.FilterOn
        strRecordSource = Forms(strMainFormName)(strSubformName).Form.RecordSource
    End If
    If InStr(strRecordSource, "SELECT ") <> 0 Then
        strSQL = strRecordSource
    Else
        strSQL = "SELECT * FROM [" & strRecordSource & "]"
    End If
    
    ' just in case our sql string ends with ";"
    strSQL = Replace(strSQL, ";", "")
    
    If bolWithFilterOn Then
        strSQL = strSQL & _
            IIf(InStr(strSQL, "WHERE ") <> 0, " And ", " Where ") & _
            IIf(strSubformName = "", Forms(strMainFormName).Form.Filter, Forms(strMainFormName)(strSubformName).Form.Filter)
    End If
    
    Set db = CurrentDb
    
    'create temporary query
    Set qrydef = db.CreateQueryDef(strTempQryDef, strSQL)
    db.QueryDefs.Append qrydef
    Set qrydef = Nothing
    
    DoCmd.TransferSpreadsheet TransferType:=acExport, _
    SpreadsheetType:=acSpreadsheetTypeExcel12Xml, _
    TableName:=strTempQryDef, _
    FileName:=Replace(CurrentProject.Path & "\", "\\", "\") & strMainFormName & _
        IIf(strSubformName = "", "", "_" & strSubformName) & ".xlsx"
    
    ' Delete the temporary query
    db.QueryDefs.Delete strTempQryDef
    
    Set db = Nothing

End Sub

 

[Gint3232]

i will post it again, just supply your main form name and your subform name on this sub, if there is no subform just passed the main form name:

Private Sub yourButtonToImportName(ByVal strMainFormName As String, Optional ByVal strSubformName As String = "")

    Dim db As DAO.Database
    Dim qrydef As DAO.QueryDef
    
    Dim strSQL As String
    Dim bolWithFilterOn As Boolean
    Dim strTempQryDef As String
    Dim strRecordSource As String
    
    strTempQryDef = "__zqry"
    
    If strSubformName = "" Then
        bolWithFilterOn = Forms(strMainFormName).Form.FilterOn
        strRecordSource = Forms(strMainFormName).Form.RecordSource
    Else
        bolWithFilterOn = Forms(strMainFormName)(strSubformName).Form.FilterOn
        strRecordSource = Forms(strMainFormName)(strSubformName).Form.RecordSource
    End If
    If InStr(strRecordSource, "SELECT ") <> 0 Then
        strSQL = strRecordSource
    Else
        strSQL = "SELECT * FROM [" & strRecordSource & "]"
    End If
    
    ' just in case our sql string ends with ";"
    strSQL = Replace(strSQL, ";", "")
    
    If bolWithFilterOn Then
        strSQL = strSQL & _
            IIf(InStr(strSQL, "WHERE ") <> 0, " And ", " Where ") & _
            IIf(strSubformName = "", Forms(strMainFormName).Form.Filter, Forms(strMainFormName)(strSubformName).Form.Filter)
    End If
    
    Set db = CurrentDb
    
    'create temporary query
    Set qrydef = db.CreateQueryDef(strTempQryDef, strSQL)
    db.QueryDefs.Append qrydef
    Set qrydef = Nothing
    
    DoCmd.TransferSpreadsheet TransferType:=acExport, _
    SpreadsheetType:=acSpreadsheetTypeExcel12Xml, _
    TableName:=strTempQryDef, _
    FileName:=Replace(CurrentProject.Path & "\", "\\", "\") & strMainFormName & _
        IIf(strSubformName = "", "", "_" & strSubformName) & ".xlsx"
    
    ' Delete the temporary query
    db.QueryDefs.Delete strTempQryDef
    
    Set db = Nothing

End Sub

Can't get any of this to work ,do you have a working.mdb example

 

[arnelgp]

just remove this line:

db.QueryDefs.Append qrydef