Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2007

    Question Unanswered: Automate Excel From Access to generate custom report

    My goal is for my Code in Access to Create an Excel file, Open the file in Excel, fill in the data i will have generated in a previous process, and format the worksheet.

    Alternatively, is it better to create the file, "connect" to the file without displaying Excel, and then use a Shell command to display the file for the user?

    I found this code in an old post and am trying to modify it for my use. My button click code calls FeedbackReportMaster and then FeedbackReportMaster is calling FRGEN. I know it could be combined but I need it separated as I will be calling different functions based on selections made on the form for report generation.

    Since I am not passing the name of an existing Workbook, the ProcError is invoked, and the file is created, when my code runs it still goes to ProcError. ERR is set to 0 (though i am not sure where this value comes from. And Excel is not ever displayed.

    I have obviously not done this before so I am just looking for help...

    Function FeedbackReportMaster()
    Dim strDT As String, strYR As String, strMO As String, strDY As String, strPath As String, strExt As String
    Dim appXL As New Excel.Application
        'Set the path info
        strDT = Date
        strDT = Format(strDT, "YYYYMMDD")
        strPath = "C:\Program Files\vibePublisher\Reports\Feedback Report " & strDT & ".xls"
        'Call FRGEN to create the excel file
        Call FRGEN(strPath)
    End Function
    Function FRGEN(strWorkBook As String)
    Dim objXLApp As New Excel.Application 'Excel.Application
    Dim objXLWb As New Excel.Workbook 'Excel.Workbook
    Dim objXLSheet As New Excel.Worksheet 'Excel.Worksheet
    Dim strWorkSheet As String
    'Error handler
    On Error GoTo ProcError
    'Change the cursor
    DoCmd.Hourglass True
    'Start Excel
        Set objXLApp = CreateObject("Excel.Application")
    'Open workbook, error routine will 'create it if doesn't exist
        Set objXLWb = objXLApp.Workbooks.Close(strWorkBook)
        Set objXLWb = objXLApp.Workbooks.Open(strWorkBook)
    'Select a worksheet, if sheet doesn't exist 'the error routine will add it
        If strWorkSheet = "" Then
            strWorkSheet = "Sheet1"
        End If
        Select Case Err
            Case 9 'Worksheet doesn't exist
            Set objXLSheet = objXLWb.ActiveSheet
            ' = strWorkSheet
            Resume Next
            Case 1004 'Workbook doesn't exist, make it
            Set objXLWb = objXLApp.ActiveWorkbook
            objXLWb.SaveAs strWorkBook
            Resume Next
        Case Else
            DoCmd.Hourglass False
            MsgBox Err.Number & " " & Err.Description
            Resume 0
        End Select
    End Function
    Last edited by Brent Blevins; 08-16-07 at 14:40.

  2. #2
    Join Date
    Jun 2004
    Seattle, WA
    Did you happen to reads Pootle's post in the code bank?
    My Blog

  3. #3
    Join Date
    Jun 2007
    I broke rule #1..

    Let me go look!

Posting Permissions

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