jforget1
asked on
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
What am I missing here?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
Thanks. Next time, accept only or give preference to the real answer to your problem (EE rules somewhere...).
ASKER
Sorry, I took ideas from each and I just wanted to let all know that I appreciate the input on the fix.
Fine with me!
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.
- sort the view you use in Notes, so you don't have to sort in Excel
- output a .csv file, it's just text...