• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 5158
  • Last Modified:

OLE Automation Object Error

Have an export to excel that is giving me an export I have never seen. I modified the code below from  an export I have used many times. When I run the export it gets to record 221 and then it gives me an OLE Automation Object Error. This export has more columns than I have used in the past, 63 columns. Could that be causing the error. Not sure where to look here.
Sub Click(Source As Button)
	
	Dim s As New NotesSession
	Dim ws As New NotesUIWorkspace
	Dim uidoc As NotesUIDocument
	Dim db As NotesDatabase
	Dim v As NotesView
	' Dim doc As NotesDocument
	Dim colVals As Variant 
	Dim Mgrdc As NotesDocumentcollection
	Dim MgrDoc As notesDocument
	Dim cur As NotesDocument  
	Dim Repdc As NotesDocumentCollection     
	Dim RepDoc As NotesDocument
	Dim RepDoc2 As NotesDocument
	Dim mgrview As NotesView     
	Dim RepView As NotesView
	Dim RepView2 As NotesView
	Dim txt_Agency As String  
	Dim tmp_NotesName As NotesName
	Dim MgrKey, RepKey, ADKey As String
	Dim RepKey2 As String
	Dim xlApp As Variant
	Dim xlSheet As Variant
	Dim i As Integer
	Dim x As Integer
	
	Set db = s.CurrentDatabase
	Set v = db.GetView( "(bpq_all)" )
	
	Set RepDoc = v.GetFirstDocument
	
	
	Print "Creating Excel Workbook..."
	Set xlApp = CreateObject("Excel.application")
	Print "Creating Excel Worksheet for EXP Export.."
	xlApp.Workbooks.Add
	Set xlSheet = xlApp.Workbooks(1).Worksheets(1)
	
	XlApp.Columns("A:CC").Select 
	With XlApp.Selection 
		.WrapText=1
		.HorizontalAlignment = xlCenter
	End With 
	
	xlSheet.Columns(1).Columnwidth = 10
	xlSheet.Columns(2).Columnwidth = 10
	xlSheet.Columns(3).Columnwidth = 9
	xlSheet.Columns(4).Columnwidth = 9
	xlSheet.Columns(5).Columnwidth = 15
	xlSheet.Columns(6).Columnwidth = 11
	xlSheet.Columns(7).Columnwidth = 20
	xlSheet.Columns(8).Columnwidth = 9
	xlSheet.Columns(9).Columnwidth = 50
	xlSheet.Columns(10).Columnwidth = 25
	xlSheet.Columns(11).Columnwidth = 20
	xlSheet.Columns(12).Columnwidth = 20
	xlSheet.Columns(13).Columnwidth = 20
	xlSheet.Columns(14).Columnwidth = 20
	xlSheet.Columns(15).Columnwidth = 20
	xlSheet.Columns(16).Columnwidth = 20
	xlSheet.Columns(17).Columnwidth = 20
	xlSheet.Columns(18).Columnwidth = 20
	xlSheet.Columns(19).Columnwidth = 20
	xlSheet.Columns(20).Columnwidth = 20
	xlSheet.Columns(21).Columnwidth = 20
	xlSheet.Columns(22).Columnwidth = 20
	xlSheet.Columns(23).Columnwidth = 20
	xlSheet.Columns(24).Columnwidth = 20
	xlSheet.Columns(25).Columnwidth = 20
	xlSheet.Columns(26).Columnwidth = 20
	xlSheet.Columns(27).Columnwidth = 20
	xlSheet.Columns(28).Columnwidth = 20
	xlSheet.Columns(29).Columnwidth = 20
	xlSheet.Columns(30).Columnwidth = 20
	xlSheet.Columns(31).Columnwidth = 20
	xlSheet.Columns(32).Columnwidth = 20
	xlSheet.Columns(33).Columnwidth = 20
	xlSheet.Columns(34).Columnwidth = 20
	xlSheet.Columns(35).Columnwidth = 20
	xlSheet.Columns(36).Columnwidth = 20
	xlSheet.Columns(37).Columnwidth = 20
	xlSheet.Columns(38).Columnwidth = 20
	xlSheet.Columns(39).Columnwidth = 20
	xlSheet.Columns(40).Columnwidth = 20
	xlSheet.Columns(41).Columnwidth = 20
	xlSheet.Columns(42).Columnwidth = 20
	xlSheet.Columns(43).Columnwidth = 20
	xlSheet.Columns(44).Columnwidth = 20
	xlSheet.Columns(45).Columnwidth = 20
	xlSheet.Columns(46).Columnwidth = 20
	xlSheet.Columns(47).Columnwidth = 20
	xlSheet.Columns(48).Columnwidth = 20
	xlSheet.Columns(49).Columnwidth = 20
	xlSheet.Columns(50).Columnwidth = 20
	xlSheet.Columns(51).Columnwidth = 20
	xlSheet.Columns(52).Columnwidth = 20
	xlSheet.Columns(53).Columnwidth = 20
	xlSheet.Columns(54).Columnwidth = 20
	xlSheet.Columns(55).Columnwidth = 20
	xlSheet.Columns(56).Columnwidth = 20
	xlSheet.Columns(57).Columnwidth = 20
	xlSheet.Columns(58).Columnwidth = 20
	xlSheet.Columns(59).Columnwidth = 20
	xlSheet.Columns(60).Columnwidth = 20
	xlSheet.Columns(61).Columnwidth = 20
	xlSheet.Columns(62).Columnwidth = 20
	xlSheet.Columns(63).Columnwidth = 20
	
	xlSheet.Cells(1,1).Value = "EXP First Name"
	xlSheet.Cells(1,2).Value = "EXP Last Name"
	xlSheet.Cells(1,3).Value = "Agency"
	xlSheet.Cells(1,4).Value = "Status"
	xlSheet.Cells(1,5).Value = "Start Date"
	xlSheet.Cells(1,6).Value = "Summit Effective Date"
	xlSheet.Cells(1,7).Value = "EXP Spoc"
	xlSheet.Cells(1,8).Value = "EXP Status"
	xlSheet.Cells(1,9).Value = "EXP Date"
	xlSheet.Cells(1,10).Value = "BD Spoc"
	xlSheet.Cells(1,11).Value = "BD Status"
	xlSheet.Cells(1,12).Value = "BD Date"
	xlSheet.Cells(1,13).Value = "EGA Spoc"
	xlSheet.Cells(1,14).Value = "EGA Status"
	xlSheet.Cells(1,15).Value = "EGA Date"
	xlSheet.Cells(1,16).Value = "Prod Spoc"
	xlSheet.Cells(1,17).Value = "Prod Status"
	xlSheet.Cells(1,18).Value = "Prod Date"
	xlSheet.Cells(1,19).Value = "Seg Serv Spoc"
	xlSheet.Cells(1,20).Value = "Seg Serv Status"
	xlSheet.Cells(1,21).Value = "Seg Serv Date"
	xlSheet.Cells(1,22).Value = "Comp Spoc"
	xlSheet.Cells(1,23).Value = "Comp Status"
	xlSheet.Cells(1,24).Value = "Comp Date"
	xlSheet.Cells(1,25).Value = "Legal Spoc"
	xlSheet.Cells(1,26).Value = "Legal Status"
	xlSheet.Cells(1,27).Value = "Legal Date"
	xlSheet.Cells(1,28).Value = "Pre Hire Spoc"
	xlSheet.Cells(1,29).Value = "Pre Hire Status"
	xlSheet.Cells(1,30).Value = "Pre Hire Date"
	xlSheet.Cells(1,31).Value = "NEF Spoc"
	xlSheet.Cells(1,32).Value = "NEF Status"
	xlSheet.Cells(1,33).Value = "NEF Date"
	xlSheet.Cells(1,34).Value = "Lic U4 Spoc"
	xlSheet.Cells(1,35).Value = "Lic U4 Status"
	xlSheet.Cells(1,36).Value = "Lic U4 Date"
	xlSheet.Cells(1,37).Value = "Lic Spoc"
	xlSheet.Cells(1,38).Value = "Lic Status"
	xlSheet.Cells(1,39).Value = "Lic Date"
	xlSheet.Cells(1,40).Value = "DBA Spoc"
	xlSheet.Cells(1,41).Value = "DBA Status"
	xlSheet.Cells(1,42).Value = "DBA Date"
	xlSheet.Cells(1,43).Value = "DBA Leg Spoc"
	xlSheet.Cells(1,44).Value = "DBA Leg Status"
	xlSheet.Cells(1,45).Value = "DBA Leg Date"
	xlSheet.Cells(1,46).Value = "SMRU Spoc"
	xlSheet.Cells(1,47).Value = "SMRU Status"
	xlSheet.Cells(1,48).Value = "SMRU Date"
	xlSheet.Cells(1,49).Value = "IT Spoc"
	xlSheet.Cells(1,50).Value = "IT Status"
	xlSheet.Cells(1,51).Value = "IT Date"
	xlSheet.Cells(1,52).Value = "FP Spoc"
	xlSheet.Cells(1,53).Value = "FP Status"
	xlSheet.Cells(1,54).Value = "FP Date"
	xlSheet.Cells(1,55).Value = "Adv Mkt Spoc"
	xlSheet.Cells(1,56).Value = "Adv Mkt Status"
	xlSheet.Cells(1,57).Value = "Adv Mkt Date"
	xlSheet.Cells(1,58).Value = "Inst Spoc"
	xlSheet.Cells(1,59).Value = "Inst Status"
	xlSheet.Cells(1,60).Value = "Inst Date"
	xlSheet.Cells(1,61).Value = "A&H Spoc"
	xlSheet.Cells(1,62).Value = "A&H Status"
	xlSheet.Cells(1,63).Value = "A&H Date"
	
	RecNum = 0
	
	While Not RepDoc Is Nothing
		
		x = RecNum + 3 ' Start at 3-rd row '
		
		xlSheet.Cells(x,1).Value = RepDoc.first_name
		xlSheet.Cells(x,2).Value = RepDoc.last_name
		xlSheet.Cells(x,3).Value = RepDoc.agency_num
		xlSheet.Cells(x,4).Value = RepDoc.status
		xlSheet.Cells(x,5).Value = RepDoc.actual_date
		xlSheet.Cells(x,6).Value = RepDoc.summit_date
		xlSheet.Cells(x,7).Value = RepDoc.exp_contact 
		xlSheet.Cells(x,8).Value = RepDoc.exp_status
		xlSheet.Cells(x,9).Value = RepDoc.exp_date
		xlSheet.Cells(x,10).Value = RepDoc.bd_contact 
		xlSheet.Cells(x,11).Value = RepDoc.bd_status
		xlSheet.Cells(x,12).Value = RepDoc.bd_date
		xlSheet.Cells(x,13).Value = RepDoc.ega_contact 
		xlSheet.Cells(x,14).Value = RepDoc.ega_status
		xlSheet.Cells(x,15).Value = RepDoc.ega_date
		xlSheet.Cells(x,16).Value = RepDoc.prod_contact 
		xlSheet.Cells(x,17).Value = RepDoc.prod_status
		xlSheet.Cells(x,18).Value = RepDoc.prod_date
		xlSheet.Cells(x,19).Value = RepDoc.ss_contact 
		xlSheet.Cells(x,20).Value = RepDoc.ss_status
		xlSheet.Cells(x,21).Value = RepDoc.ss_date
		xlSheet.Cells(x,22).Value = RepDoc.comp_contact 
		xlSheet.Cells(x,23).Value = RepDoc.comp_status
		xlSheet.Cells(x,24).Value = RepDoc.comp_date
		xlSheet.Cells(x,25).Value = RepDoc.leg_contact 
		xlSheet.Cells(x,26).Value = RepDoc.leg_status
		xlSheet.Cells(x,27).Value = RepDoc.leg_date
		xlSheet.Cells(x,28).Value = RepDoc.pre_hire_contact 
		xlSheet.Cells(x,29).Value = RepDoc.pre_hire_status
		xlSheet.Cells(x,30).Value = RepDoc.pre_hire_date
		xlSheet.Cells(x,31).Value = RepDoc.nef_contact 
		xlSheet.Cells(x,32).Value = RepDoc.nef_status
		xlSheet.Cells(x,33).Value = RepDoc.nef_date
		xlSheet.Cells(x,34).Value = RepDoc.licu4_contact 
		xlSheet.Cells(x,35).Value = RepDoc.licu4_status
		xlSheet.Cells(x,36).Value = RepDoc.licu4_date
		xlSheet.Cells(x,37).Value = RepDoc.lic_contact 
		xlSheet.Cells(x,38).Value = RepDoc.lic_status
		xlSheet.Cells(x,39).Value = RepDoc.lic_date
		xlSheet.Cells(x,40).Value = RepDoc.dba_contact 
		xlSheet.Cells(x,41).Value = RepDoc.dba_status
		xlSheet.Cells(x,42).Value = RepDoc.dba_date
		xlSheet.Cells(x,43).Value = RepDoc.dba_leg_contact 
		xlSheet.Cells(x,44).Value = RepDoc.dba_leg_status
		xlSheet.Cells(x,45).Value = RepDoc.dba_leg_date
		xlSheet.Cells(x,46).Value = RepDoc.smru_contact 
		xlSheet.Cells(x,47).Value = RepDoc.smru_status
		xlSheet.Cells(x,48).Value = RepDoc.smru_date
		xlSheet.Cells(x,49).Value = RepDoc.it_contact 
		xlSheet.Cells(x,50).Value = RepDoc.it_status
		xlSheet.Cells(x,51).Value = RepDoc.it_date
		xlSheet.Cells(x,52).Value = RepDoc.fp_contact 
		xlSheet.Cells(x,53).Value = RepDoc.fp_status
		xlSheet.Cells(x,54).Value = RepDoc.fp_date
		xlSheet.Cells(x,55).Value = RepDoc.am_contact 
		xlSheet.Cells(x,56).Value = RepDoc.am_status
		xlSheet.Cells(x,57).Value = RepDoc.am_date
		xlSheet.Cells(x,58).Value = RepDoc.sb_contact 
		xlSheet.Cells(x,59).Value = RepDoc.sb_status
		xlSheet.Cells(x,60).Value = RepDoc.sb_date
		xlSheet.Cells(x,61).Value = RepDoc.ah_contact 
		xlSheet.Cells(x,62).Value = RepDoc.ah_status
		xlSheet.Cells(x,63).Value = RepDoc.ah_date
		
		RecNum = RecNum + 1
		Print "Gathering Data.  Record Number: " + RecNum  ' optional
		Set RepDoc = v.GetNextDocument( RepDoc )
	Wend
	
	Print "Data Collection Complete."
	Set xlSheet = xlApp.Workbooks(1).Worksheets("Sheet1")
	Set range1 = xlsheet.Range("A1: AE2000") 
	Call range1.Sort(xlsheet.Columns("A"), , , , , , , 1)
	
	Msgbox "Your EXP Status Export is ready in Excel. Click OK and then open the Excel file flashing near the bottom of your screen.  ", 64, "Excel Export"
	
	xlApp.Visible = True
	xlApp.UserControl = True
	
	
End Sub

Open in new window

0
jforget1
Asked:
jforget1
3 Solutions
 
RonaldZaalCommented:
just my 2 cts, i get this when the field value to export has special char, or is very large.  What are the field values you write at record 221 ? Anything special ?
0
 
Sjef BosmanGroupware ConsultantCommented:
Why do you create the report directly in Excel? Consider the alternative:
- sort the view you use in Notes, so you don't have to sort in Excel
- output a .csv file, it's just text...
0
 
mbonaciCommented:
Here's how you can debug your Excel export:

 - change the code so that you add this line in line 39 of your code:
       xlApp.Visible = True

- then, before you open your Lotus database, turn debugger on (File > Tools > Debug LotusScript) and you'll be able to see what happens in Excel after each line of code is executed

- start the export and use StepInto button in debugger to go line by line
0
[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

 
jforget1Author Commented:
Wow this one has me baffled. I took a look at the bpq_all view that feeds this export. I took out an older form design which has the record that this fails on. Now no matter what I do when the action runs, it is pulling data from the view before I modified it. When I check the revised view for the name of the user that the excel sheet fails on is not even in that view. It seems to be running on a cached version of the view. I have done a full reboot, but it still is gathering old data which should not be in the export.

What am I missing here?
0
 
Sjef BosmanGroupware ConsultantCommented:
Quit Notes, remove Cache.NDK from the Notes data directory, and restart Notes. Then try again. If still in trouble, there might be 2 views with the same name (or alias...).
0
 
jforget1Author Commented:
Wow I feel like a jackass, there was a view bpq_all and also off the screen at the bottom of the list, (bpq_all).  That is the one I needed to edit. That did it, the export went thru perfect now. Must have been something on the old form design which it did not like.
0
 
Sjef BosmanGroupware ConsultantCommented:
Thanks. Next time, accept only or give preference to the real answer to your problem (EE rules somewhere...).
0
 
jforget1Author Commented:
Sorry, I took ideas from each and I just wanted to let all know that I appreciate the input on the fix.
0
 
Sjef BosmanGroupware ConsultantCommented:
Fine with me!
0
 
MarComGSRCommented:
I was getting a similar error for the same situation and realized there were date fields that contained erroneous dates.  For instance, 02/10/0209.  I fixed them and the error went away.
0

Featured Post

Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now