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...
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
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
On Error GoTo ProcError
'Change the cursor
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"
Select Case Err
Case 9 'Worksheet doesn't exist
Set objXLSheet = objXLWb.ActiveSheet
'objXLSheet.name = strWorkSheet
Case 1004 'Workbook doesn't exist, make it
Set objXLWb = objXLApp.ActiveWorkbook
MsgBox Err.Number & " " & Err.Description