Export subform results to Excel

Discussion in 'Access Forms Coding' started by jimster68, Mar 6, 2007.

  1. jimster68

    jimster68 Guest

    I have built a main form that includes controls to filter/search records from
    a subform based on a query. The query pulls data from multiple tables. I
    want to create another command button in the main form to export the filtered
    results from the subform to an excel spreadsheet. I have the following code
    but it pulls all of the data from the query, not the filtered results.

    Private Sub cmdExcel_Click()
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
    "q_Server_Data", "c:\temp\test1.xls"

    End sub

    How can I change this to include only the data from the filter/search in the
    subform?

    Thanks,
    Jim
     
    jimster68, Mar 6, 2007
    #1
    1. Advertisements

  2. jimster68

    jimster68 Guest

    Well I discovered something that works.

    DoCmd.OutputTo acOutputForm, "frm_Server_Data", acFormatXLS, _
    strPath & "\ServerData.xls" ', AutoStart:=-1

    This outputs the filtered results from the subform to the spreadsheet.

    "jimster68" wrote:

    > I have built a main form that includes controls to filter/search records from
    > a subform based on a query. The query pulls data from multiple tables. I
    > want to create another command button in the main form to export the filtered
    > results from the subform to an excel spreadsheet. I have the following code
    > but it pulls all of the data from the query, not the filtered results.
    >
    > Private Sub cmdExcel_Click()
    > DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
    > "q_Server_Data", "c:\temp\test1.xls"
    >
    > End sub
    >
    > How can I change this to include only the data from the filter/search in the
    > subform?
    >
    > Thanks,
    > Jim
     
    jimster68, Mar 9, 2007
    #2
    1. Advertisements

  3. jimster68

    Norbert

    Joined:
    Sep 15, 2011
    Messages:
    8
    Likes Received:
    0
    Location:
    Prague, The Czech Republic
    A2EE.mda is a tool that exports records from subform or subdatasheet to Excel

    Hi,
    I think you will be able to export filtered subform's records to the Excel with A2EE.mda which is an Access add-in specially developed for such tasks.
    You will find more on:
    limbersti.cz/A2EE/
    Let us know it it helped.
    BR
    Norbert
     
    Norbert, Jun 6, 2012
    #3
  4. DoCmd.TransferSpreadsheet method will always export all data of underlying table or query, not only the filtered data.

    When you want to export only filtered data, you need to use Access add-in 'A2EE.mda' that can be found here:
    http://www.limbersti.cz/A2EE/

    In it's settings you can for example setup that all subdatasheet's records related to current record of master table / query / form will be exported to Excel.

    Which is exactly what you look for.

    BR

    Norbert


    > On Tuesday, March 06, 2007 11:59 AM jimster6 wrote:


    > I have built a main form that includes controls to filter/search records from
    > a subform based on a query. The query pulls data from multiple tables. I
    > want to create another command button in the main form to export the filtered
    > results from the subform to an excel spreadsheet. I have the following code
    > but it pulls all of the data from the query, not the filtered results.
    >
    > Private Sub cmdExcel_Click()
    > DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
    > "q_Server_Data", "c:\temp\test1.xls"
    >
    > End sub
    >
    > How can I change this to include only the data from the filter/search in the
    > subform?
    >
    > Thanks,
    > Jim



    >> On Friday, March 09, 2007 3:06 PM jimster6 wrote:


    >> Well I discovered something that works.
    >>
    >> DoCmd.OutputTo acOutputForm, "frm_Server_Data", acFormatXLS, _
    >> strPath & "\ServerData.xls" ', AutoStart:=-1
    >>
    >> This outputs the filtered results from the subform to the spreadsheet.
    >>
    >> "jimster68" wrote:
     
    Norbert Limbersky, Jun 25, 2012
    #4
    1. Advertisements

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.
Similar Threads
  1. Charles D Clayton Jr

    Export subform to Excel

    Charles D Clayton Jr, Feb 25, 2004, in forum: Access Forms Coding
    Replies:
    1
    Views:
    119
    PC Datasheet
    Feb 25, 2004
  2. G

    Export Subform results to EXCEL

    G, Feb 27, 2005, in forum: Access Forms Coding
    Replies:
    4
    Views:
    549
    Norbert
    Jun 16, 2012
  3. smsscout
    Replies:
    2
    Views:
    128
    smsscout
    Sep 12, 2005
  4. rsbutterfly219 via AccessMonster.com

    access command in form to export query results to excel

    rsbutterfly219 via AccessMonster.com, Jun 5, 2006, in forum: Access Forms Coding
    Replies:
    2
    Views:
    204
    rsbutterfly219 via AccessMonster.com
    Jun 5, 2006
  5. Linda

    Export Search Results to Excel

    Linda, Jul 28, 2006, in forum: Access Forms Coding
    Replies:
    1
    Views:
    86
    Yanick
    Jul 28, 2006
  6. harrykp via AccessMonster.com

    export the search results in the subform to an excel spreadsheet.

    harrykp via AccessMonster.com, Oct 25, 2006, in forum: Access Forms Coding
    Replies:
    0
    Views:
    113
    harrykp via AccessMonster.com
    Oct 25, 2006
  7. Sarah

    Link Subform to subform within another subform

    Sarah, Feb 29, 2008, in forum: Access Forms Coding
    Replies:
    2
    Views:
    167
    Sarah
    Mar 1, 2008
  8. tmdrake

    Suggestions Please - Subform Results Export to Excel

    tmdrake, Jun 25, 2008, in forum: Access Forms Coding
    Replies:
    18
    Views:
    135
    Jeanette Cunningham
    Aug 13, 2008
Loading...