Getting Common Name Using Lotusscript

HCL Domino Domino Forum
1

I currently have code that gets certain values from a notes document and exports them to an Excel Spreadsheet. There are three fields where the value is in this format: CN=FirstName LastName/OU=XXX/OU=XXX/O=XXXXX

What I’m trying to get is:

FirstName LastName

This is not an Author Field and it’s not getting the user name.

This is the agent that I’m using:

Sub Initialize

Dim s As New NotesSession

Dim db As NotesDatabase

Dim view As NotesView

Dim collection As NotesDocumentCollection

Dim doc As NotesDocument



Set s = New NotesSession

Set db = s.CurrentDatabase



Set view = db.GetView ("ExportView")

Set collection = db.Alldocuments



Dim RequestType As Variant

Dim DataStatus As Variant



Dim PL1 As String

Dim PL2 As String

Dim PL3 As String



Dim ReqNum As Variant

Dim DataReqNum As Variant

Dim TotalHours As Variant



Dim xlApp As Variant

Dim xlSheet As Variant

Dim xlCell As Variant

Dim xlRow As Variant

Dim strUserFile As String

Dim row As Long

row = 1	





Set xlApp = CreateObject ("Excel.Application")

xlApp.Workbooks.Add

Set xlSheet = xlApp.Workbooks(1).Worksheets(1)

'Set xlSheet = xlApp.ActiveWorkbook.Worksheets(1)

xlApp.Visible = True





xlSheet.Cells(row, 1).Value = "Data Status"

xlSheet.Cells(row, 2).Value = "Request type"

xlSheet.Cells(row, 3).Value = "PL1"

xlSheet.Cells(row, 4).Value = "PL2"

xlSheet.Cells(row, 5).Value = "PL3"

xlSheet.Cells(row, 6).Value = "Request #"

xlSheet.Cells(row, 7).Value = "Data Request #"

xlSheet.Cells(row, 8).Value = "Total Hours"

xlSheet.Cells(row, 9).Value = "Comments"

row = row + 1





Set doc = view.GetFirstDocument





While Not (doc Is Nothing)

	

	RequestType = doc.GetItemValue( "RequestType" )(0)

	DataStatus = doc.GetItemValue( "DataStatus" )(0)

	PL1 = doc.GetItemValue( "PL1")(0)

	PL2 = doc.GetItemValue( "PL2")(0)

	PL3 = doc.GetItemValue( "PL3")(0)

	ReqNum = doc.GetItemValue( "ReqNum" )(0)

	DataReqNum = doc.GetItemValue( "DataReqNum" )(0)

	TotalHours = doc.GetItemValue( "TotalHours" )(0)



	xlSheet.Cells(row, 1).Value = (DataStatus)

	xlSheet.Cells(row, 2).Value = (RequestType)

	xlSheet.Cells(row, 3).Value = (PL1)

	xlSheet.Cells(row, 4).Value = (PL2)

	xlSheet.Cells(row, 5).Value = (PL3)

	xlSheet.Cells(row, 6).Value = (ReqNum)

	xlSheet.Cells(row, 7).Value = (DataReqNum)

	xlSheet.Cells(row, 8).Value = (TotalHours)

	row = row + 1

	

	Set doc = view.GetNextDocument(doc)

Wend



xlSheet.Columns(1).Columnwidth = 22

xlSheet.Columns(2).Columnwidth = 18

xlSheet.Columns(3).Columnwidth = 35

xlSheet.Columns(4).Columnwidth = 35

xlSheet.Columns(5).Columnwidth = 35

xlSheet.Columns(6).Columnwidth = 18

xlSheet.Columns(7).Columnwidth = 14

xlSheet.Columns(8).Columnwidth = 12

xlSheet.Columns(9).Columnwidth = 55



xlSheet.Columns(9).Wraptext = True



Set xlCell = xlSheet.Range ("A1:I1")

xlCell.Font.Bold = 2

End Sub

I’ve tried using this line below as well as other ways of getting the value and then changing it to the common name, but I am not having any luck. Does anyone know how I should code this to get the desired result?

PL1 = Evaluate (doc.GetItemValue(| @Name([cn];PL1)(0) | ))

2

Subject: Use the NotesName class

Don’t use Evaluate when you have a perfectly fine native functionality in the NotesName class…

Dim username as NotesName

longusername = “CN=FirstName LastName/OU=XXX/O=YYY”

Set username = New NotesName( longusername )

MsgBox username.Common

3

Subject: The Result When I Try This

This is what I have (PL1 is the name of the field that contains the value I’m getting):

Dim PL1Name As String

Dim PL1Nam As NotesName

PL1Name = doc.GetItemValue( “PL1”)(0)

Set PL1Nam = New NotesName (PL1Name)

xlSheet.Cells(row, 3).Value = PL1Nam

I get a type mismatch on the last line.

4

Subject: That Worked

My bad. I left something out. It works just fine. I appreciate your help!

5

Subject: Post the solution

Why not post the solution, so other can benefit from it as well? Don’t just take, give back to the community if you get help.

I bet your code was:

xlSheet.Cells(row, 3).Value = PL1Nam.Common