hello friends, I have written one agent to extract report from one view to excel.my problem is that when i am running this agent an error “OLE Automation object Error” is coming.this view has 264 collums and after debugging program is stopped at 256’th collumn value.plz suggest the solution as soon as possible
Subject: lotus
Excel doesn’t support taht many columns.
You’ll need to use something else to store the data, or find a different way to lay the data out.
Subject: please write a more meaningfull subject - and you will get easier help
Subject: Try this agent to retrieve data from excelsheet
Sub Initialize Dim session As New NotesSession
Dim db As NotesDatabase
Dim doc As NotesDocument
Dim view As NotesView
Dim row As Integer
Dim written As Integer
Set db = session.CurrentDatabase
Set doc = New NotesDocument(db)
Set db=session.currentdatabase
Set doc=session.documentcontext
doc.Form = "ImportForm"
Path = doc.Path_of_Excel_Sheet(0)
Msgbox(path)
Dim xlFilename As String
'// This is the path and name of the Excel file that will be imported
xlFilename = Path
'// Next we connect to Excel and open the file. Then start pulling over the records.
Dim Excel As Variant
Dim xlWorkbook As Variant
Dim xlSheet As Variant
Print "Connecting to Excel..."
Set Excel = CreateObject( "Excel.Application" )
Excel.Visible = False '// Don't display the Excel window
Print "Opening " & xlFilename & "..."
Excel.Workbooks.Open xlFilename '// Open the Excel file
Set xlWorkbook = Excel.ActiveWorkbook
Set xlSheet = xlWorkbook.ActiveSheet
'// Cycle through the rows of the Excel file, pulling the data over to Notes
Goto Records
Print "Disconnecting from Excel..."
xlWorkbook.Close False '// Close the Excel file without saving (we made no changes)
Excel.Quit '// Close Excel
Set Excel = Nothing '// Free the memory that we'd used
Print " " '// Clear the status line
Records:
row = 0 '// These integers intialize to zero anyway
written = 0
Print "Starting import from Excel file..."
Do While True '// We use a test a few lines down to exit the loop
Finish:
With xlSheet
row = row + 1
Set view= db.GetView("VImport_File") '// Name of View
Set doc = db.CreateDocument '// Create a new doc
doc.Form = "ImportForm"
doc.OrderNumber = .Cells( row, 1 ).Value
doc.OrderDate = .Cells(row, 2 ).Value
doc.Status = .Cells(row, 3).Value
doc.Urgency= .Cells( row, 4 ).Value
doc.JobTitle = .Cells(row, 5).Value
doc.Client = .Cells( row, 6).Value
doc.Location = .Cells( row, 7 ).Value
doc.TR = .Cells( row, 8 ).Value
doc.Notes= .Cells( row, 9 ).Value
Call doc.Save( True, True ) '// Save the new doc
written = written + 1
'If doc.OrderNumber(0) = "" Then
' Goto Done
'Else
’ End If
End With
Print written & " records imported...", doc.LastName(0)
Loop
Return
'Done:
End Sub