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.
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
code.txt
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.
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.
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.
ASKER
Okay -- the error message now says "at line 111"
Thoughts?
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.
ASKER
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
ASKER
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?
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...
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...
ASKER
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
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
ASKER
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?
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?
ASKER
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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. :)
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.
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.
ASKER
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
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","
thisFormat = fmtNumber
Case "CURRENCY"
thisFormat = fmtCurrency
Case Else
'do nothing
End Select
If thisFormat <>"" Then
xlApp.Range(xlSheet.Cells(
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.
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.
ASKER
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!!
As always -- I appreciate all of the help and have learned more on how to debug lotusscript in the process -- so thanks!
Happy holidays!!
ASKER
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!!
As always -- I appreciate all of the help and have learned more on how to debug lotusscript in the process -- so thanks!
Happy holidays!!
... + 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.