Hi all,Need some urgent help plzzzzzz…
I have written a code export the field values to the excel sheet and detach the attachments of the document to a separate folder. The code is mentioned below:
Sub Initialize
On Error Goto errHandler
Dim luiws As New NotesUIWorkspace
Dim luiview As NotesUIView
Set luiview = luiws.CurrentView
Dim lodc As NotesDocumentCollection
Dim lodoc As NotesDocument
Dim rtitem As Variant
Dim rtitem1 As NotesRichTextItem
Dim outt As String
Set lodc = luiview.Documents
Dim foldername As String
' Describe the excel object
Dim xlApp As Variant
Dim xlsheet As Variant
Dim row As Integer
Dim col As Integer
Set xlApp = CreateObject("Excel.application")
If Isempty(xlApp) Then
Msgbox "Excel Not installed"
Else
xlApp.Visible = True
xlApp.Workbooks.Add
Set xlsheet = xlApp.Workbooks(1).Worksheets(1)
Print "Data Export to Excel - Processing"
End If
row = 1
col = 1
Dim client As String
Dim result As Variant
Dim reviewdate As String
Dim summary As String
Dim temp As Variant
Dim NotesMacro As String
Dim filepath As String
' Take the loop to export all the documents to excel and separate the file attachments
Set lodoc = lodc.GetFirstDocument
While Not lodoc Is Nothing
client = lodoc.PanelClient(0)
reviewdate = lodoc.PanelReviewDate(0)
summary = lodoc.PanelSummaryText(0)
xlsheet.cells(row,col).value = client
xlsheet.cells(row,col+1).value = reviewdate
xlsheet.cells(row,col+2).value = summary
If lodoc.HasItem("PanelAttachments") Then
Set rtitem = lodoc.GetFirstItem("PanelAttachments")
End If
If rtitem.GetUnformattedText <> "" Then
xlsheet.cells(row,col+3).value = rtitem.GetUnformattedText
End If
' Code begins to extract the file attachment
Set rtitem1 = lodoc.GetFirstItem("PanelAttachments")
temp = rtitem1.EmbeddedObjects
foldername = lodoc.PanelClient(0) + lodoc.PanelReviewDate(0)
If Instr ( foldername , "/" ) Then
NotesMacro = {@ReplaceSubstring("} + foldername + {"; "/" ;} + { "_" )}
result = Evaluate(NotesMacro)
End If
If Not Isempty(temp) Then
Forall o In rtitem1.EmbeddedObjects
file_Name$ = o.Source
filepath = "P:\SydneyReports\"+ result(0)
If Dir$(filepath,16) = "" Then
Mkdir filepath
End If
Call o.ExtractFile( Cstr(filepath$ + "\" + file_Name$ ))
End Forall
End If
' Code ends
Set lodoc = lodc.GetNextDocument(lodoc)
row = row +1
Wend
Xlapp.columns("D").Select
XlApp.Selection.Columns.ColumnWidth = 160
Xlapp.columns("A:D").Select
XlApp.Selection.Columns.AutoFit
Exit Sub
errHandler:
Msgbox "The error is " &Error()& "at line no. " &Erl()
End Sub
Each time I am running the code then I am getting the “OLE: Automation Object error” on the line:
xlsheet.cells(row,col+3).value = rtitem.GetUnformattedText
There is a lot of text which is expoted to this cell.
Please suggest how to get rid of this error.
Thanks in advance.