Excel VBA - Working with Lotus Notes notesembeddedobjects

[aww91]

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]") <---
Stuck here!!!
End If

Set doc = dc.GetNextDocument(doc)
Wend

End Sub

 

[Guest]

I don't have Lotus Notes, but is there an option/method to save the
attachment and then you could just open it in Excel.

 

[aww91]

Thanks for your prompt reply. Yes, in Lotus Notes you can use the
ExtractFile method to save the attachment and then re-open it.
Solution is a little inefficient and I wanted to avoid it if at all
possible - but I may not have a choice.

Thanks again,

 

[NickHK]

No idea about Lotus Notes, but do you have to use late binding ?
If you can set a reference in Excel, then you do not have to use the generic
"Object" variable, but can use those exposed by the library.
You will also get Intellisense to help you with the available methods,
properties etc.
Also the Object browser will list those available.

NickHK

 

[urs meli]

Maybe you have to save the attachment first, before you can open it?
like

if excelOBJ.type = 1454 then
call excelObj.ExtractFile(filepath)
end if

EMBED_ATTACHMENT = 1454
And afterwards you could load the Workbook like any normal xls file.

getAttachement returns a NotesEmbeddedObject Object, I don't think it
can directly be opened in Excel.

If docStatus.Text = "Completed" Then
Set excelObj = doc.getAttachment("[attachment filename]") <---
Stuck here!!!

No idea about Lotus Notes, but do you have to use late binding ?
If you can set a reference in Excel, then you do not have to use the generic
"Object" variable, but can use those exposed by the library.
You will also get Intellisense to help you with the available methods,
properties etc.
Also the Object browser will list those available.

NickHK

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]") <---
Stuck here!!!
End If

Set doc = dc.GetNextDocument(doc)
Wend

End Sub