Solved: Intermittent crashing during Notes-Excel export

[muttleee]

Hiyas,

This is the first time I've done much in the way of vba with Excel although I've done some with Word before now (with much help from the Word Help forum on here). I used the code at this link as the basis of an export function from Lotus Notes to Excel:

http://www.bluestream.org/Domino/Exp...elViaNotes.htm

This works fine as a straight dump of all the information in a view. I then started to tweak it a bit to do some processing on the info in the view and change what's exported accordingly. All seemed to be going fine until it crashed when at the point when it brought up the message box for the progress bar. Now it crashes frequently but intermittently. It can run 4 or 5 times in a row without crashing while other times it crashes on every attempt to run it. It's driving me nuts. It shows the progress bar dialog box but then hangs and nothing happens from there on.

The last 3 columns of the view I am exporting contain dates known as the PPE, PER or PPR date. Either the PPE date will exist on a given record or the PPR and PER dates will. I only want to print the one of the three that falls in the calendar month 2 months from now so there's a bit of processing to work out which one that is. Here are the only bits I've changed in order to achieve that (basically the column headers and values for the last column in the Excel sheet):

[vba]'Start filling in the header row.
col = 1
Dim x As Integer
With xlSheet
For x = 0 To 7

.Cells(1, col) = v.Columns(x).Title
col = col + 1

Next
'hard code title of column 8
.Cells(1, 8) = "PPE/PER/PPR Date"
End With

Set docX = v.GetFirstDocument

'Row by Row, Column by Column, fill in the values
'***********************************************
Row = 2
On Error Resume Next

Dim y As Integer

With xlSheet

While Not docX Is Nothing
For y = 0 To 7
.Cells(Row, y) = docX.ColumnValues(y - 1)
Next

'compute value for PPE/PER/PPR date column
If Not docX.CWsubType(0) = "IT" Then
.Cells(Row, 8) = docX.ColumnValues(8) & " (PPE)"
Else
'for non-IT caseworks
If ((Month(Cdat(docX.PPRdte(0))) - Month(Today) = 2 And Year _
(Cdat(docX.PPRdte(0))) - Year(Today) = 0) Or _
(Month(Cdat(docX.PPRdte(0))) - Month(Today) = -10 And Year _
(Cdat(docX.PPRdte(0))) - Year(Today) = 1)) Then
.Cells(Row, 8) = docX.ColumnValues(9) & " (PPR)"
Else
If ((Month(Cdat(docX.PERdte(0))) - Month(Today) = 2 And Year _
(Cdat(docX.PERdte(0))) - Year(Today) = 0) Or _
(Month(Cdat(docX.PERdte(0))) - Month(Today) = -10 And Year _
(Cdat(docX.PERdte(0))) - Year(Today) = 1)) Then
.Cells(Row, 8) = docX.ColumnValues(7) & " (PER)"
End If
End If

End If

Row = Row + 1
[/vba]
it always crashes at the same point, ie the line:
[vba]NEMProgressSetBarRange hwnd, numDocs[/vba] which is *before* any of the bits I've changed, so it doesn't even reach the new code. I have no idea why it works fine sometimes and not others. I know this is the worst sort of error but if anyone has any suggestions about how to fix this I'd be very grateful!

[lucas]

Line breaks added to your code so it doesn't run off the screen....

[malik641]

Hey muttlee, welcome

Does it tell you which error it is? Is it a type mismatch?

I don't understand why this isn't going to errorHandler3 like it is supposed to? I'm not sure why this is happening....mostly because I'm not the best with API calls, and I haven't done any work with Lotus either. It looks like something happened with the variables passed to the API procedure...but I'm not sure.

[muttleee]

Hey muttlee, welcome

Does it tell you which error it is? Is it a type mismatch?

I don't understand why this isn't going to errorHandler3 like it is supposed to? I'm not sure why this is happening....mostly because I'm not the best with API calls, and I haven't done any work with Lotus either. It looks like something happened with the variables passed to the API procedure...but I'm not sure.

Hiya :-)

Unfortunately it doesn't tell me anything about what sort of error is happening...it just hangs and I can't access the debugger or anything from that point on. There is no actual error message as such. All I know is the line it crashes on, as I mentioned in the original post. I assume it's not something like a type mismatch error because the problem only occurs intermittently and I imagine that a type mismatch would happen every time I run it if that was actually the problem. Also, the offending line works fine in other versions of this agent that don't have the same processing built in. That's my feeling anyway...I am no expert though!

As I said, the export hangs at the same line every time but after that, Notes shuts down altogether if I let it sit for a minute. The only error message I get is a Notes dialog to tell me that Notes NSD is running, just before it shuts down. Notes saves a log file that's 19,500 lines long every time this happens but needless to say it's impossible to make head nor tail of that. :-/

[muttleee]

Quick update - I commented out the progress bar lines in the code thinking that at worst I can get rid of them altogether and it should work fine. Ha! Sounds good in theory but it still crashes intermittently in the same way as before. It seems the whole progress bar is a red herring which makes sense I suppose considering that I was sure there was nothing wrong with it. I'm stumped.

[muttleee]

I think I've sussed it out. Needless to say it was pretty straightforward when I sat down and went through everything logically. Compare this bit of code that prints out the table headings:

[vba]col = 1
Dim x as Integer

For x = 0 To 7

.Cells(1, col) = v.Columns(x).Title
col = col + 1

Next[/vba]
with this bit that fills in the column values:

[vba]For y = 0 To 7
.Cells(Row, y) = docX.ColumnValues(y - 1)
Next [/vba]

As you can see the second loop has docX.ColumnValues(y - 1) in it which I always thought was a bit odd but it seemed to work (initially anyway) even though y-1 starts off with a value of -1. I thought I could use one counter variable to count both the column in the Notes document and the column in the Excel doc but it seems this is where it was going wrong. I changed it to be like the first example (with a separate variable for Notes and Excel column counting) and I haven't had a crash since.

I stilll don't know why it worked so intermittently in the first place but it seems to work now and that's the main thing.