Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2007
    Posts
    14

    Unanswered: Export datasheet subform to MS Excel

    Hi,

    I've got a form, let's say "Customer" and a datasheet subform inserted in "Customer", let's call it "Orders". The records displayed in "Orders" are linked to the current record in "Customer". I would like to do automatically (via a command button and a macro), what i do now manually:
    1- copy the records of the "Orders" datasheet subform for a given "Customer" record
    2- paste them in an MS Excel worksheet.

    This copy-and-paste operation is very easy, but I've haven't find yet any way to automatize it...

    Any code template? Any idea? My wish would be to insert a command button on the "Customer" form to export the "Orders" data to MS Excel.

    Thanks in advance for any kind of help.

    deloffa5

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Create yourself a query on the Orders table that picks up orders where the customer ID = the value on the form.
    E.g. Forms!myForm.txtCustomerID.Value

    Then you can run the DoCmd.TransferSpreadsheet method with the query to export the results!
    George
    Home | Blog

  3. #3
    Join Date
    Sep 2007
    Posts
    14
    Thanks for the advice Georgev, such a solution does work. But as i've a lot of form and subform, i think that in my particular situation it would be quite difficult to implement.

    I'd like to know if there's any kind of automatic copy-and-paste method. A macro that copy the current records displayed in an datasheet subform and paste them in Excel.

    Thanks,

    deloffa5

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Is your subform based on a specific query?
    If so what does the query look like?
    George
    Home | Blog

  5. #5
    Join Date
    Sep 2007
    Posts
    14
    My subform is not based on a specific query. The "problem" is that i've got combo boxes in my subform. And these combo boxes have Row Sources (SQL strings) and Control Sources.

    Ex:
    Combo Box "Provider" in my subform "Orders"
    Control Source: "Provider_ID" (in "Orders" Subform)
    Row Source: Select Provider_ID, Provider_Name from Provider
    Bound Column: 1

    That's because of this type of combo boxes that i'd prefer using copy-and-paste method.

    deloffa5

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I'm not aware of any copy-paste method, I don't think you'll find one that does what you want even if it does!

    However, I do love to be proved wrong in this forum!!

    In my head the only option I can think of is for you to create a bunch of queries based on the form criteria, unless...

    Let me get back to you....
    George
    Home | Blog

  7. #7
    Join Date
    Aug 2012
    Posts
    2

    I had the same problem

    Hi,
    I had the similar problem and managed to solve it with access add-in A2EE.mda.
    It exports data from subdatasheet or subform to Excel.
    Unfortunatelly it is not free :-(
    L.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •