Subject: RE: Excel & Cannot create automation object & scheduled agent
Dim session As New NotesSessionDim txtFilename As String
Dim flag As Variant
Dim xlApp As Variant
Dim xlWkBook As Variant
Dim xlSheet As Variant
Dim xlSheet2 As Variant
Dim prodCol As NotesDocumentCollection
Dim scrapCol As NotesDocumentCollection
Dim x As Integer
Dim y As Integer
Dim agent As NotesAgent
Dim agentLog As New NotesLog(“Agent log”)
flag = False
On Error Goto GeneralError
On Error 213 Goto errorAccess
Set db = session.CurrentDatabase
Call agentLog.OpenAgentLog
'start the log off
Call agentLog.LogAction(“Excel export of production and scrap started”)
txtFilename = GetExportFilename(“ProductionAndScrap”)
If txtFilename = “Error” Then
Call agentLog.LogError(0, “The Export Filename document is missing from the Configuration menu, cannot complete the export”)
Exit Sub
End If
If txtFilename = “” Then
Call agentLog.LogError(0, “There is no filename in the This Years Production and Scrap field of the Export Filenames document, cannot continue with export”)
Exit Sub
End If
'we’ve got the filename, make sure the file is there
flag = CheckFileExists(txtFilename)
If Not flag Then
Call agentLog.LogError(0, "The excel file called " + txtFilename + " does not exist. The export cannot continue.")
Exit Sub
End If
'count the production first
Set prodCol = GetDocCollection(“production”)
If prodCol.Count > 0 Then
Call CountColl(prodCol, "prod")
End If
'count the scrap
Set scrapCol = GetDocCollection(“scrap”)
If scrapCol.Count > 0 Then
Call CountColl(scrapCol, "scrap")
End If
If (prodCol.Count > 0) Or (scrapCol.Count > 0) Then
'we only want to write to the spreadsheet if we have some information to write
'create the spreadsheet
Print (“Creating Excel object”)
Set xlApp = CreateObject(“Excel.Application”)
xlApp.Visible = False
xlApp.Application.DisplayAlerts = False
Print (“Opening Spreadsheet”)
xlApp.Workbooks.Open txtFilename
Print (“Opening Worksheets”)
Set xlSheet = xlApp.Workbooks(1).Worksheets(1)
Set xlSheet2 = xlApp.Workbooks(1).Worksheets(2)
Set xlWkBook = xlApp.ActiveWorkbook
xlSheet.Activate
Print (“Writing Production Figures to spreadsheet”)
'if we have some production data, write it to the first sheet
If prodCol.Count > 0 Then
Call WriteArrayToSheet(xlSheet, "prod")
End If
Print (“Writing Scrap Figures to spreadsheet”)
If scrapCol.Count > 0 Then
Call WriteArrayToSheet(xlSheet2, "scrap")
End If
'save the spreadsheet
Print(“Save & Close Spreadsheet”)
Call xlWkBook.saveas(txtFilename)
xlWkBook.Close
xlApp.DisplayAlerts = False
xlApp.Quit
Set xlApp = Nothing
Print(“”)
Call agentLog.LogAction(“Export production and scrap agent finished”)
Call agentLog.Close
Exit Sub
GeneralError:
Call agentLog.LogError(0, "The following error occured: " & Err & " : " & Error$)
Print(“”)
Exit Sub
errorAccess:
Call agentLog.LogError(0, "The Export production and scrap agent does not have access to the folder " & filename & " either change the name of the file you are trying to save, or speak to the IT Department to get access to the folder")
xlWkBook.Close
xlApp.DisplayAlerts = False
xlApp.Quit
Set xlApp = Nothing
Print(“”)
Exit Sub
If I click on Agent Run from the workstation software on the server, it runs fine with no errors. If I run it scheduled, after the line
Set xlApp = CreateObject(“Excel.Application”)
it comes up with cannot create automation object.
I