Results 1 to 3 of 3
  1. #1
    wizzz_wizzz is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2013
    Posts
    14

    Export Filtered Form to Excel

    I have a table (tblMain with 20 fields), which I created a query (qrySPM) to choose 5 fields from tblMain), from which I created a form (frmSPM-in split form view) for the purpose of data entry into tblMain. Finally, I would like to filter the various fields in frmSPM and export the filtered data to excel at the click of a button.



    I have struggled for a week due to being new to vba. Below is the code is copied (from Rey Obrero- link omitted as I am not sure if I can post it here) and did some modification as below. I am still very much confused about the "querydef" part. Appreciate any help to point me to the right direction. TIA.


    Code:
    Private Sub CommandSPM_Click()
    
    
    
    
    Dim sWhere As String, qd As DAO.QueryDef, nSql As String
    
    
    
    
     If Me.Filter = "" Then
         sWhere = ""
         Else
         sWhere = Me.Filter
     End If
    
    
     Set qd = CurrentDb.QueryDefs("qrySPM")
     qd.SQL = "select * from tblMain"
     nSql = qd.SQL & " Where " & sWhere
     If Len(sWhere) > 0 Then
     qd.SQL = nSql
     End If
    
    
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qrySPM3", "Z:\aSOPO\SPM.xlsx", True
    
    
    
    
    End Sub

  2. #2
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 7 32bit Access 2013
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qrySPM3", "Z:\aSOPO\SPM.xlsx", True
    Should that 3​ be there.
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  3. #3
    wizzz_wizzz is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2013
    Posts
    14
    Hi Bob,

    Thank you very much for pointing out the mistake. I have made the corrections but when I run the code, I am prompted to "Enter Parameter Value", qrySPM3.Field1.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 10-26-2016, 06:38 AM
  2. Can't export data in a filtered subform only
    By mayestom in forum Macros
    Replies: 30
    Last Post: 08-11-2014, 09:23 AM
  3. Export Filtered Query to Excel Using Combo Box on Form
    By besuchanko in forum Import/Export Data
    Replies: 0
    Last Post: 02-07-2013, 10:10 PM
  4. Export Filtered ListBox output to a Excel Sheet
    By gokul1242 in forum Programming
    Replies: 4
    Last Post: 10-11-2012, 02:19 PM
  5. Export Filtered Results to Excel Tabs
    By opod in forum Programming
    Replies: 1
    Last Post: 08-03-2011, 01:33 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums