Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

VBA code to export large number of custom contacts

Posted on 2003-10-26
8
Medium Priority
?
405 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article will help to fix the below errors for MS Exchange Server 2013 I. Certificate error "name on the security certificate is invalid or does not match the name of the site" II. Out of Office not working III. Make Internal URLs and Externa…
If you troubleshoot Outlook for clients, you may want to know a bit more about the OST file before doing your next job. IMAP can cause a lot of drama if removed in the accounts without backing up.
Many of my clients call in with monstrous Gmail overloading issues with Outlook. A quick tip is to turn off the All Mail and Important folders from synching. Here is a quick video I made to show you how to turn off these and other folders in Gmail s…
CodeTwo Sync for iCloud (http://www.codetwo.com/sync-for-icloud?sts=6554) automatically synchronizes your Outlook 2016, 2013, 2010 or 2007 folders with iCloud folders available via iCloud Control Panel. This lets you automatically sync them with…

618 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