Link to home
Start Free TrialLog in
Avatar of AliciaVee
AliciaVee

asked on

Export to Excel -- Summary agent

Experts,
I have the attached code, which I used successfully in another database (client version 6.5).  This still works, for 4 out of the 5 views, but now one of the views don't work -- giving me the following error:

Title of error: Error processing Excel Export:
There was a general Excel export error: subscript out of range -9

What I am really trying to do now, is use this code in another database -- and for my summary views (categorized views) all of the do not work.  Not sure why it isn't working -- the only change is that I am now using Excel 2007 instead of Excel 2000 -- would that matter?  

See attached code.
Thanks in advance.
'ExportCategoryExcel: 

Option Public
Option Declare
Const xlWorkbookNormal = -4143
Const xlEdgeBottom = 9
Const xlContinuous = 1
Const xlThin = 2
Const xlEdgeTop = 8
Const xlThick = 4
Const fontName="Arial"
Const fmtGeneral="General"
Const fmtCurrency="#,##0;[Red](#,##0)"
Const fmtNumber="#,##0;[Red](#,##0)"
Const fmtPercent="#,##0.0%;[Red](#,##0.0%)"
Const fmtGM="#,##0.00;[Red](#,##0.00)"
Const fmtCustID="00000000"
Const fmtMarkup="#,#0.00;[Red]-#,##0.00"
Dim session As Variant
Dim ws As Variant
Dim db As NotesDatabase
Dim doc As NotesDocument
Dim view As NotesView
Dim vname As String
Dim vcols As Variant
Dim uvcols As Integer
Dim rows, cols, maxcols, x As Integer
Dim result As Variant
Dim trackCols() As String
Dim theTitles() As String
Dim viewName As String
Dim errorFLAG As Integer
Dim xlApp As Variant
Dim xlSheet As Variant
Dim excelTitle As String
Dim excelCompany As String
Dim continue As Variant
Dim cRGBCream As Long, cRGBBlue As Long, cRGBBlack As Long,cRGBRED As Long, cRGBYellow As Long
Dim cRGBGrey As Long,cRGBLTBlue As Long,cRGBMedBlue As Long

Sub Initialize
      'Export Entire View Documents Agent
	On Error Goto ErrorHandler      
	Continue = True
	Set session = New NotesSession      
	Set db = session.currentdatabase
	Set ws =New NotesUIworkspace      
	Dim view As NotesView      
	Dim uiview As NotesUIView
	Set uiview = ws.currentview
	Dim numdocs As Integer
	Dim platform As String
	Dim thisTitle As String
	viewname = ws.currentview.viewname
	Set view = db.getView(viewName)
	If view Is Nothing Then
		Msgbox "Sorry, there was an error loading this view. " + Chr(10) + "Please contact the database administrator",,"Error loading view"
		Exit Sub
	End If
      'Creating colors for format
	cRGBCream= makeRGB(255,224,191)
	cRGBBlue=makeRGB(0,0,80)
	cRGBBlack=makeRGB(0,0,0)
	cRGBRed = makeRGB(208,0,0)
	cRGBGrey=makeRGB(240,240,240)
	cRGBYellow=makeRGB(255,255,192)
	cRGBLtBlue=makeRGB(224,255,255)
	cRGBMedBlue = makeRGB(0,0,255)      
      'Check to see if the user is on a MacIntosh
      'the "Create Object" function does not run on a Mac
	'platform = session.Platform
	'If Not Instr (platform, "MacIntosh") = 0 Then
		'Messagebox ("This function cannot be run on a MacIntosh. Please use a PC to pull this data into a spreadsheet.")
		'Exit Sub
	'End If
	Dim nav As NotesViewNavigator
	Dim entry As NotesViewEntry
	Dim colEntries As Variant
     'Get all selected docs from the view or the entire view
	numdocs = view.allentries.count      
	Dim i As Long
	Dim j As Long
     'Get the number of columns in the view, and store the column headings in an array
	vcols = view.Columns
	uvcols = Ubound(vcols)
	Redim theTitles(uvCols+1) As String      
     'Create the Excel spreadsheet, hide it from the UI, and add a new workbook
	Set xlApp = CreateObject("Excel.Application")
	xlApp.Visible = False 'hide Excel while processing
	xlApp.Workbooks.Add
     'Set the Excel row and column heading to R1C1 reference style so that we can loop with numbers instead of letters
	xlApp.ReferenceStyle = 2
	Set xlSheet = xlApp.Workbooks(1).Worksheets(1)
	xlSheet.Name = "Lotus Export" 
	rows = 1
	cols = 1
     'Enter the view column headings into the spreadsheet, skipping hidden columns and columns without titles.
     'Rows stay constant at 1 while columns is incremented by 1 for each view column
	With xlSheet
		For x = 0 To uvcols                  
			If vcols(x).IsHidden = False Then
				If vcols(x).Title = "" Then
					.Cells(rows, cols).Value = "COL " + Cstr(cols)
					theTitles(cols) = "COL " + Cstr(cols)
					cols = cols + 1
				Else
					.Cells(rows, cols).Value = Trim(vcols(x).Title)
					theTitles(cols) = Ucase(Trim(vcols(x).Title))
					cols = cols + 1
				End If
			End If                        
		Next     
		maxcols = cols - 1
		Redim trackCols(maxCols)
            'Export Categories to excel worksheet
		x=0
		Dim doOnce As Boolean
		doOnce = True
		Set nav = view.CreateViewNav
		Set entry = nav.GetFirst            
		rows = 2
		cols = 1
		Print "Exporting category documents to Excel..."
		While Not entry Is Nothing
			If entry.isCategory Then      
				colEntries = entry.ColumnValues
				Forall ce In colEntries
					If ce <> "" Then
						If doOnce Then
							trackcols(x) =      Typename ( ce )                                          
						End If                                    
						.Cells(rows, cols).Value = ce                              
					Else
						.Cells(rows, cols).Value = ""
					End If                                          
                              'Always increment to be consistent with loop for col titles                                    
					cols = cols + 1      
					x = x +1                              
				End Forall
				doOnce = False
				rows = rows + 1
				cols = 1
			End If
			Set entry = nav.GetNextCategory(entry)
		Wend    
	End With      
	Set doc = Nothing
	Set view = Nothing
	On Error Goto ExcelFormatError
	Print "Formatting Excel Spreadsheet..."
	Call formatSpreadsheet
ExitHere:      
     'Release Excel app and workbook if there is an error, otherwise memory will get clogged with versions of Excel running
	If Not Continue Then 
		xlApp.Visible = False
		xlApp.Workbooks(1).close(False)            
		xlApp.quit                  
	Else
		If Not xlapp Is Nothing Then
			xlApp.Visible = True 'Show Excel in the UI when done processing
		End If
	End If
	Set xlApp = Nothing
	Set xlSheet = Nothing      
	Exit Sub
ErrorHandler:
	Msgbox  "Error #" & Format$(Err) & " at line " & Format$(Erl) & ": " & Error$, 0, "Error Processing Excel Export"     
	Continue = False
	If Not xlapp Is Nothing Then               
           'Release Excel app and workbook if there is an error, otherwise memory will get clogged with versions of Excel running
		xlApp.Workbooks(1).close(False)      
		xlapp.quit
		Set xlApp = Nothing
		Set xlSheet = Nothing
	End If      
	Exit Sub     
ExcelFormatError:
	Msgbox  "There was a general excel export error: " + Error$ + "-" + Str(Err), 0, "Error Processing Excel Export"  
	Continue = False
	If Not xlapp Is Nothing Then               
          'Release Excel app and workbook if there is an error, otherwise memory will get clogged with versions of Excel running
		xlApp.Workbooks(1).close(False)      
		xlapp.quit
		Set xlApp = Nothing
		Set xlSheet = Nothing
	End If      
	Exit Sub     
End Sub
Sub Terminate
End Sub
Sub FormatSpreadsheet
      'Format the appearance of the Excel spreadsheet:
     'Select all cells and format
      'General Cell Format
	xlApp.Range(xlSheet.Cells(1,1), xlsheet.Cells(rows+2,maxcols)).Select
	xlApp.Selection.Font.Name = "Arial"
	xlApp.Selection.Font.Size = 8
	xlApp.Selection.Font.Color = cRGBBlack
	xlApp.Selection.Columns.AutoFit
	xlApp.Selection.RowHeight = 13
      'Top Row
	xlApp.Range(xlSheet.Cells(1,1), xlSheet.Cells(1, maxcols)).Select      
	xlApp.Selection.Font.Bold = True
	xlApp.Selection.Font.Name = "Arial"
	xlApp.Selection.Font.Size = 9
	xlApp.Selection.Font.Color = cRGBBlack
	xlApp.Selection.RowHeight = 15
	xlApp.Selection.Interior.color = cRGBGrey
	With xlApp.Selection.Borders(xlEdgeBottom)
		.color =  cRGBBlack            
		.LineStyle = xlContinuous
		.Weight = xlThin
	End With
	With xlApp.Selection.Borders(xlEdgeBottom)            
		.LineStyle = xlContinuous
		.Weight = xlThin
		.color =  cRGBBlack            
	End With
	With xlApp.Selection.Borders(xlEdgeTop)            
		.LineStyle = xlContinuous
		.Weight = xlThick
		.color =  cRGBBlack            
	End With
      'format the NUMERIC Columns using the TrackCols array
	Dim thisFormat As String
	For x = Lbound(theTitles) To Ubound(theTitles)
		thisFormat = ""
		Select Case Ucase(TrackCols(x))
		Case "NUMBER","DOUBLE","LONG","INTEGER"
			thisFormat = fmtNumber
		Case "CURRENCY"
			thisFormat = fmtCurrency
		Case Else
                  'do nothing                  
		End Select
		If thisFormat <>"" Then
			xlApp.Range(xlSheet.Cells(2, x+1), xlsheet.Cells(rows, x+1)).NumberFormat = thisFormat
		End If
	Next      
	'Commenting out - don't want overall totals
	'Dim sumString As String
      'Format the column numbers      
	'For x = Lbound(theTitles) To Ubound(theTitles)
	'	thisFormat = ""
	'	sumString = {R} + Cstr(2) + {C} + Cstr(x+1) + ":" + {R} + Cstr(rows-1) + {C} + Cstr(x+1)
	'	Select Case Ucase(trackCols(x))
	'	Case "NUMBER","DOUBLE","LONG","INTEGER","CURRENCY"
	'		xlApp.Cells(rows,x+1).formula={=SUM(} + sumString + {)}
	'		xlApp.Range(xlSheet.Cells(2,x+1), xlsheet.Cells(rows,x+1)).NumberFormat = fmtNumber
	'	Case Else
     '             'do nothing
	'	End Select            
	'Next
	With xlApp.Worksheets(1).PageSetup
		.Orientation = 2            
		If excelTitle= "" Then
			.CenterHeader =viewname + " Report - Summary"
		Else
			.CenterHeader = excelTitle + " - Summary"
		End If
		.RightFooter =  "&R Page &P of &N" 
		.LeftFooter = Format(Now(),"mm/dd/yyyy hh:mm AM/PM")
		If excelCompany = "" Then
			.CenterFooter = "Export from Lotus Notes by" + session.commonusername
		Else
			.CenterFooter = "&B" + excelCompany + "&B"
		End If
            '.PrintArea =myRange
		.PrintTitleRows = xlApp.Rows(1).Address
		.LeftMargin = xlApp.InchesToPoints(0.35)
		.RightMargin = xlApp.InchesToPoints(0.35)
		.TopMargin = xlApp.InchesToPoints(1)
		.BottomMargin = xlApp.InchesToPoints(1)
		.HeaderMargin = xlApp.InchesToPoints(0.5)
		.FooterMargin = xlApp.InchesToPoints(0.5)            
	End With
	Stop
        'Set the Excel row and col heading back to the AI Reference Style where the cols are lettered and the rows are numbered
	xlApp.ReferenceStyle = 1      
	xlApp.Range("B:B").Select  
	With xlapp.selection
		.NumberFormat = "[$-F800]dddd, mmmm dd, yyyy"
		.Font.Name = "Arial"
		.Font.Size = 8
		.Font.FontStyle = "Bold"
		.Font.Color = cRGBMedBlue 
	End With
End Sub
Function MakeRGB(Red As Integer ,Green As Integer, Blue As Integer) As Long 
	MakeRGB = (((&H100& * Green) Or Red) Or (&H10000 * Blue)) 
End Function

Open in new window

code.txt
Avatar of mbonaci
mbonaci
Flag of Croatia image

It would've helped if you added "line" in the ExcelFormatError error handler, like this:

... + Str(Err) & " at line " & Format$(Erl)

Cuz' currently we can (without examining the code in detail) establish that the error originated in Sub FormatSpreadsheet.

Of course, "Subscript out of range" error usually means that you tried to access an element of an array that was outside its bounds (either greater than its ubound or smaller than its lbound).

I don't feel like drilling into the code without any "meat" behind.

Why don't you debug the code? That would've been my first move...

If that's not an option, add error line part to error handler and run it again and then report back the findings.
Avatar of Sjef Bosman
I'd verify lines 226-228:

      For x = Lbound(theTitles) To Ubound(theTitles)
            thisFormat = ""
            Select Case Ucase(TrackCols(x))

and insert a test right before line 228, like
      If x>maxcols Then ALARM!!

But indeed, some indication as to the line where the error occurs would be nice.
Avatar of AliciaVee
AliciaVee

ASKER

Hi -- I did try to debug the code -- activated the debugger -- but the error came up and nothing happened...which I thought was strange.  I will try to add the additional error msgs and see what happens. Thanks.
Okay -- the error message now says "at line 111"
Thoughts?
What is your line 111, inside your code? Line 111 in the code above couldn't have produced the error... unlikely in any case.
okay -- I "think" the line is the following: (looking in the lower right corner of the agent properities pane, shows the 111 number

Call formatSpreadsheet

which is the last line, right before the "Exit Here" statement:
Print "Exporting category documents to Excel..."
            While Not entry Is Nothing
                  If entry.isCategory Then      
                        colEntries = entry.ColumnValues
                        Forall ce In colEntries
                              If ce <> "" Then
                                    If doOnce Then
                                          trackcols(x) =      Typename ( ce )                                          
                                    End If                                    
                                    .Cells(rows, cols).Value = ce                              
                              Else
                                    .Cells(rows, cols).Value = ""
                              End If                                          
                              'Always increment to be consistent with loop for col titles                                    
                              cols = cols + 1      
                              x = x +1                              
                        End Forall
                        doOnce = False
                        rows = rows + 1
                        cols = 1
                  End If
                  Set entry = nav.GetNextCategory(entry)
            Wend    
      End With      
      Set doc = Nothing
      Set view = Nothing
      On Error Goto ExcelFormatError
      Print "Formatting Excel Spreadsheet..."
      Call formatSpreadsheet
ASKER CERTIFIED SOLUTION
Avatar of Sjef Bosman
Sjef Bosman
Flag of France image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
That just means that the error originated in the FormatSpreadsheet Sub, which doesn't have error handling.

But didn't I say in my first post that "the error originated in Sub FormatSpreadsheet".
So, as I also mentioned in my first post, if you place some kind of error handling in that Sub, you'll get the exact line.
Experts,

I appologize for not coming back to this question -- holidays hit, then I traveled for a week for business, now I am back.

I'm not the greatest debugger or error troubleshooting -- so I am open to having any script that will export my view, that is categorized, using totals -- into Excel.  I would think this would be as commen as an excel export script -- which I do have, but does not do categories.  

Can anyone help?
You could have fooled me! You may not be the greatest (that was Mr M. Ali), but I know you are good enough to work this one out, especially with all the help above. Do give it a try...
The solution wont fall from sky, start working on it and ask specific questions when you get stuck.

I know the code probably looks frightening to you, but when you get into it and concentrate on a single line at the time (that's what a debugger is used for), you can get the whole picture in the end.
Start line by line and for each line that you don't understand, ask here and we'll help...
Thanks all!  Yes -- the code is super scary -- soooo long!

Okay -- I did as sjef suggested, and the wierd thing is that in the first "summary" view (that is categorized) that I tested it in, the Excel report worked!  I know it did not work before in that view.  So, I thought -- what?  Could it be fixed!!  Not.  I then tried it in another categorized (summary) view and got the follow error:
there was a general excel export error.  subscript out of range -9

and then another error right after:
there was a general excel export error.  object variable not set -91

So, looks like that second error msg grabbed something new.  line 91 is the first "end if" in the following code:
While Not entry Is Nothing
If entry.isCategory Then      
colEntries = entry.ColumnValues
Forall ce In colEntries
If ce <> "" Then
If doOnce Then
trackcols(x) =      Typename ( ce )                                          
End If                                    
.Cells(rows, cols).Value = ce                              
Else
.Cells(rows, cols).Value = ""
End If


Update:  
I only have two summary views (categorized) that I wanted to export to Excel, as mentioned in my previous response, since adding the error checking, one of my two views now exports.  One doesn't. I decided to create another categorized view to export.  it works perfectly.  I am now thinking there is something different about the one view that will not export.  And, in looking at the difficult view, the first column is a date colum, converted to a year and there are many records with a blank in this field that will need to be updated.  I'm wondering if that field is populated, if the view will export?  Does this make any sesne?
Update (again) No -- my thoughts were not correct.  I adjusted the view to exclude all records that did not have a date in the first column that is being categorized -- which did not affect the export -- still does not work on that one view.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ah...now we are cooking with gas!  Below error appeared:

There was a general excel export error.  subscript out of range -9 in line 40

and then when I click "ok" the following error appears:
there was a general excel export error.  object variable not set -91

At line 40, the following code is stated:
      numdocs = view.allentries.count  

I'm still very confused on why it is only 1 of the 3 views I have as summary (categoirzed) that will not export?  Of course, I'm no expert.  :)


Makes me assume that you spelled the view's name incorrectly... But on the other hand, you have an explicit test on view Is Nothing, which excludes a non-existent view. Hmmm...

The view could be corrupt of course, but let's not assume that. Can you check again in your code that it's really line 40, the one with allentries.count? In your current code I mean? For it used to be line 80 or so.
Okay -- I will admit -- I am an idiot!!

I was in the wrong sub -- the code (line 40) was in the Initialize -- not in the FormatSpreadsheet sub...

Good catch sjef!  So, below is line 40 in the FormatSpreadsheet sub -- I've commented where line 40 is -->Select Case Ucase(TrackCols(x))

Dim thisFormat As String
      For x = Lbound(theTitles) To Ubound(theTitles)
            thisFormat = ""
            Select Case Ucase(TrackCols(x))  <---THIS IS LINE 40
            Case "NUMBER","DOUBLE","LONG","INTEGER"
                  thisFormat = fmtNumber
            Case "CURRENCY"
                  thisFormat = fmtCurrency
            Case Else
                  'do nothing                  
            End Select
            If thisFormat <>"" Then
                  xlApp.Range(xlSheet.Cells(2, x+1), xlsheet.Cells(rows, x+1)).NumberFormat = thisFormat
            End If
      Next      
Aaaah, keep the gas a-flowing!

The error says: well, you want TrackCols(x) but x is either too high or too low (out of range). Suggestion:
      Print Ubound(TrackCols), x
when the error occurs.

There must be something wrong with the logic, I suppose x gets higher than maxCols. For you to find out.
Experts -- I am going to remove this agent from the database and just go with exporting non-categorized views...not a big deal and is simply a nice to have.  I can always have folks use the copy as table and paste into Excel if needed.

As always -- I appreciate all of the help and have learned more on how to debug lotusscript in the process -- so thanks!

Happy holidays!!
Experts -- I am going to remove this agent from the database and just go with exporting non-categorized views...not a big deal and is simply a nice to have.  I can always have folks use the copy as table and paste into Excel if needed.

As always -- I appreciate all of the help and have learned more on how to debug lotusscript in the process -- so thanks!

Happy holidays!!