PDA

View Full Version : [SOLVED] Formula to extract Lotus Notes username



Glaswegian
02-19-2010, 06:00 AM
Hi

I'm looking at e-mailing from Excel using Lotus Notes (I know, but I have to work with what I've got) and have one problem.

The code for Notes includes a line to parse the username to find the Notes Database name. However, the username does not appear correctly. The original line of code is

username = session.username
MailDbName = Left$(username, 1) & Right$(username, (Len(username) - InStr(1, username, " "))) & ".nsf"

Stepping through the code shows that the username starts as

CN=John Smith/OU=AB/O=XYZ_EUROPE

The actual username within Notes should be

John Smith/AB/XYZ_EUROPE

and this is what I need to identify the user database.

I'm not much good at formulae so I'd be grateful if anyone could suggest the changes required to end up with the correct username.

Thanks.

Bob Phillips
02-19-2010, 06:21 AM
Try


=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"CN=",""),"OU=",""),"O=","")

Glaswegian
02-19-2010, 07:04 AM
Hi xld

Thanks.

When I put your formula in the code I get a "Function not defined" message.

MailDbName = Substitute(Substitute(Substitute(username, "CN=", ""), "OU=", ""), "O=", "") & ".nsf"

No doubt something I've done or not done...

Bob Phillips
02-19-2010, 07:58 AM
When you said formula, I presumed that you meant Excel.

In VBA, it would look like



MailDbName = Replace(Replace(Replace(username, "CN=", ""), "OU=", ""), "O=", "")

Glaswegian
02-19-2010, 08:06 AM
Hi

Apologies for not being clearer - that works great. Many thanks (as always).

Bob Phillips
02-19-2010, 08:46 AM
Hi

Apologies for not being clearer - that works great. Many thanks (as always).

I wasn't criticising Iain, just getting my excuses in for why I gave you a formula not VBA :)

Glaswegian
02-19-2010, 09:11 AM
Lol - no worries (I didn't think you were being critical anyway...)