Results 1 to 3 of 3

Thread: MSAccess

  1. #1
    Join Date
    Oct 2002

    Post Unanswered: MSAccess

    hi there,
    i am trying to export data from a subform to the excel sheet.....if i do it using comand button wiht macro built is just exporting the data from the main from and not from the subform....please anyone help me with this ...and it is very urgent.
    thank you.

  2. #2
    Join Date
    Oct 2001
    The subform does not automatically come with the parent form.

    Add a second action in the macro to export the subform. You will need to specify a second file, otherwise it will be overwritten.

    You can not specify the name of the worksheet or range to use during the export, using any of the built in fucntions such as transfer spreadsheet or OutputTo.

    If you want both recordsources exported to the same workbook, you need to write vba code against excels object model.

  3. #3
    Join Date
    Oct 2001
    Change my mind - despite the help files saying that you can not specify a range on an export - you can with TransferSpreadsheet . You just have to make sure that the Range defined is large enough to hold your recordset(see below).

    This example uses a Workbook (c:\Book1.xls) that has two worksheets. The name of the worksheet doesn't matter when using ranges. Each worksheet has its own Range "MainForm" and "SubForm"

    "MainForm" range =Sheet1!$A$1:$C$4
    =Sheet1!$A$1:$C$4 is receiving an export of three fields.
    The range must be equal in the number of cells to the number of fields, but will shrink or grow depending on the number of rows.

    What I have done in the code below is grab the recordsource for the forms and append to a temp table that is created on the fly. Then transfer the table to the spreadsheet's range, and finally - delete the temp table and start over with the form's subform.

    Function TransferToExcel(strTable As String, strFileName As String, strRange As String) As Boolean

    On Error GoTo PROC_ERR
    TransferToExcel = True

    DoCmd.TransferSpreadsheet _
    TransferType:=acExport, _
    SpreadSheetType:=acSpreadsheetTypeExcel97, _
    TableName:=strTable, _
    FileName:=strFileName, _
    HasFieldNames:=True, _

    Exit Function

    MsgBox Err.Description
    TransferToExcel = False
    Resume PROC_EXIT

    End Function

    Private Sub cmdExport_Click()
    On Error GoTo Err_cmdExport_Click

    Dim fOK As Boolean
    Dim strSQL As String

    strSQL = "SELECT * INTO Temp_t FROM (" & Me.RecordSource & ");"
    DoCmd.RunSQL strSQL
    fOK = TransferToExcel("Temp_t", "C:\Book1.xls", "MainForm")
    DoCmd.DeleteObject acTable, "Temp_T"

    strSQL = "SELECT * INTO Temp_t FROM (" & Me![SubformName].Form.RecordSource & ");"
    DoCmd.RunSQL strSQL
    fOK = TransferToExcel("Temp_t", "C:\Book1.xls", "SubForm")
    DoCmd.DeleteObject acTable, "Temp_T"

    Exit Sub

    MsgBox Err.Description
    Resume Exit_cmdExport_Click
    End Sub

    Not sure if this helps your problem - but I wanted to see if there was a way around it.


Posting Permissions

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