Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2008

    Unanswered: Export report to excel

    Hi, I used access 2003 and I have a report based on a crosstab query it exports to a excel template via a macro and it works fine; my problem is that that I need access not to over write the template but to add the report to the template even if it is to a new worksheet so I can reference to the data to the worksheet with the template.
    Here is the code:
    Function export_test()
    On Error GoTo export_test_Err

    DoCmd.OutputTo acReport, "Progress Report All Tasks test222", "MicrosoftExcelBiff8(*.xls)", "C:\Documents and Settings\randy\Desktop\test222.xlt", False, "", 0

    Exit Function

    MsgBox Error$
    Resume export_test_Exit

    End Function
    I'm very new to programing any help will be appreciated

  2. #2
    Join Date
    Jun 2002
    Mpls/St.Paul area
    'If you can export from the cross tab query try this.

    Function ExportReport()
    Dim strExcelFile As String
    Dim strWorksheet As String
    Dim strDB As String
    Dim strQuery As String
    Dim objDB As Database

    strExcelFile = "C:\MyLocation\MySpreadSheet.xls"
    strWorksheet = "NewSheetName"
    strDB = "C:\MyDBLocation\MyDatabase.mdb"
    strQuery = "MyCrossTab"

    Set objDB = OpenDatabase(strDB)

    objDB.Execute _
    "SELECT * INTO [Excel 8.0;DATABASE=" & strExcelFile & _
    "].[" & strWorksheet & "] FROM " & "[" & strQuery & "]"
    Set objDB = Nothing

    End Function

    This is mostly from

Posting Permissions

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