Results 1 to 10 of 10
  1. #1
    taimysho0 is offline Competent Performer
    Windows XP Access 2000
    Join Date
    Nov 2011
    Posts
    286

    COPY records from SUBFORM and PASTE to EXCEL?


    hello, i have a subform with rows of data, i will add a button. when the user clicks the button, all the rows on the subform will transfer to a an excel spreadsheet with the headers included.

    can anyone help me achieve this? thanks! pic is attached of my form:

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    taimysho0 is offline Competent Performer
    Windows XP Access 2000
    Join Date
    Nov 2011
    Posts
    286
    wow you are a great help! the only thing is im not sure if im delcaring this in the correct areas.

    i declared this at the top of the form:

    Public Function Send2Excel(frmqryChannelIDSearch As Form, Optional HHFs As String)
    ' frm is the name of the form you want to send to Excel
    ' strSheetName is the name of the sheet you want to name it to



    Dim rst As DAO.Recordset
    Dim ApXL As Object
    Dim xlWBk As Object
    Dim xlWSh As Object
    Dim intCount As Integer
    Const xlCenter As Long = -4108
    Const xlBottom As Long = -4107

    On Error GoTo err_handler

    Set rst = frmqryChannelIDSearch.RecordsetClone

    Set ApXL = CreateObject("Excel.Application")
    Set xlWBk = ApXL.Workbooks.Add
    ApXL.Visible = True
    End Function



    and then on my button click procedure to open up the spreadsheet i have:

    Private Sub Command53_Click()

    Send2Excel Me.frmqryChannelIDSearch, "HHFs"


    End Sub



    however, im getting some kind of type mismatch error? thanks for any help!

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    That article doesn't show the function arguments in parens but I thought they were required:

    Send2Excel(Me.frmqryChannelIDSearch, "HHFs")

    You are referencing a subform, that can be more complicated.

    What line errors? Use step debug.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    taimysho0 is offline Competent Performer
    Windows XP Access 2000
    Join Date
    Nov 2011
    Posts
    286
    hrmm..i threw that line of code in there but an error message pops up saying "expected: = "

    instead of referencing the subform can i reference the actual form the subform derived from? so instead of me.frmchannelidsearch i just put forms!frmchannelidsearch

    however, its still giving me hat "expected: = " error if i use parenthesis

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Okay, never done that but maybe eliminating parens allows the function to run without have to be part of an expression.

    Yes, referencing subform is tricky. First suggestion is to name the container control different from the object it holds. Like ctrChannelSearch. Then try: Me.ctrChannelSearch.Form
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    taimysho0 is offline Competent Performer
    Windows XP Access 2000
    Join Date
    Nov 2011
    Posts
    286
    hrmm i used the .form and it worked without errors, excel opened up. the only thing is its blank and the sheet was not renamed. i didnt understand what you meant by changing the name of the container? how would i do that?


    i just added a .form to everything. here is my code:

    at top:
    Public Function Send2Excel(frmqryChannelIDSearch As Form, Optional HHFs As String)
    ' frm is the name of the form you want to send to Excel
    ' strSheetName is the name of the sheet you want to name it to



    Dim rst As DAO.Recordset
    Dim ApXL As Object
    Dim xlWBk As Object
    Dim xlWSh As Object
    Dim intCount As Integer
    Const xlCenter As Long = -4108
    Const xlBottom As Long = -4107



    Set rst = Me.frmqryChannelIDSearch.Form.RecordsetClone

    Set ApXL = CreateObject("Excel.Application")
    Set xlWBk = ApXL.Workbooks.Add
    ApXL.Visible = True
    End Function







    on button click event:
    Send2Excel Me.frmqryChannelIDSearch.Form, "HHF"




    thanks for the patience and help!

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    A subform (or subreport) is created by putting a subform/subreport container control on a form/report. The container control has a SourceObject property. This can be a table, query, form, report. Like any control the container has a name property. With the main report in design view, click the edge of the subreport, this selects the container, click within the container to select the object it holds.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    taimysho0 is offline Competent Performer
    Windows XP Access 2000
    Join Date
    Nov 2011
    Posts
    286
    i tried the renaming of the subform container and still nothing..excel opens up but no values

  10. #10
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Bob's code is a generic function designed to take any form name and any string as worksheet name. Do not change the function code in any way.

    The error is that you should not put the subform and worksheet names in the function declaration and not in the Set rs line. Put boblarson's function code, as is without edits, into a general code module.

    Then call the function with parameters from an event such as a button click. Review that thread with bob's code again.

    I tested all this and it works.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. Copy and Paste Row (vb)
    By Computer202 in forum Programming
    Replies: 7
    Last Post: 03-28-2014, 01:59 AM
  2. Copy-Paste on Internet
    By BorisGomel in forum Access
    Replies: 5
    Last Post: 11-09-2011, 10:33 AM
  3. Copy-Paste
    By BorisGomel in forum Access
    Replies: 4
    Last Post: 10-25-2011, 07:17 AM
  4. Copy/paste to new record.
    By xbox1513 in forum Forms
    Replies: 1
    Last Post: 02-23-2011, 04:52 PM
  5. Replies: 1
    Last Post: 11-17-2010, 10:38 AM

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