Solved

VBA code to export large number of custom contacts

Posted on 2003-10-26
8
389 Views
Last Modified: 2010-04-08
I've written code in VBA to export a whole contacts folder to Excel, including nearly all built-in Outlook fields plus 100+ user fields. It loops through each contact and then through all properties in each contact. It's slow, averaging about 15 seconds per contact, and while most of my users will have 250-500 contacts, a few will have 2000-10,000. Is this method likely to be stable doing an export with potentially 1 million pieces of info to export? More importantly, is there a faster way for that many contacts? (I know about the cut and paste method but this needs to be idiotproof). Would CDO somehow help me? I know Redemption MAPITables can pull info from contacts quickly but that info still needs to be exported cell by cell.

Thanks, Adam
0
Comment
Question by:adampirg
  • 4
  • 2
8 Comments
 
LVL 7

Expert Comment

by:_agj_
ID: 9625973
--> Is this method likely to be stable doing an export with potentially 1 million pieces of info to export?

shud be fine as long as u got DoEvents at the rite places to prevent system getting stuck.
0
 
LVL 7

Expert Comment

by:_agj_
ID: 9625998
is this a standalone app/exe that uses excel and outlook objects?
or is it an excel template that fills itself?
0
 
LVL 7

Expert Comment

by:_agj_
ID: 9626013
in outlook, in the file menu, u got an import/export option.

using the 'export to file' option under it, u can export to an excel sheet as well.

try it(i need to upgrade installation for that).
we cud automate this thing, if needed. simple vba.
0
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
LVL 7

Expert Comment

by:_agj_
ID: 9626023
what i mean is that:
one cud write code to do:
1 using the outlook object, export the contacts list to an excel sheet.
2. format this sheet if req., using the excel object.
Done.
0
 

Author Comment

by:adampirg
ID: 9627817
I have written VBA code, which I will probably put in a COM addin, to programmatically loop through each property of each contact in the folder, take the value of the property and insert it in Excel using objWS.Cells. All contacts are on custom forms, with 100+ custom fields each, so I can't use built-in export function. It will be run from a toolbar button within Excel, I think.

Where would I want DoEvents? I have it in there a couple times to call a separate Sub that updates the user on the progress of the export. Do I need it somewhere in the loop so every contact or every property does this? My core code:
        For Each objItem In objContactF.Items 'for each contact in the folder
           objSafeContact.Item = objItem  'sets Redemption SafeContactItem to this Contact
           intCol = 2 'reset to first column with each new contact
           For Each objProp In objSafeContact.ItemProperties 'reset to objSafeContact
                strValue = _
                    FormatForExcel(objSafeContact.ItemProperties.Item(objProp.Name).Value)
                objWS.Cells(intRow, intCol) = strValue
            intCol = intCol + 1
           Next 'Next property of the current contact
          intRow = intRow + 1
        Next 'Next contact in the folder
0
 

Author Comment

by:adampirg
ID: 9635201
I've read that Outlook has a memory leak when processing a large items collection with a for-next loop, and that using CDO may make it go faster or avoid this memory leak. Can you use CDO with contacts, or just with messages?
0
 
LVL 1

Accepted Solution

by:
GhostMod earned 0 total points
ID: 10600482
PAQed, with points refunded (500)

GhostMod
Community Support Moderator
0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Follow this checklist to learn more about the 15 things you should never include in an email signature from personal quotes, animated gifs and out-of-date marketing content.
Many people use more than one email account and so it becomes difficult for them to manage them when they use separate accounts,  so, in this article, I have shared an easy way to add Other Mail Accounts in your Google Inbox. It helps to combine all…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
This Experts Exchange video Micro Tutorial shows how to tell Microsoft Office that a word is NOT spelled correctly. Microsoft Office has a built-in, main dictionary that is shared by Office apps, including Excel, Outlook, PowerPoint, and Word. When …

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question