Solved

VBA code to export large number of custom contacts

Posted on 2003-10-26
8
392 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Suggested Solutions

If you don't know how to downgrade, my instructions below should be helpful.
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…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

735 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