Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2011

    Unanswered: COPY records from SUBFORM and PASTE to EXCEL?

    hello i have a SUBFORM on my form with rows. i want to click on a button and have these rows pasted into an excel spreadsheet with the headers. i came across this thread for help but it seems to be giving me some kind of formatting error:

    Export any form's recordset to Excel - Access World Forums

    i have this declared at the top of my form's code:

    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 on the button click event i have this:

    Send2Excel Me.ChannelIDSearch, "HHFs"

    it says i have some kind of formatting issue??

    please help thanks!

  2. #2
    Join Date
    Mar 2009
    Provided Answers: 15
    The function Send2Excel is declared as:
    Public Function Send2Excel(frmqryChannelIDSearch As Form, Optional HHFs As String)
    Send2Excel Me.ChannelIDSearch, "HHFs"
    You pass a control not a form as parameter. It should be:
    Send2Excel Me.ChannelIDSearch.Form, "HHFs"
    Provided that ChannelIDSearch is the name of the subform/subreport control.
    Have a nice day!

Posting Permissions

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