muttleee
09-22-2006, 03:58 AM
Hiyas, :hi:
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/ExportToExcelViaNotes.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):
'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
it always crashes at the same point, ie the line:
NEMProgressSetBarRange hwnd, numDocs 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!
:dunno:banghead:
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/ExportToExcelViaNotes.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):
'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
it always crashes at the same point, ie the line:
NEMProgressSetBarRange hwnd, numDocs 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!
:dunno:banghead: