Solved

Export to Excel -- Summary agent

Posted on 2010-11-23
21
1,252 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
  • 11
  • 6
  • 4
21 Comments
 
LVL 22

Expert Comment

by:mbonaci
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Author Comment

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

Expert Comment

by:Sjef Bosman
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 22

Expert Comment

by:mbonaci
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

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…
IBM Notes offer Encryption feature using which the user can secure its NSF emails or entire database easily. In this section we will discuss about the process to Encrypt Incoming and Outgoing Mails in depth.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

744 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now