Error running scheduled agent that creates Excel worksheet

hcl-bot · January 8, 2007, 4:50pm

Hello -

I haven’t found a successful response to this error in the forum yet. I have created a scheduled agent that creates an Excel spreadsheet, attaches it to an email then sends it off and deletes it. The code runs successfully when I invoke it manually from my desktop. When I schedule the agent on the server, I receive the error “Unable to set the Orientation property of the PageSetup class”.

I have searched a great deal on this and most people have said it is because there is no default printer set up on the server, but there is. We have even opened up Excel on the server and printed from it to verify that it works. If I remove the part of the code where I am trying to format the Excel spreadsheet (i.e., “pagesetup”), it also completes without any issue. I have tried things like:

Giving myself every agent permission in the server document possible
Putting several different names in the “Run on behalf of” tab of the agent to make sure the same that the server sees running the agent is the same person who has a default printer set up.
Adding things to my code like “activesheet.uprotect” or “activesheet.activate”

I was wondering if anyone out there had this issue with the “mysterious” no default printer issue but found out what the underlying issue was.

Any help is appreciate, code posted below.

Matthew

'Declare our variables

Dim columnTitle(1 To 6)As String

Dim view As NotesView

Dim db As NotesDatabase



Dim s As New NotesSession

Set db = s.CurrentDatabase

Dim doc As NotesDocument

Dim getdoc As NotesDocument



papersizechoice=1	

paperorient=2



Dim collection As NotesDocumentCollection     

Dim searchquery As String

Dim dayofweek As Integer

Dim reportenddate As notesdatetime

Dim reportbegindate As notesdatetime

Set DbProfile = DB.getprofiledocument("Database Profile")



'find the Sunday previous to today's date

dayofweek = Weekday(Today)

If dayofweek = 1 Then

	Set reportenddate = New NotesDateTime ( Today)

Elseif dayofweek = 2 Then

	Set reportenddate = New NotesDateTime (Today)

	Call reportenddate.AdjustDay( -1 )

Elseif dayofweek = 3 Then

	Set reportenddate = New NotesDateTime (Today)

	Call reportenddate.AdjustDay( -2 )

Elseif dayofweek = 4 Then

	Set reportenddate = New NotesDateTime (Today)

	Call reportenddate.AdjustDay( -3 )

Elseif dayofweek = 5 Then

	Set reportenddate = New NotesDateTime (Today)

	Call reportenddate.AdjustDay( -4 )

Elseif dayofweek = 6 Then

	Set reportenddate = New NotesDateTime (Today)

	Call reportenddate.AdjustDay( -5 )

Elseif dayofweek = 7 Then

	Set reportenddate = New NotesDateTime (Today)

	Call reportenddate.AdjustDay( -6 )

End If



'find the date one week before the Sunday above, we now have one complete week



Dim tempdate As String

tempdate = reportenddate.DateOnly

Set reportbegindate = New NotesDateTime (tempdate)

Call reportbegindate.AdjustDay(-6)



'build our search query

begindatestring$ = reportbegindate.dateonly

enddatestring$ = reportenddate.DateOnly

searchstring1$ = |(FIELD form ="fmIncident")|

searchstring2$ = |(FIELD date <= |& enddatestring$ &|)|

searchstring3$ = |(FIELD date >= |& begindatestring$ &|)|

searchquery$ = searchstring1$ + " And " + searchstring2$ + " And " + searchstring3$



'update the index to search on first, then search db

Call db.UpdateFTIndex(True)

Set collection = db.FTSearch( searchquery$, 0, FT_SCORES)          

Set getdoc = collection.GetFirstDocument     

Dim nextdoc As NotesDocument

Set nextdoc = collection.GetFirstDocument          



'Start creating the Excel Spreadsheet

Dim title As String

title= "Stars Weekly Incident Report"

header = reportbegindate.DateOnly + " - " + reportenddate.dateonly



Dim xlApp As Variant

Dim xlsheet As Variant

Set xlApp = CreateObject("Excel.application")

xlapp.visible=False    ' keep excel closed since this is an agent

xlApp.Workbooks.Add

Set xlsheet = xlApp.Workbooks(1).Worksheets(1)

xlsheet.activate

xlsheet.unprotect

XlApp.Windows(1).DisplayGridlines=False



'Format the Excel spreadsheet so report looks good



XlApp.Cells.Font.Name = "Verdana"

XlApp.Cells.Font.Size = 8

XlApp.Cells.WrapText = True

XlApp.Windows(1).DisplayGridlines = False

XlApp.Columns(1).ColumnWidth = 26

XlApp.Columns(2).ColumnWidth = 12

XlApp.Columns(3).ColumnWidth = 14

XlApp.Columns(4).ColumnWidth = 15.86

XlApp.Columns(5).ColumnWidth = 15

XlApp.Columns(6).ColumnWidth = 55.0



'activate the current excel worksheet	

xlsheet.Activate  	

xlsheet.Name = Title



'create the column headings

Dim col(6) As Variant

col(1) = "A"

col(2)="B"

col(3)="C"

col(4)="D"

col(5)="E"

col(6)="F"



Dim row As Integer

row = 5	'start the report at row 5 since we have a few lines of headings



 ' Define repeating Column Header	

columntitle(1) = "Date/Time"

columntitle(2) = "Assigned To"

columntitle(3) = "Customer"

columntitle(4) = "Type"

columntitle(5) = "River/Mile"

columntitle(6) = "Event Details (see stars report for full details)"



'Now run through the documents we found in the database and start inserting them into the report

Dim t As String 

Dim j As Integer

j=1

Dim counter As Integer



Set nextdoc = collection.GetFirstDocument

Set getdoc =collection.GetFirstDocument



counter=6 	



Do Until(nextdoc Is Nothing)

	xlsheet.Range(col(1) +Cstr(row)).value = nextdoc.Barge(0)

	xlsheet.Range(col(1) +Cstr(row)).Font.Bold = True

	xlsheet.Range(col(1) +Cstr(row)).Font.Size = 9

	xlsheet.Range(col(1) +Cstr(row)).Font.ColorIndex = 25

	

	row=row+2

	

	For b = 1 To 6

		xlsheet.Range(col(b)+ Cstr(row)).Borders(9).Weight = 2			

		xlsheet.Range(col(b)+ Cstr(row)).value  = columnTitle(b)

		xlsheet.Range(col(b) +Cstr(row)).Font.Bold = True		

	Next  

	

	row=row+1

	xlsheet.Range(col(1) +Cstr(row)).value = nextdoc.Date(0)

	

	xlsheet.Range(col(2) +Cstr(row)).value = nextdoc.AssignedTo(0)

	xlsheet.Range(col(3) +Cstr(row)).value = nextdoc.Customer(0)

	xlsheet.Range(col(4) +Cstr(row)).value = nextdoc.IncidentType(0) + "/" + nextdoc.IncidentSubType(0)

	xlsheet.Range(col(5) +Cstr(row)).value = nextdoc.River(0)+"/"+ nextdoc.MilePoint(0)

	xlsheet.Range(col(6) +Cstr(row)).value = nextdoc.EventDetails(0)

	

	

	row=row+3

	Set nextdoc = collection.GetNextDocument(getdoc)

	Set getdoc= nextdoc          

	

Loop





'Format the Excel report, I could not get the part to work where a picture was inserted at the top of the report

xlapp.Range("A5:F" +Cstr(row-1)).HorizontalAlignment = 6

xlapp.Range("A5:F" +Cstr(row-1)).VerticalAlignment = 1



xlapp.ActiveSheet.PageSetup.Orientation = 2 





'More formatting 	

XlApp.range("A1:F" +Cstr(row-1)).Select     

With XlApp.Selection     		

	.columns.WrapText=True		

	.Columns.AutoFit          

End With       



With XlApp     

	

	.Range("A1:F1").Font.Size = 12

	.Range("A2:F2").Font.Size = 10

	.Range("A3:F3").Font.Size = 9

	.Range("A1").value="STARS"		

	.Range("A2").value="Weekly Incident Report - By Vessel"

	.Range("A3").value=Ucase$(Header) 

	.Range("A2:F3").HorizontalAlignment = 7

	.Range("A2:F2").MergeCells=True

	.Range("A3:F3").MergeCells=True

	

End With          

With Xlapp.range("A1:A3")     

	.font.Bold=True  

	

End With          



'make report print correctly, etc.

With xlsheet.PageSetup

	

	.RightFooter = "Page &P" & Chr$(13) & "Date: &D"

	.CenterFooter = "Service Tracking & Reporting System"

	

	.PrintArea = ("A1:F"+Cstr(row-1))               

End With          









'this is where we detach the report to the machine the agent is running on, we first try to delete in case there is an old report, then

'we create a new one. We also have to terminate the excel application and worksheet so it isn't left running

On Error Resume Next	

createobject("scripting.filesystemobject").deletefile("C:\weeklybargeincidentreport.xls") 	

nameoffile = "c:\weeklybargeincidentreport.xls"

xlapp.ActiveSheet.saveas nameoffile

xlapp.ActiveWorkbook.close False	

Set xlsheet = Nothing

Set xlapp = Nothing





'*Here we will email the report to whomever is specified below

Dim mailList As String

Dim newDoc As NotesDocument

Dim mLineText As String 

Dim deckBarge As String



Dim mailDoc As New NotesDocument(db)

Set richStyle = Session.CreateRichTextStyle     

Dim richText As New NotesRichTextItem(mailDoc, "Body")   

Dim object As NotesEmbeddedObject



Call mailDoc.AppendItemValue("Subject", "Weekly STARS Report")   

Call mailDoc.AppendItemValue("Form", "Memo")   

Call mailDoc.AppendItemValue("Principal","STARS Reporting/CANAL_UNION@CANAL_UNION")

mailDoc.SendTo = DbProfile.IncidentsManager







mLineText = "Please find this weeks Barge Incident Report."  & Chr(13) & Chr(13)

Call richText.AppendText(mLineText)     

Set object = richtext.EmbedObject(EMBED_ATTACHMENT, "", "c:\weeklybargeincidentreport.xls")

richStyle.Italic = True   

Call richText.AppendStyle(richStyle) 

Call mailDoc.Send( True )	



' delete our report file off the machine the agent ran

createobject("scripting.filesystemobject").deletefile("C:\weeklybargeincidentreport.xls")

End Sub

hcl-bot · January 8, 2007, 4:58pm

Subject: Error running scheduled agent that creates Excel worksheet

I have run into this issue in the past and I seem to recall that I resolved it by setting the server to run under a specific operating system user account that had access to the default printer. Running under the defaults would not work.

hcl-bot · January 8, 2007, 5:07pm

Subject: RE: Error running scheduled agent that creates Excel worksheet

Thanks for your response.

Are you saying to change “LOG ON AS” service credentials for Lotus Domino Server to the domain admin instead of the using LocalSystem?

hcl-bot · January 8, 2007, 5:30pm

Subject: RE: Error running scheduled agent that creates Excel worksheet

Yes - what I have done in the past is create an account just for the Notes Servers and then give them the access they need.