Solved

Export to Excel -- Summary agent

Posted on 2010-11-23
21
1,271 Views
Last Modified: 2013-12-18
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
0
Comment
Question by:AliciaVee
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 11
  • 6
  • 4
21 Comments
 
LVL 22

Expert Comment

by:mbonaci
ID: 34203338
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.
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 34205012
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.
0
 

Author Comment

by:AliciaVee
ID: 34208304
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.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:AliciaVee
ID: 34208590
Okay -- the error message now says "at line 111"
Thoughts?
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 34209200
What is your line 111, inside your code? Line 111 in the code above couldn't have produced the error... unlikely in any case.
0
 

Author Comment

by:AliciaVee
ID: 34209428
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
0
 
LVL 46

Accepted Solution

by:
Sjef Bosman earned 250 total points
ID: 34209521
Ah, right... That explains a lot.

What you should do is this:

- after the line with Sub FormatSpreadsheet, add yet another error trap:

      On Error Goto ExcelFormatError

- and right before the End Sub:

      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    

This way we'll see the line INSIDE the called function with the error.
0
 
LVL 22

Expert Comment

by:mbonaci
ID: 34211473
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.
0
 

Author Comment

by:AliciaVee
ID: 34312865
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?
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 34315302
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...
0
 
LVL 22

Expert Comment

by:mbonaci
ID: 34316916
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...
0
 

Author Comment

by:AliciaVee
ID: 34331746
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


0
 

Author Comment

by:AliciaVee
ID: 34331768
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?
0
 

Author Comment

by:AliciaVee
ID: 34331775
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.
0
 
LVL 22

Assisted Solution

by:mbonaci
mbonaci earned 250 total points
ID: 34332809
That is not the line, that's the error code (Err returns error code):

there was a general excel export error.  object variable not set -91

Msgbox  "There was a general excel export error: " + Error$ + "-" + Str(Err), 0, "Error Processing Excel Export"  

Add Erl to get the line, like this:

Msgbox  "There was a general excel export error: " + Error$ + " - " + Str(Err) + " in line " + Str(Erl), 0, "Error Processing Excel Export"  

Then run the export and we'll know more...
0
 

Author Comment

by:AliciaVee
ID: 34335237
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.  :)


0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 34335710
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.
0
 

Author Comment

by:AliciaVee
ID: 34337794
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      
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 34338668
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.
0
 

Author Comment

by:AliciaVee
ID: 34371782
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!!
0
 

Author Closing Comment

by:AliciaVee
ID: 34372161
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!!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Capture Serial Data in Lotus Notes Database 8 273
Lotus notes view not working using Trim 14 214
Username format for Domino LDAP 3 110
@Mailsend 3 80
You’ve got a lotus Domino web server, and you have been told that “leverage browser caching” is a must do. This means that we have to tell the browser everywhere in the web to use cache. In other words, we set (and send) an expiration date in the HT…
Article by: Rob
Notes 8.5 Archiving Steps and Tips This article covers setting up a Notes archive, and helps understand some of the menu choices making setting up and maintaining a Notes archive file easier.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question