My xl macro runs fine in my Domino scheduled agent ... except when it doesn't

Hi,
i am mystified and willing to test anything in order to solve this. All WAG's will be gratefully accepted!

I have a LotusScript agent that runs as a scheduled agent on a Domino (Lotus Notes) server. It creates a report as an Excel spreadsheet, saves it, and attaches it to a Notes document. There is a separate report for each client who wants it. It uses the "COM/OLE" interface from LotusScript to automate Excel. It basically reports one row per Notes document. No problems there.

A year ago, one client wanted a mod : only one address per row instead of several. It was way easier to code a macro that would post-process the report, than to revise the reporting logic. The macro is stored in a spreadsheet that is attached to the document that describes the client's reporting options. I added to the reporting logic a final step that runs the macro, ending with :
retval = xlApp.Run( "my Temp Path\TempXL.xls!PostMacro" )

This worked great until two weeks ago. Since then, the system usually has stored the report as if the macro had never run.

This client's report has grown over time and it has reached 1,500 rows. In testing, using scheduled agents, I found that eveything works with smaller files, but not with these large files. (I have not yet determined how large it needs to be to break it.)

The macro works fine on all files when I run it on my desktop PC, and equally when I run it as a (Windows) user on the server. The problem only occurs when it is a scheduled agent kicking off the macro via COM/OLE, and then, only when there are many rows.

There are no errors logged by the server, and no run-time LotusScript errors when the agent runs. An empty string is always the return value from
xlApp.Run() .

I added a 30-second sleep after the call to xlApp.Run() and before saving the result, but, it did not help.

If there is anything that I can try, I will try it!

I think that the code is OK but here it is:

Sub PostMacro()
'
' PostMacro Macro
' The *** Postmacro : one per line, and, sort by Pub then by name
'
' Keyboard Shortcut: Ctrl+p
'

Dim LSearchRow As Long
Dim sAddresses As String

On Error GoTo Err_Execute
'Start search in row 4
LSearchRow = 4
sAddresses = Trim(Range("A" & CStr(LSearchRow)).Value)

' Loop over all lines from 4 onward until a blank cell A is found, which means it is the last row
Do While Len(Trim(sAddresses)) > 0
' need to be here in order to select-row-relative and to paste
Range("A" & CStr(LSearchRow)).Select

Dim nLetter As Long
Dim sLetter As String, sLeft As String
Dim sAddress As String

' Multi values? if not, ignore the line
If InStr(sAddresses, Chr$(10)) <> 0 Then

' Loop through the value of the A cell, looking for first Carriage Return
For nLetter = 1 To Len(sAddresses)
sLetter = Mid(sAddresses, nLetter, 1)
If sLetter = Chr$(10) Then ' ? Found Carriage Return ?
If nLetter < Len(sAddresses) Then ' ? was it just trailing whitespace(s) at end of cell?
' We found that sAddress is not the last address in the cell.
' So: 1) Split the cell into the first address and the rest
' 2) Copy this row 3) Insert the copy of this row 4) set column A to just the
' first address 5) Set column A of the (new) next row to the rest of the addresses
' 6) Also split up columns E, G, and J using the <CR>to delimit
' 7) Kill the loop through sAddresses 8) Let the outer loop advance to the
' next row, which is the newly inserted one with the rest of the addresses
' 1) Splits
sAddress = Trim(Mid(sAddresses, 1, nLetter - 1))
Dim sRest As String
sRest = Trim(Mid(sAddresses, nLetter + 1)) ' gets all to the end
' 2) Copy
Dim x As String
x = CStr(LSearchRow)
Range(x & ":" & x).Copy
' Rows(CStr(LSearchRow) & ":" & CStr(LSearchRow)).Select
' Selection.Copy
' 3) Insert the copy of this row
ActiveCell.Rows("1:1").EntireRow.Select
Application.CutCopyMode = False
Selection.Copy
Selection.Insert Shift:=xlDown
' 4) Write the single address 5) Write the remaining addresses
Range("A" & CStr(LSearchRow)).Value = sAddress
Range("A" & CStr(LSearchRow + 1)).Value = sRest
' 6) Also split up the other columns if they have multi values, using the <CR>to delimit
Call SplitOn(Chr$(10), "B", CStr(LSearchRow))
Call SplitOn(Chr$(10), "C", CStr(LSearchRow))
Call SplitOn(Chr$(10), "D", CStr(LSearchRow))
' 7) Kill inner loop, which is the "Loop through the value of the A cell,
' looking for first Carriage Return"
Exit For
End If
End If
Next
End If
' 8) Let the outer loop advance to the next row
' It may be a newly-inserted-and-modified portion of the current row.
LSearchRow = LSearchRow + 1
sAddresses = Range("A" & CStr(LSearchRow)).Value
Loop
' finish with a big sort
Range("$A$4").Select
' select all cells after A4
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select

Selection.Sort Key1:=Range("C4"), Order1:=xlDescending, Key2:=Range("E4") _
, Order2:=xlAscending, Key3:=Range("A4"), Order3:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
xlSortNormal

' Also, an AutoFit is needed in Excel2007
Columns("A:A").Select
Selection.Rows.AutoFit

Exit Sub
Err_Execute:
MsgBox "An error occurred."
End Sub

Public Sub SplitOn(sDelim As String, sCol As String, sRow As String)
If IsNull(sDelim) Or IsNull(sCol) Or IsNull(sRow) Then Exit Sub
If Len(sDelim) = 0 Or Len(sCol) = 0 Or Len(sRow) = 0 Then Exit Sub

Dim sValu As String
sValu = CStr(Range(sCol & sRow).Value)
If IsNull(sValu) Then Exit Sub

Dim i As Integer

i = InStr(1, sValu, sDelim)
If i = 0 Then Exit Sub

Dim sFirst As String, sRest As String

' Cleaveth
sFirst = Trim$(Left$(sValu, i - 1))
sRest = Trim$(Mid$(sValu, i + 1))

' And storeth
Range(sCol & sRow).Value = sFirst
Dim iRow As Long
iRow = CLng(sRow) + 1
Range(sCol & CStr(iRow)).Value = sRest

End Sub