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

[StillUsingNotes]

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

 

[SamTYler]

Try isolating the report agent by having it call and forget a different agent to run PostMacro.

That has the advantage of keeping your troubleshooting to a one line agent and PostMacro.

 

[StillUsingNotes]

Thanks Sam!

already had a testing program, which runs the macro as a separate test, so I tweaked that to save its results and to use any data (such as the biggest example, 1800 rows). Results were: on system A the macro works fine , but on system X it seems the macro did not run.

A and X run Windows Server 2003. A runs Excel 2007, while X runs Excel 2003. Also, A is a VM while X is a standalone PC server.

X is provisioned with more physical RAM memory than A has virtual RAM memory. I would think size-dependency of the problem implies a memory constraint but that seems unlikely. In addition, A is a busy server while X idles along doing almost nothing.

i am planning to upgrade to xl 2007 of course.

--Rich

 

[StillUsingNotes]

Switching to Excel 2007 did not fix it.

Watching it run at the server console, I can see it making changes starting at the top and working down. Soon after reaching row 1400, the macro just stops running, and all the changes disappear.

Is there some sort of "pending changes" limit in the macro interpreter?

I plan to try doing intermediate Save()s .

 

[StillUsingNotes]

The mystery deepens. It turns out the main loop was running, and making changes, but, stopping prematurely, because some blank cells in column A were mixed in. (It ends its loop over all the data rows when it finds a blank cell.)

At this point I can only speculate that the "sort" step encounters an internal Excel problem, but looping through all rows first prevents that problem. Since the loop removes all <CR>s within the cells, possibly the CRs provoke a problem.

I have encountered other bugs doing sorts in Excel 2007.

 

[tusharm]

A VBA solution that relies on selects and activecells would scare the heck out of me. You may want to see
Beyond the macro recorder
http://www.tushar-mehta.com/excel/vba/beyond_the_macro_recorder/index.htm

Also, when you post, please format it for readability. Check the '#' button just above the textbox in the form used for posting in this forum.

 

[StillUsingNotes]

1) Thanks for that, I am a newbie (that is, newbie here - when I started coding, I was putting my code on punched cards! ) Formatted macro is at the bottom of this post.

2) I have corrected the data problem of blank cells mixed in. This has caused the whole thing to produce correct output again, so certainly this is not a priority, but I still hope to understand and improve things.

3) I do not understand what is scary about my use of these features. I am not sure how else I would do this task. The task is : split up rows that have multiple data in them. The pieces of data are originally separated by <cr> characters in the cells. The algorithm is: for each data row, strip off the first value from the cells that have multiple values. Insert a new row before that row. Fill it with those first values. Repeat until there is only one value in the row, then move on to the next row.

4) I am trying to understand why it is that I see changes onscreen, but they are not saved. My current theory: The Sort has trouble with embedded <cr>CR chars . Then, Excel closes the file without a save. Then, the agent tries to save and close the file (via OLE), but nothing happens, including, no errors. Then, the agent loops to the next file. (The macro itself does not do a Save. ) Whenever the loop completes, which removes all embedded CR chars, the save works fine. It was working nightly for a year before these improper blank cells started to appear.

5) I see that using a blank cell to signal the end of data is non-brilliant. Considering that the macro keeps inserting new rows, is there a good general purpose way to loop through the rows until it is past the end of the original data?



Here is the formatted code: (nice feature! great forum!)

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     </cr></cr>

</cr></cr>

 

[StillUsingNotes]

Thanks for all the help here.

For other Excel newbies, I have since learned that:
Excel has built-in ways to reference the "last cell" of the spreadsheet, instead of homebrewing bad solutions.
Using Selections is very bad, while Ranges are very good.