Lotus Notes: Export attachments to excel sheet

Question

I want to export the attchment to excel sheet along with the other data. I am able to do the normal export. Also I extracted the attachment from the document to the file location. Now what it needs to be done is to attach it to the excel cell.

Sub Click(Source As Button)
    Dim ws As New NotesUIWorkspace
    Dim uiView As NotesUIView
    Dim doc As NotesDocument
    Dim docCol As NotesDocumentCollection

    Set uiView = ws.CurrentView
    Set docCol = uiView.Documents

    Set doc = docCol.GetFirstDocument
    Dim xlApp As Variant
    Dim xlsheet As Variant
    Dim rtitem As Variant
    Dim Ol As Variant
    maxcols= 2
    Set xlApp = CreateObject("Excel.Application")
    xlApp.StatusBar = "Creating WorkSheet. Please be patient..."
    xlApp.Visible = True
    xlApp.Workbooks.Add
    xlApp.ReferenceStyle = 2
    rows = 1
    cols = 1
    Set xlsheet = xlApp.Workbooks(1).Worksheets(1)

    xlsheet.Cells(rows,1).Value = "Created By"
    xlsheet.Cells(rows,2).Value = "File/Attachment"
    cols=1
    rows=2
    While Not doc Is Nothing

        xlsheet.Cells(rows,1).Value = doc.CreatedBy(0)
        Set rtitem = doc.GetFirstItem("FileUpload")
        If ( rtitem.Type = RICHTEXT ) Then
            Forall o In rtitem.EmbeddedObjects
                If ( o.Type = EMBED_ATTACHMENT ) Then
                    Call o.ExtractFile ( "d:\temp\" & Cstr(doc.FileName(0)) )
                End If
            End Forall
            xlsheet.Cells(rows,2).select

        '   xlsheet.Cells(rows,2).OLEObjects.Add Cstr(doc.FileName(0)),  "d:\temp\" & Cstr(doc.FileName(0)), , True, , , Cstr(doc.FileName(0))      
            'Set Ol = xlApp.OLEObjects.Add(Cstr(doc.FileName(0)),  "d:\temp\" & Cstr(doc.FileName(0)), True, False)
            xlsheet.OLEObjects.Add( "", "d:\temp\" & Cstr(doc.FileName(0)), False, False).Select


        End If

        Set doc = docCol.GetNextDocument(doc)
        rows=rows+1
        cols=1
    Wend
    xlApp.Rows("1:1").Select
    xlApp.Selection.Font.Bold = True
    xlApp.Selection.Font.Underline = True
    xlApp.Range(xlsheet.Cells(1,1), xlsheet.Cells(rows,maxcols)).Select
    xlApp.Selection.Font.Name = "Arial"
    xlApp.Selection.Font.Size = 8
    xlApp.Selection.Columns.AutoFit
    With xlApp.Worksheets(1)
        .PageSetup.Orientation = 2
        .PageSetup.centerheader = "Report - Confidential"
        .Pagesetup.RightFooter = "Page &P" & Chr$(13) & "Date: &D"
        .Pagesetup.CenterFooter = ""
    End With
    xlApp.ReferenceStyle = 1
    xlApp.Range("A1").Select
    xlApp.StatusBar = "Importing Data from Lotus Notes Application was Completed."
End Sub

Please share your views.

Regards, Himanshu

Andrew Magerman Andrew Magerman · Accepted Answer · 2013-08-22T13:49:53

Since in case of errors,it's very difficult to determine what is the problem, I generally try to program as much as possible within the Excel sheet using VBA. Once it works in VBA, I port the code to Excel.

I would recommend doing this here - this is not really a Lotus Notes question, but a VBA one.

Lotus Notes: Export attachments to excel sheet

2 Answers