From Lotus Notes to Excel

[CalvinGraham]

Long time reader, first time poster; great website, finally decided to register.

I'm wanting to set up a macro to download excel spreadsheets from LotusNotes (not the other way round as most people are interested in). I have a message on a shared database where the latest update of an xls file can be accessed by people. There's dozens of versions of the file going back numerous months/years and it would be good to analyse the changes over time

I could potentially just go and save them all to a new folder someplace but it would be much more efficient to get a Macro that could do that for me, particularly as I could then set it to work with other similar files.

 

[procod]

There few ways to do this. And it may depend on structure of database and document.
You can try this if you know DB filename and document id (last element of Notes:// path in document properties):

On Error Resume Next
Const LOTUSDB = "db\databasename.nsf"
Const DOCID = "12B34567C89B01D2C3456789012C3456"

Set s = CreateObject("Notes.NotesSession")
Server = s.GetEnvironmentString("MailServer", True)
Set LnDb = s.GetDatabase(Server, LOTUSDB)
Set doc = LnDb.GetDocumentByUNID(DOCID)
For Each notesItem In doc.Items
  If notesItem.type = 1 Then
      If IsArray(notesItem.EmbeddedObjects) Then
         For Each oFile in notesItem.EmbeddedObjects
            if LCase(Right(oFile.Source,4)) = ".xls" Then
               oFile.extractFile "c:\" & oFile.Source
            End if
         Next
      End If
  End If
Next

 

[CalvinGraham]

Thanks, although I'm getting a problem for the line "Set doc = LnDb.GetDocumentByUNID(DOCID)"

Run-time error '7063':
Database _ _ _ _ _ has not been opened yet.

I've declared the variables as objects apart from 'Server' which I've set as a string. In the locals window is seems to successfully be getting 'Server' set to something correct looking although 's' and 'LnDb' just expand to 'No Variables Set' <NO Variables>. I have Notes open, although the same error occurs when it's not.

 

[procod]

Looks like database was not opened. Did you checked database file name ? It could be found in properties of DB at workspace.
If this path is correct:
Const LOTUSDB = "db\databasename.nsf"
Then here you should get an object:
Set LnDb = s.GetDatabase(Server, LOTUSDB)

 

[CalvinGraham]

Well I tried changing the server variable to the string given when I get the properties of the database on my workspace; this time it went one line further before the 'Object variable or With block variable not set' error got flagged instead. I also tried adding a line with a LnDb.IsOpen() call which confirmed that the database is indeed opening now.

Presumably at this point it's just that the file itself won't load, ie it can't find one of "each" of the NotesItems in a non-existant document that hasn't loaded? The DocID I'm sending is the Identifier from when I right-click-properties on the document, or at least the last 32 characters (everything after the "/" )

I'll also add this link for anyone else who searches for this thread, it's a useful list of the methods of the Notes class:
http://www-12.lotus.com/ldd/doc/lot...1f82ab14864680138525642e007687cb?OpenDocument

 

[procod]

Use Watch window in Debug mode to check every object variable at each step. Check path to Database twice. It must be equal to Filename property at first tab of DB properties. I using this method to import/export files in LN.

 

[CalvinGraham]

Almost there, I found that NotesItem and oFile need to be defined as variants; previously they were objects. Anyway, it gets to the innermost part of the loop and then doesn't do anything because, I think, the attachments are embedded in the file. Some searching online seems to imply that ExtractFile returns an error for that situation so I should use the GetAttachment() method and save them to the drive instead.

 

[procod]

Well, I just checked it in this way (I didn't declare any variable at all):

Set attachment = doc.GetAttachment("1.xls")
attachment.extractFile "c:\1.xls"

and it works too.
But you need to know file name in this case.

 

[CalvinGraham]

Turns out it was my mistake earlier in the program where i defined the save to location, I missed out the "\" between the folder and the filename; there were dozens of files one folder up from where I was looking all along!

I always have Option Explicit because the program flags up undefined variables which are usually just mispelled variables you've used. "Colour" vs "Color" is an easy one to mix up in a large program if your background is British English. Evidentally here it was a pain though. Everything works now though, thanks so much.