Hi
I used Collaboration Data Objects for this in a giant organisation (I am retired so cannot test it).
The code is long. Here are a few lines from the lookup function :-
Dim oGAL As AddressEntries
Dim oAddressEntry As Object
Set oGAL = oCDOmapiSession.AddressLists.Item("Global Address List").AddressEntries
oGAL.Filter.Fields.Add CdoPR_ACCOUNT, sUserID
For Each oAddressEntry In oGAL
With oAddressEntry
If .Fields(CdoPR_ACCOUNT).Value = sUserID Then
On Error Resume Next
Cells(nExcelRow, EMAILCOLUMN).Value = .Fields(&H39FE001E).Value 'Email
Cells(nExcelRow, RESULTOFFSET + 2).Value = .Fields(CdoPR_SURNAME).Value
Cells(nExcelRow, RESULTOFFSET + 3).Value = .Fields(CdoPR_GIVEN_NAME).Value
etc. . . . .
My recollection is that each lookup took about half a second.
I cannot remember why I did the IF as the oAddressEntry is a single row of data. Maybe it was to feel good.
The CdoPR_xxx items are CDO built-in constants.
By the way, the provided UserIds here were PC logon Ids and the normal case was that there was a match with the Outlook Ids. When the lookup failed I then looked at the Active Directory. This is another way of doing it which you could consider.
Regards
Derek Turner +++
>________________________________
> From: davidb <dbraithwaite@charter.net>
>To: ExcelVBA@yahoogroups.com
>Sent: Wednesday, 7 March 2012, 2:28
>Subject: [ExcelVBA] Re: searchin outlook address book
>
>
>
>Graham ---- you might be onto something here
>
>the AddressEntries property is a collection
>a collection has a key
>the key is ????
>
>I am guessing, the key is the property (e.g. "ID") that you sort the list on....
>
>So perhaps you can use the known ID to do a direct (I suspect implicit binary search) .
>
>I have not tested it ---- but it would be simple for Michael.
>
>db
>
>--- In ExcelVBA@yahoogroups.com, "Graham Pearson" <me@...> wrote:
>>
>> I agree with Michael - a binary search algorithm will be much more efficient. You should find the entry you're looking for in, at most, 16 tries.
>> However, I'm dumbfounded that Outlook.AddressEntries doesn't already have a "built-in" function for performing a binary search.
>>
>>
>> ----- Original Message -----
>> From: David Braithwaite
>> To: ExcelVBA@yahoogroups.com
>> Sent: Tuesday, March 06, 2012 11:09 AM
>> Subject: [ExcelVBA] Re: searchin outlook address book
>>
>>
>>
>> Hi Michael
>>
>> Right now you are, on average, looking through 50% (25000) names each time
>> you do a lookup.
>>
>> For each probe into the address list, you are making a networked access into
>> this list.
>>
>> I don't have the resources to test this (no access to a "global address
>> list"), but
>>
>> You might try
>>
>> Dim objAddressListEntries As Outlook.AddressEntries
>>
>> Set objAddressListEntries = objAddressList.AddressEntries
>>
>> objAddressListEntries.Sort "ID"
>>
>> then use a binary search algorithm. This would cut your average searches
>> per item from 25,000 to less than 20.
>>
>> Db
>>
>> [Non-text portions of this message have been removed]
>>
>>
>>
>>
>>
>> [Non-text portions of this message have been removed]
>>
>
>
>
>
>
[Non-text portions of this message have been removed]
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.
----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA
----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com
----------------------------------
Tidak ada komentar:
Posting Komentar