0

i am trying to write a code to open VBA and do search based on the cell value in A1 (integer). i managed to write a code up to point where i can open the lotus notes and go to specific database. I tried many online codes but couldn't manage to find the code to search in that database. "Lotus.NotesSession" doesn't work the excel version i use. Could you please help me to finish this code. Code is below:

Sub macro4()

Dim uiWs As Object
Dim dbname As String
Dim serverName As String
Dim db As NotesDatabase
Dim doccol As NotesDocumentCollection
Dim varA As Integer



dbname = "***"

serverName = "***"


Set uiWs = CreateObject("Notes.NotesUIWorkSpace")

Call uiWs.OpenDatabase(serverName, dbname) 

Set db = uiWs.GetDatabase(serverName, dbname) ---->where i get the error

varA.Value = Sheets("sheet1").Range("A1").Value

Set doccol = db.FTSearch(varA, Nothing, 0)

End Sub
Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
RALF
  • 37
  • 7
  • Your fulltextsearch db.ftsearch() requires different parameters, see https://www.ibm.com/support/knowledgecenter/en/SSVRGU_9.0.1/basic/H_FTSEARCH_METHOD_DB.html . The first should be a string. – umeli Jan 28 '19 at 07:59

2 Answers2

2

In Notes there are two "parent"- classes to derive everything from. The NotesUIWorkspace is the class for the "frontend": It contains everything that you SEE in the client. The NotesSession is the class for the backend. NotesDatabase is a backend- class. To correctly get your database, you need to use NotesSession:

Set ses = CreateObject("Notes.NotesSession")
Set db = ses.GetDatabase(serverName, dbname)

You mixed up COM and OLE Integration. The thing you tried to use (Lotus.NotesSession) is for COM only and you need to include Notes in your project to use this.

For your example to work you need to use the OLE integration: Notes.NotesSession

Now to your "Search"- Code:

There are two different ways to search a NotesDatabase:

There is the Fulltextsearch and the "normal" search.

The Fulltextsearch just searches for your value everywhere in all documents and returns a collection. A search for "Tom" in a mailfile will find all mails / calendar entries that where:

  • sent by Tom
  • received by Tom
  • have the word "Tom" in subject or body or an attachment of the mail.

The syntax for FTSearch is: 

Set doccol = db.FTSearch( YourSearchValue )

You can restrict the search to one certain field by using a special syntax for your search. e.G. to only search in the "From" field you could write 

[From] = "YourSearchValue"

In FTSearch the "=" always means "contains"

The normal search uses a Formula (in @Formula- syntax) to search for a document. It needs the right syntax, otherwise it will not find anything. A formula to search all documents that come from "Tom" would be: 

@Contains( From ; "Tom" )

The syntax for search is: 

Set doccol = db.Search( YourQueryAsExampleAbove, Nothing, 0 )

With Nothing = Cutoffdate (if given only return documents created or modified after the date) and 0 = max. number of documents to return (0 = return everything).

So your example code for the could be something like:

strQuery = "FieldToSearch = " & Sheets("sheet1").Range("A1").Value
Set doccol = db.Search( strQuery, Nothing, 0 )
Tode
  • 11,795
  • 18
  • 34
  • Thanks for your reply, i really appreciated. it is very detailed and useful. i tried your codes but still having "type mismatch error" on the line Set db = uiWs.GetDatabase(serverName, dbname)---->Type Mismatch Error – RALF Jan 28 '19 at 16:25
  • i think i am front end user, when i use NotesUIWorkspace and call i can go to straight to application/form where i want to make a search but, the with the codes i am using seems i can't do search. – RALF Jan 28 '19 at 16:28
  • Just read the [documentation](https://www.ibm.com/support/knowledgecenter/en/SSVRGU_9.0.1/basic/H_NOTESUIWORKSPACE_CLASS.html) about NotesUIWorkspace: there is NO getDatabase in there, and OpenDatabase does not return a NotesDatabase Object that you need for your search... – Tode Jan 28 '19 at 23:10
  • And one more thing: there is no „remote control“ for the search bar in an open database... you need to do the search in the backend and then somehow present the results to the user... but this is not en easy task, even for experienced Notes Developers... if your result is a single document, then you could use uiws.EditDocument to directly open it for the user... – Tode Jan 28 '19 at 23:12
  • Thanks for the info, basically spent days to write a code and not successful. i gave up, thanks anyway. – RALF Jan 29 '19 at 05:56
0

After calling OpenDatabase successfully, you can use 

set uiDb = uiWS.CurrentDatabase

That will get a NotesUIDatabase object, and then you can use 

set db= uiDb.Database

That will get you the NotesDatabase object that you need in order to call the FTSearch method.

Richard Schwartz
  • 14,463
  • 2
  • 23
  • 41
  • Thank you, i did what you said and initially it gave me Type mismatch error when i declare uidb variable as notesuidatabase, when i changed object it code didn't give me any error but also didn't do anything, so i gave up. Thanks for the comment. – RALF Jan 29 '19 at 05:58
  • `Sub macro5() Dim uiWs As Object Dim dbname As String Dim serverName As String Dim db As Object Dim doccol As Object Dim strquery As String Dim uidb As Object serverName = "***" dbname = "***" Set uiWs = CreateObject("Notes.NotesUIWorkSpace") Call uiWs.OpenDatabase(serverName, dbname) Set uidb = uiWs.CurrentDatabase Set db = uidb.DATABASE strquery = "FieldToSearch = " & Sheets("test").Range("A2").Value Set doccol = db.FTSearch(strquery, 0) End Sub` – RALF Jan 29 '19 at 06:26
  • What do you do after you call FTSearch? That's a back-end method. It won't show you any results. You have to write code to loop through the collection and get the data. – Richard Schwartz Jan 30 '19 at 04:53
  • But in any case, I have no idea why it gave you the type mismatch when you declared it as NotesUIDatabase. If you run in the debugger, what do you see as the type of the uidb object after the OpenDatabase call? – Richard Schwartz Jan 30 '19 at 04:55
  • Hi Richard, basically the database i open with the call function is a log. you can search by ID (where i want to put on cell "A2" . After the search it only shows that person and when double click on the log entry you can see information about that person. So i want to create a hyperlink for each person and when clicked automatically opens lotus notes, goes to that database (which i managed till that point) and search & founds that ID number. Rest of it is not that important. But with my knowledge seems it is not possible. I believe if i show you the log maybe you will have more idea? – RALF Jan 30 '19 at 21:47
  • Hmmm... If the OpenDatabase call worked, the CurrentDatabase should certainly not return a Nothing value. Is your call to OpenDatabase actually causing the Notes client to show the open database? – Richard Schwartz Feb 01 '19 at 14:21
  • Hi Richard. Yes, after the call Opendatabase, Lotus notes directly goes to Log entry and i can see the document, maybe the problem is cannot bring back-end search to show on front-end?.But i can find all of the log entries on mail.box file and i can only open and see them if i export mail.box file on lotus notes as a "structured" and when i open this file on excel, the text format is readable, but when i open mali.box file without exporting( directly from lotus notes directory) the text format is all shapes not readable. is there any code to convert mail.box without exporting? – RALF Feb 01 '19 at 20:07
  • Then, i dont need to have a code to open lotus notes and do search, i can find exactly same things if i can open mail.box file on excel (of course readable version) and use "match" function to find what i need. Any help on this anyone? – RALF Feb 01 '19 at 20:13
  • FYI.. StackOverflow isn't really designed to use comment threads for extended discussions like this, and they discourage it. Now that you're talking about mail.box I really cannot follow what it is that you are trying to do. But you are correct that the back-end search will not show its results in the front end. The FTSearch returns a NotesDocumentCollection, and you can write code to iterate through that collection and you can access data values from the dos in that collection and could use your code to display that data in Excel or a in MsgBox or in a VBA Form. – Richard Schwartz Feb 02 '19 at 04:45