Exporting data from Lotus Notes databases

5,203

Here's a free tool I wrote years ago for exporting data to Excel: Export to Excel 2.0

Also, I wrote some code to extract attachments from email, and this code could be adapted to work on other databases

(Declarations)
Dim sDir As String
Dim s As NotesSession
Dim w As NotesUIWorkspace
Dim db As NotesDatabase
Dim dc As NotesDocumentCollection
Dim doc As NotesDocument

Sub Initialize

 Set s = New NotesSession
 Set w = New NotesUIWorkspace 
 Set db = s.CurrentDatabase
 Set dc = db.UnprocessedDocuments
 Set doc = dc.GetFirstDocument
 Dim rtItem As NotesRichTextItem
 Dim RTNames List As String
 Dim DOCNames List As String
 Dim itemCount As Integer
 Dim sDefaultFolder As String
 Dim x As Integer
 Dim vtDir As Variant
 Dim iCount As Integer
 Dim j As Integer
 Dim lngExportedCount As Long
 Dim attachmentObject As Variant

 x = Msgbox("This action will extract all
 attachments from the " & Cstr
(dc.Count) & _
 " document(s) you have selected, 
and place them into the folder of your 
choice." & _
 Chr(10) & Chr(10) & "Would you like 
to continue?", 32 + 4, "Export 
Attachments")
 If x <> 6 Then Exit Sub 

 sDefaultFolder = s.GetEnvironmentString
("LPP_ExportAttachments_DefaultFolder")
 If sDefaultFolder = "" Then sDefaultFolder = "F:"
 vtDir = w.SaveFileDialog( False, "Export 
attachments to which folder?", "All 
files|*.*", sDefaultFolder, "Choose 
Folder and Click Save")
 If Isempty(vtDir) Then Exit Sub
 sDir = Strleftback(vtDir(0), "\")
 Call s.SetEnvironmentVar
("LPP_ExportAttachments_DefaultFolder", sDir) 

 While Not (doc Is Nothing)

  iCount = 0
  itemCount = 0
  lngExportedCount = 0
  Erase RTNames
  Erase DocNames

  'Scan all items in document
  Forall i In doc.Items

   If i.Type = RICHTEXT Then
    Set rtItem = doc.GetfirstItem(i.Name)
    If Not Isempty(rtItem.EmbeddedObjects) 
Then
     RTNames(itemCount) = Cstr(i.Name)
     itemCount = itemCount +1
    End If
   End If

  End Forall  

  For j = 0 To itemCount-1 
   Set rtItem = Nothing
   Set rtItem = doc.GetfirstItem(RTNames(j))
   Forall Obj In rtItem.EmbeddedObjects
    If ( Obj.Type = EMBED_ATTACHMENT ) 
Then
     Call ExportAttachment(Obj)
     Call Obj.Remove
     Call doc.Save( False, True ) 
 'creates conflict doc if conflict exists
    End If 
   End Forall 
  Next

  'Scan all items in document
  Forall i In doc.Items

   If i.Type = ATTACHMENT Then

    DOCNames(lngExportedCount) = 
i.Values(0)
    lngExportedCount = lngExportedCount + 1

   End If

  End Forall

  For j% = 0 To lngExportedCount-1 
   Set attachmentObject = Nothing
   Set attachmentObject = 
doc.GetAttachment(DOCNames(j%))
   Call ExportAttachment(attachmentObject)   
   Call attachmentObject.Remove   
   Call doc.Save( False, True ) 
'creates conflict doc if conflict exists
  Next  

  Set doc = dc.GetNextDocument(doc)
 Wend

 Msgbox "Export Complete.", 16, "Finished"

End Sub

Sub ExportAttachment(o As Variant)

 Dim sAttachmentName As String
 Dim sNum As String
 Dim sTemp As String

 sAttachmentName = sDir 
& "\" & o.Source
 While Not (Dir$(sAttachmentName, 0) = "")
  sNum = Right
(Strleftback(sAttachmentName, "."), 2)
  If Isnumeric(sNum) Then
   sTemp = Strleftback(sAttachmentName, ".")
   sTemp = Left(sTemp, Len(sTemp) - 2)
   sAttachmentName = sTemp
 & Format$(Cint(sNum) + 1, "##00") & _
   "." & Strrightback(sAttachmentName, ".")
  Else
   sAttachmentName = 
Strleftback(sAttachmentName, ".") & _
   "01." & Strrightback(sAttachmentName, ".")
  End If
 Wend

 Print "Exporting " & sAttachmentName
 'Save the file
 Call o.ExtractFile( sAttachmentName ) 

End Sub
Share:
5,203

Related videos on Youtube

Jak
Author by

Jak

Updated on September 17, 2022

Comments

  • Jak
    Jak 3 months

    I'm looking for a tool, hopefully low cost or free, to help me export Lotus Notes databases (not Lotus Notes mail files!) to any kind of format. CSV/Excel etc..

    I also need to be able to extract attachments in the database. They're all Word documents.

    I've found one called DetachIt - but it's high license cost puts it quite far out of reach.

    Thank you!

    • tony roth
      tony roth over 12 years
      I think MS has some tools to do this look up lotus notes transporter suite and they are free. Also if you open a database within notes it has export options.
    • tony roth
      tony roth over 12 years
      also there is odbc connector for lotus notes!
  • Jak
    Jak over 12 years
    I'm very unfamilar with LotusScript - usually a C# programmer. Will this remove the attachments? I simply want to copy them.. I'm a little concerned by the Obj.Remove line - but I honestly don't know what it's actually doing.
  • Ken Pespisa
    Ken Pespisa over 12 years
    It does, but you can comment out the two lines calling the Remove method, and the Save method that follows them, and then the code will simply extract without removing the attachments.