Schedule Agent hanging when trying to save excel file

HCL Domino Domino Forum
1

Hi,

I have a schedule agent running on server. The agent runs on a view and does the following :

  1. Extract attachment from the documents of the view and puts them into a hard drive of the server.

  2. Creates an excel sheet and puts the name of the attchment in the excel sheet corresponding to the document no.

  3. Saves the excel sheet and closes it.

  4. Creates a new doc and embeds the excel sheet into the rich text field of the doc.

However, the agent runs fine uptill 2nd step. Howevr , wen I am tryin to save the excel sheet , the agent hangs up and does not quit from the agent manager . Moreover , it does not throw any error msg.

Please have a look at the code :

Sub Initialize

On Error Goto ErrorHandler





Print "agtExportToExcel is about to start running****************"

Dim los As New notessession

Dim lodb As notesdatabase

Dim loVw As notesview

Dim loForm As NotesForm

Dim lodoc As NotesDocument



Dim lvvcols As Variant

Dim liUvcols As Integer

Dim lirows As Integer



Set lodb = los.currentdatabase

Set loForm = lodb.GetForm("frmEmbedExcelSheet")

Set loVw = lodb.getview("PAR Data Export")



Set xlApp = CreateObject("excel.application")

Set xlWb = xlApp.WorkBooks.Add()

Set xlWs = xlWb.WorkSheets.Add()

xlFileName = "Excel Sheet Data Export"



Msgbox "Callling Export Agent"

%REM

lvxlApp.StatusBar = "Creating WorkSheet. Please be patient..."

lvxlApp.Visible = True

lvxlApp.Workbooks.Add

lvxlApp.ReferenceStyle = 2

Set lvxlsheet = lvxlApp.Workbooks(1).Worksheets(1)

lvxlsheet.Name = "QSI Attachment "

%END REM

lirows = 0

licols = 0



'xlApp.StatusBar = "Creating Cells and Creating Cell Headings. Please be patient..."



Msgbox  "Creating Cells and Creating Cell Headings. Please be patient..."

xlWs.Range("A1").OffSet(0,licols).Font.Bold=True ' make bold

xlWs.Range("A1").OffSet(0,licols).HorizontalAlignment = 3 'center in cell

xlWs.Range("A1").OffSet(0,licols).value="Record Number" 'Column titles



licols=licols+1



xlWs.Range("A1").OffSet(0,licols).Font.Bold=True ' make bold

xlWs.Range("A1").OffSet(0,licols).HorizontalAlignment = 3 'center in cell

xlWs.Range("A1").OffSet(0,licols).value="Attachment Name" 'Column titles



licols = licols + 1	



'xlWs.Range("A1").OffSet(0,licols).Font.Bold=True ' make bold

'xlWs.Range("A1").OffSet(0,licols).HorizontalAlignment = 3 'center in cell

'xlWs.Range("A1").OffSet(0,licols).value="File Size" 'Column titles

'

'licols = licols + 1	



xlWs.Range("A1").OffSet(0,licols).Font.Bold=True ' make bold

xlWs.Range("A1").OffSet(0,licols).HorizontalAlignment = 3 'center in cell

xlWs.Range("A1").OffSet(0,licols).value="Attachment Path" 'Column titles



lirows=1

licols=0 'to reset the column position



Set lodoc = loVw.getfirstdocument

'Msgbox loVw.EntryCount

While Not lodoc Is Nothing

	'lvxlApp.StatusBar = "Importing Data from Lotus Notes Application. Please be patient..."

	lsvar = lodoc.ContNum(0)

	xlWs.Range("A1").OffSet(lirows,0).value = lsvar

	

	licols = licols+1

	

	Call fpsExtractingtAttachment(lodoc,lirows,licols,xlWs)

	

	licols = licols-1'decrementing the column to get the attname corr to the record no

	lirows=lirows+1'incrementing the row to set the record nos 

	

	If flag = 0 Then 'to check it entered the getatt function or not

		lirows1 = lirows- 1'decrementing to write in corresponding to the record no

		licols1 = licols1 +1                        ' + to write the code corr to the record no

		xlWs.Range("A1").OffSet(lirows1,licols1).value =  "No Attachment Exist"

		licols1= licols1+1

		xlWs.Range("A1").OffSet(lirows1,licols1).value =  "No Attachment Exist"

		licols1=licols1-2 ' decrementing by 2 ,so that the cell point on th rite cell

	End If

	Set lodoc = loVw.GetNextDocument(lodoc)

Wend



Msgbox "After Extracting Attachment function"

@@@@@ PRINTS THE ABOVE STATEMENT , HOWEVER IT STUCKS AT THE BELOW LINE @@@@

xlWb.saveas xlFileName



xlWb.Close True







Msgbox "Before fpsExcelSheetDataExport"

Call fpsExcelSheetDataExport(xlFileName,loForm,lodb)



'xlApp.visible=True



Print "agtExportToExcel ENDS"

Cleanup:

Exit Sub

ErrorHandler:

Messagebox("Error : An unexpected error @Initialize_ "& Error &" has occured at line no _"& Erl &",while trying to submit the request.")

Goto Cleanup

End Sub

Any help would be appreciated.

Thanks n Regards,

Meggie.

2

Subject: schedule Agent hanging when trying to save excel file.

As far as i can see you are trying to save a file called “Excel Sheet Data Export”. Is that correct?

There are some issues:

  1. I have no idea where that file is being saved because no path is specified. Maybe the agent dont have access to create files there?

  2. Maybe a file of the same name allready exist and for some reason is not possible to over wright (maybe it is open by another excel instance?)

Solution: add a path (that exist and with proper access) to the filename and make the filename unique.

Like this maybe:

xlFileName = “d:\xlFiles\DataExport” & now

“& now” adds a time stamp to the filename so it is unique

3

Subject: schedule Agent hanging when trying to save excel file.

Go to the agent properties (2nd Tab),Under set runtime security level,

Choose Allow restricted operations