Solved

VBA code to export large number of custom contacts

Posted on 2003-10-26
8
388 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

Check out this infographic on what you need to make a good email signature that will work perfectly for your organization.
Are you unable to connect or configure Hotmail email account in Microsoft Outlook 2010, 2007? Or Outlook.com emails are not downloading to Outlook? Lets’ see the problem and resolve Outlook Connector error syncing folder hierarchy (0x8004102A).
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 …
To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…

706 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now