Export Subform results to EXCEL

Discussion in 'Access Forms Coding' started by G, Feb 27, 2005.

  1. G

    G Guest

    I have a form and subform. When the user selects criteria on the form, the
    subform reflects the criteria.
    I need to export the results to excel, but I must be doing it wrong, because
    it exports all the records, not just the selected results.

    I am using the following...

    'export the results to Excel
    DoCmd.OutputTo acOutputForm, "subfrmData", acFormatXLS, "1.xls", True

    Thank you,
    G
    True
    --
    Thank you in advance for your assistance. It is greatly appreciated.
     
    G, Feb 27, 2005
    #1
    1. Advertisements

  2. G

    Dirk Goldgar Guest

    "G" <> wrote in message
    news:
    > I have a form and subform. When the user selects criteria on the
    > form, the subform reflects the criteria.
    > I need to export the results to excel, but I must be doing it wrong,
    > because it exports all the records, not just the selected results.
    >
    > I am using the following...
    >
    > 'export the results to Excel
    > DoCmd.OutputTo acOutputForm, "subfrmData", acFormatXLS, "1.xls",
    > True


    The OutputTo method is going to export the subform's recordsource as it
    is defined in the form's Design View, not reflecting the changes you've
    made to it on the fly. It won't export the recordset currently
    displayed on the open subform.

    There may be a better way, but the only way to do this that I can think
    of offhand is to create a stored query to represent the form's current
    recordsource query, then export that. Maybe you could use logic like
    this:

    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef

    Set db = CurrentDb

    ' Get the existing export query, or create it if
    ' it doesn't exist.
    On Error Resume Next
    Set qdf = db.QueryDefs("qryDataExport")
    If qdf Is Nothing Then
    Set qdf = db.CreateQueryDef("qryDataExport")
    End If
    If qdf Is Nothing Then
    MsgBox Err.Description, vbExclamation, "Error " & Err.Number
    Exit Sub
    End If
    On Error GoTo 0 ' or your error-handler

    ' Here we have got a reference to the export query.
    ' Set its SQL property to the query we want to export.
    qdf.SQL = Me!subfrmDataFiltered.Form.RecordSource

    Set qdf = Nothing ' we're done manipulating it.

    ' Now export the query.
    DoCmd.OutputTo _
    acOutputQuery, "qryDataExport", _
    acFormatXLS, "1.xls", True

    --
    Dirk Goldgar, MS Access MVP
    www.datagnostics.com

    (please reply to the newsgroup)
     
    Dirk Goldgar, Feb 27, 2005
    #2
    1. Advertisements

  3. G

    G Guest

    Dirk,

    That worked. but I have a question on it.
    the line
    Set qdf = Nothing ' we're done manipulating it.
    Why is that set to nothing before exporting? Seems like that would make the
    dataset null (which it didn't).

    G



    "Dirk Goldgar" wrote:

    > "G" <> wrote in message
    > news:
    > > I have a form and subform. When the user selects criteria on the
    > > form, the subform reflects the criteria.
    > > I need to export the results to excel, but I must be doing it wrong,
    > > because it exports all the records, not just the selected results.
    > >
    > > I am using the following...
    > >
    > > 'export the results to Excel
    > > DoCmd.OutputTo acOutputForm, "subfrmData", acFormatXLS, "1.xls",
    > > True

    >
    > The OutputTo method is going to export the subform's recordsource as it
    > is defined in the form's Design View, not reflecting the changes you've
    > made to it on the fly. It won't export the recordset currently
    > displayed on the open subform.
    >
    > There may be a better way, but the only way to do this that I can think
    > of offhand is to create a stored query to represent the form's current
    > recordsource query, then export that. Maybe you could use logic like
    > this:
    >
    > Dim db As DAO.Database
    > Dim qdf As DAO.QueryDef
    >
    > Set db = CurrentDb
    >
    > ' Get the existing export query, or create it if
    > ' it doesn't exist.
    > On Error Resume Next
    > Set qdf = db.QueryDefs("qryDataExport")
    > If qdf Is Nothing Then
    > Set qdf = db.CreateQueryDef("qryDataExport")
    > End If
    > If qdf Is Nothing Then
    > MsgBox Err.Description, vbExclamation, "Error " & Err.Number
    > Exit Sub
    > End If
    > On Error GoTo 0 ' or your error-handler
    >
    > ' Here we have got a reference to the export query.
    > ' Set its SQL property to the query we want to export.
    > qdf.SQL = Me!subfrmDataFiltered.Form.RecordSource
    >
    > Set qdf = Nothing ' we're done manipulating it.
    >
    > ' Now export the query.
    > DoCmd.OutputTo _
    > acOutputQuery, "qryDataExport", _
    > acFormatXLS, "1.xls", True
    >
    > --
    > Dirk Goldgar, MS Access MVP
    > www.datagnostics.com
    >
    > (please reply to the newsgroup)
    >
    >
    >
     
    G, Feb 27, 2005
    #3
  4. G

    Dirk Goldgar Guest

    "G" <> wrote in message
    news:
    > Dirk,
    >
    > That worked. but I have a question on it.
    > the line
    > Set qdf = Nothing ' we're done manipulating it.
    > Why is that set to nothing before exporting? Seems like that would
    > make the dataset null (which it didn't).


    A QueryDef object is a code structure that represents a query that is
    stored in an Access database. When the query is actually created in the
    database -- whether by way of the Acess user interface or by code such
    as the "db.CreateQueryDef" line in the code I posted -- there is
    something physically stored in the database's internal works that
    defines the query. A QueryDef object, as I said, is a code
    representation of that physically stored query, and it includes
    properties and methods that allow us to manipulate the query in code,
    but it is not the same thing as the stored query.

    The code I posted creates a QueryDef object to represent the query named
    "qryDataExport". If the query doesn't already exist it first creates
    it, but either way we end up with the object variable qdf pointing to a
    QueryDef object (code structure) in memory, which is a representation of
    the actual query as it is stored in the database. We then set its SQL
    property, which (because that's the way the QueryDef object is defined
    to work) physically changes the stored query. Then we destroy the
    QueryDef object, but that doesn't destroy the stored query; it only
    destroys this code representation of the query. The query itself is
    still there, stored in the database, until we delete it one way or
    another. I decided not to automatically deleted it after exporting the
    query to Excel, because I figure you're going to use it again, just with
    different SQL.

    Does that clarify the matter?

    --
    Dirk Goldgar, MS Access MVP
    www.datagnostics.com

    (please reply to the newsgroup)
     
    Dirk Goldgar, Feb 27, 2005
    #4
  5. G

    Norbert

    Joined:
    Sep 15, 2011
    Messages:
    8
    Likes Received:
    0
    Location:
    Prague, The Czech Republic
    Hi,
    the only way of exporting data from both form and it's subform, that I am aware to be working, is with use of MS-Access add-in 'A2EE.mda'.
    This add-in has been specially developed for exporting:
    - forms with subforms
    or
    - datasheets with subdatasheets
    from Access to Excel.

    With this add-in you can:
    - either send only those subform's data that are related to current main form's record
    - or send all main form's and subform's data

    Both mentioned options will keep access-like drill-down structure in Excel.

    You may find examples and details on:
    limbersti.cz/A2EE/

    BR
    Norbert
     
    Norbert, Jun 16, 2012
    #5
    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:
    135
    PC Datasheet
    Feb 25, 2004
  2. smsscout
    Replies:
    2
    Views:
    151
    smsscout
    Sep 12, 2005
  3. 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:
    235
    rsbutterfly219 via AccessMonster.com
    Jun 5, 2006
  4. Linda

    Export Search Results to Excel

    Linda, Jul 28, 2006, in forum: Access Forms Coding
    Replies:
    1
    Views:
    104
    Yanick
    Jul 28, 2006
  5. 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:
    137
    harrykp via AccessMonster.com
    Oct 25, 2006
  6. jimster68

    Export subform results to Excel

    jimster68, Mar 6, 2007, in forum: Access Forms Coding
    Replies:
    3
    Views:
    511
    Norbert Limbersky
    Jun 25, 2012
  7. Sarah

    Link Subform to subform within another subform

    Sarah, Feb 29, 2008, in forum: Access Forms Coding
    Replies:
    2
    Views:
    188
    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:
    155
    Jeanette Cunningham
    Aug 13, 2008
Loading...