Lotus Notes Database - Get text from field which includes attachment

[GaryStone10]

Hi all
I've got a problem that seems like it should be simple, but I've been unsuccessful in searching this site and googling. I'm pulling all data from a Lotus Notes datbase and I've run into no trouble (except for finding out the maximum character limit per cell in excel...), but I have an issue with one field. Basically the field consists of text and attachments, and I only want to pull out the text. trying to do this the normal way (cells(x,y)=field.value) gives me an error. Annoyingly, I can pull out the attachment, but can't seem to get at the text.

Any suggestions would be very much appreciated as this is driving me mad.
Thanks in advance.
Gary Stone.

 

[GWilliams88]

Hello,

I am curious to know what you used to pull the data out of the database? Were you using VBA through excel to push the data into the spreadsheets? I am currently in the process of doing so, however, I am having difficulty pulling out the attachments. What does your code look like and what are you using to query the database?

Thanks,
Garrett

 

[GaryStone10]

Hello,

I am curious to know what you used to pull the data out of the database? Were you using VBA through excel to push the data into the spreadsheets? I am currently in the process of doing so, however, I am having difficulty pulling out the attachments. What does your code look like and what are you using to query the database?

Thanks,
Garrett

Hi

I did eventually manage this, but with a really rubbish way of reading each record out. I'll paste my code and then try and remember what I was doing.

Sub Get_View()
 
'This was a quick bit of code to get a list of views in a database
Set onotes = CreateObject("Notes.NotesSession")
 
'Using the address of the server and the name of the database 
Set Db = onotes.GetDatabase(Cells(1, 2).Value, Cells(1, 4).Value)

Application.StatusBar = "Connecting to " & Db.Title & "..."
If Cells(1, 6) = "" Then
UserForm1.ComboBox1.Clear
 
'Add each view to a combobox then show the combobox and move to the next stage
For Each v In Db.Views
    UserForm1.ComboBox1.AddItem v.Name, UserForm1.ComboBox1.ListCount
Next v
UserForm1.Show
Exit Sub
End If
Get_Headers
End Sub

Sub Get_Headers()
'Now we can pull out the list of columns in the view
Dim x As Long
Dim y As Long
Dim a As Long
Dim b As Long
Set onotes = CreateObject("Notes.NotesSession")
Set Db = onotes.GetDatabase(Cells(1, 2).Value, Cells(1, 4).Value)
Application.StatusBar = "Connecting to " & Db.Title & "..."
 
'Get the view from the sheet or the combobox
If Cells(1, 6).Value = "" Then
Set View = Db.GetView(UserForm1.ComboBox1.Text)
Else
Set View = Db.GetView(Cells(1, 6).Value)
End If
'get the first document in the view and read off the name of each header
'adding them to a new combobox
Set doc = View.GetFirstDocument
    For Each i In doc.Items
    b = 0
'If I remember rightly, there were some system names that appeared in every view
    If (i.Name <> "$FILE" And i.Name <> "Body") Then
        If UserForm2.List1.ListCount = 0 Then
            UserForm2.List1.AddItem i.Name, 0
        Else
            For y = 0 To UserForm2.List1.ListCount - 1
                If UserForm2.List1.List(y) = i.Name Then
                    b = 1
                Else
                    b = b
                End If
            Next y
                If b = 0 Then
                UserForm2.List1.AddItem i.Name, UserForm2.List1.ListCount
                End If
        End If
    End If
    Next i

UserForm2.Show
End Sub

Sub Get_Data()
Dim x As Long
Dim y As Long
Dim a As Long
Dim b As Long
Dim c As Long
Set onotes = CreateObject("Notes.NotesSession")
 
'Connect to the database using the server name and database name
Set Db = onotes.GetDatabase(Cells(1, 2).Value, Cells(1, 4).Value)
Application.StatusBar = "Connecting to " & Db.Title & "..."
 
'connect to the view
If Cells(1, 6).Value = "" Then
Set View = Db.GetView(UserForm1.ComboBox1.Text)
Else
Set View = Db.GetView(Cells(1, 6).Value)
End If
 
'put headers on sheet
For c = 0 To UserForm2.List2.ListCount - 1
    Cells(2, c + 1) = UserForm2.List2.List(c)
Next c
 
'For each document, go through the headers and place the field on the sheet
For x = 1 To View.ENTRYCOUNT
    Set doc = View.GetNthDocument(x)
    For Each i In doc.Items
        For a = 1 To c + 1
        If i.Name = Cells(2, a) Then
'Can't remember why I did the next bit, trying to identify dates I guess
'Something to note here that was annoying, Lotus notes database fields have a much higher character limit to Excel cells.
            If i.Type = 1024 And Len(i.Text) < 20 Then
            Cells(x + 2, a).Value = CDate(i.Text)
            Else
            Cells(x + 2, a).Value = i.Text
            End If
            Exit For
        End If
        Next a
        Application.StatusBar = "Reading Record " & x & " out of " & View.ENTRYCOUNT
    Next i
              
'Now we can pull out the files      
If UserForm2.CheckBox1.Enabled = True Then
'If the document has a file attached
If doc.HasEmbedded Then
'Create a folder based on a location given on the sheet
Dim fso
Dim fol As String
fol = Cells(1, 8) & "\" & Cells(x + 2, 90).Value
Set fso = CreateObject("Scripting.FileSystemObject")
If Not fso.FolderExists(fol) Then
    fso.CreateFolder (fol)
End If
'On the Lotus Notes setup I used the files were stored in a field called 'Body'. Ca't confirm this is universal though.
    Set rtitem = doc.GetFirstItem("Body")
        For Each o In rtitem.EmbeddedObjects
'Extract the file, cutting the name down if it is too large
            If Len(o.Name) > 100 Then
                o.ExtractFile (Cells(1, 8) & "\" & Cells(x + 2, 90).Value & "\" & Left(o.Name, 100) & Right(o.Name, 4))
            Else
                o.ExtractFile (Cells(1, 8) & "\" & Cells(x + 2, 90).Value & "\" & o.Name)
            End If
        Next o
    End If
Else
End If
Next x

Application.StatusBar = False
Exit Sub
End Sub

And that's that. I have no way of knowing if it would work outside of the specific company I was working for at the time, and I believe I stumbled across a better way of doing it, but never had the need to create a new report.

I should note, this code requires the references
'Lotus Domino Objects'
'Lotus Notes Automation Classes'