Excel VBA - Working with notesembeddedobjects

HCL Domino Domino Forum
1

Hi,

Trying to import data from Lotus Notes into an Excel spreadsheet. The problem is that the data is stored in an attachment in Notes. Successful with getting notesmbeddedobject, but not sure what to do next with it to create Excel workbook object so that I can access the data. See below:

Macro in Excel:

Dim Session as Object

Dim db As Object

Dim dc As Object

Dim excelObj As Object

'Dim doc As Object

'Create the session.

Set Session = CreateObject(“Notes.NotesSession”)

'Create a handle to lotus notes database using the session created above.

Set db = Session.GetDatabase(“[serverName]”, “[database filename]”)

'Create a handle to the document we need

Set dc = db.AllDocuments

Set doc = dc.GetFirstDocument

While Not (doc Is Nothing)

Set docStatus = doc.getFirstItem("docStatus")

Set docSubject = doc.getFirstItem("Subject")

If docStatus.Text = "Completed" Then

   Set excelObj = doc.getAttachment("[attachment filename]")  

End If

Set doc = dc.GetNextDocument(doc)

Wend

End Sub

2

Subject: Excel VBA - Working with notesembeddedobjects

You could extract the file using (extractfile)into a specified path and point your excel object to the excel file.Then you could access the cells of excel.Call object.extractfile("C:" & object.name)

fn="C:" & object.name

Set Excel = CreateObject( “Excel.Application” )

Excel.Visible = False '// Don’t display the Excel window

Print "Opening " & xlFilename & “…”

Excel.Workbooks.Open fn

Set xlWorkbook = Excel.ActiveWorkbook

Set xlSheet = xlWorkbook.ActiveSheet

Now you can access the cells

print xlSheet.Cells(1, 1).Value

3

Subject: RE: Excel VBA - Working with notesembeddedobjects

Thanks. Was hoping to avoid writting to disk, but this may be my only option.