Solved

Macro to load folder names into array very slow

Posted on 2012-03-31
12
443 Views
Last Modified: 2012-06-21
Hi

I have  a macro that, using a For Each...Next loop, cycles through each of the "first level" folders beneath the "personal folder" and, if it is (a) a folder for mail and (b) is not one of the standard folders (eg deleted items, sent items etc), adds the name of the folder to a one-dimensional array.

All pretty straightforward. However, the first time I run this macro after turning my pc on, it takes 11.5 seconds to do this. There seems to be a lot of hard disk activity during this time. If I run the macro again, it only takes 0.2 seconds (even if I have closed and re-opened Outlook in the meanwhile). There are only about 40 folders it has to cycle through, of which 27 are loaded into the array.

I would really like to speed this process up for that first time after switching on the pc. Can anyone explain why it takes so long and what I might do about it? Could I have Outlook automatically do something in the background on opening such that, by the time I want to run my macro (which is likely to be several minutes up to several hours after opening Outlook), it will only take the 0.2 seconds? Or can I store the folder list somehow?

Thanks for any ideas

A
0
Comment
Question by:alpha456
  • 6
  • 5
12 Comments
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 37792182
Your macro probably is not fully recursive at certain points, slowing down at the point the code becomes non-recursive.  For recursive code, see Chris Bottomley's code at this link:
   
    http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_22995423.html
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 37792437
We could modify some code but if most of the time it works well for you perhaps the easiest is if you provide your code for us to consider ... at the very least we can load the array in 'our' code suitably.

Chris
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 37793004
OKay, i've played at length with some options, and for what it's worth this is my attempt at the requested functionality:

Sub launchpad()
Dim MyFolder As Object
Dim arr() As Variant
    
    arr = Array("")
    Set MyFolder = Application.Session.PickFolder
    Call ProcessFolder(MyFolder, arr)
    ReDim Preserve arr(LBound(arr) To UBound(arr) - 1)
 
Set MyFolder = Nothing
End Sub
 
Sub ProcessFolder(StartFolder As Object, arr As Variant)
Dim objFolder As Object
    
    intArrayElement = UBound(arr)
    If StartFolder.DefaultItemType = olMailItem And Not isDefaultFolder(StartFolder) Then
        arr(UBound(arr)) = StartFolder.FullFolderPath
        ReDim Preserve arr(LBound(arr) To UBound(arr) + 1)
    End If
        
    ' process all the subfolders of this folder
    For Each objFolder In StartFolder.Folders
        Call ProcessFolder(objFolder, arr)
    Next
 
Set mai = Nothing
Set objFolder = Nothing
Set objItem = Nothing
End Sub

Function isDefaultFolder(fldr As MAPIFolder) As Boolean
Dim oldName As String
 
        oldName = fldr.Name
        On Error Resume Next
        Err.Clear
        fldr.Name = oldName & "ish"
        If Err.Number <> 0 Then
            isDefaultFolder = True
        Else
            fldr.Name = oldName
            isDefaultFolder = False
        End If
        On Error GoTo 0
        
End Function
 

Open in new window


launchpad is the sub that launches the other code, and before it exits, arr within launchpad has the folder paths for each folder that is NOT one of the default folders - what is stored in the array is unclear ... but hopefully much the same as your code.  Note there is no property that I am aware of that implicitly tells us this, hence the function which tries to rename them ... which is not allowed and is used to detect thereby if the folder is default or not.

For info, if we tried to use the name then subfolders may use the same name and it would only work on the default PST if we tried using the get default method hence I believe the above represents a good method.

Unless/until we see your code I cannot comment on the initial slow run time ... but hopefully this is a different method and whatever causes that issue is not going to affect this approach.

Chris
0
 
LVL 2

Author Comment

by:alpha456
ID: 37793614
Thanks for your help on this.

As it happens, your code runs more slowly than mine for some reason, but more importantly, it also suffers from the same problem as mine. When run for the first time after starting my pc, it takes 55 seconds to run. When run subsequently, it takes just 25 seconds.

It seems as if it is something to do with how Outlook stores the information, rather than the exact code used to extract it.

A
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 37793794
My code may well be slow to run given the mechanism I use to establish buy;t in folders.  It is precise but error handing causes delays.  I would suspect your default folder test is perhaps less rigorous but irrespective if you have the delay of such significant first time around with both codes I would say the problem is systemic i.e. not to do with code.  I cannot see a justification for a lot of hard disk activity per se so it may be that the first time you are opening outlook that for example public folders on the network are being initialised.  Whether public or local though I can imagine first time around it taking that longer period and thereafter working ok so the issue is systemic in that it is your outlook installation at blame rather than anything in our control ... IMHO.

Whilst there are those who may be able to suggest a system change, that is not me but please be more helpful if someone should try to help, (I asked for your code to see if there was any clues we could glean from it).

Chris
0
 
LVL 2

Author Comment

by:alpha456
ID: 37795508
Chris

I did not post my code originally, because I did not believe the issue arose from a problem with my code. It seems you now share this view.

I did not post my code after you asked me to, because, having gone away for the weekend, I did not see your post with that request until after your second post, by which time it was too late. I am sorry you consider that unhelpful. While the rules of this forum do say people should respond to posts, there is no guideline on what is an acceptable delay.

Whatever is considered acceptable, I note that the gap between your first and second posts was about 6.5 hours. Depending on where in the world I was, I might well have been asleep for that entire period.

I did thank you in a previous post (which is more than I have received on occasion when trying to help people in areas I know something about), but thank you again for your time.

Antony
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 37795600
Antony

Apologies for what appears as a tantrum, the intent was simply to encourage you provide every possible bit of information to the experts as (within reason) the more data the less invest we need to do ... for example in order to try and test at your end I generated some code, if I had had access to your code at the outset, a quick run on my PC would have proven it works consistently ... a less involved method to suggest the issue as the application:

Your code on my pc running ok implies application
My code on your pc showing similar delay implies application

It actually took the 6 1/2 hours to structure and test some code, (around other commitments) to meet the potential initial requirement ... it is of course a valid position that your code became superfluous as soon as I posted it but my expectation at the time was you would post yours so we could see if yours would in fact work with some tweaks.  Whilst that is still possible it is in my view unlikely to be the case.

I am however extremely interested to see what mechanism you use to establish default folders because it is quicker than mine and if it's equally or more robust then it's a useful bit of code to analyse.

Either way, my apology stands but hopefully you understand a little better the underlying intent.

Chris
0
 
LVL 2

Author Comment

by:alpha456
ID: 37797383
My code was very amateurish.

I was very impressed with (a) the recursive loop, which I did not use and (b) the test for being a built-in folder. I simply listed the standard folders I wanted my code to ignore!

I occasionally answer questions on this forum and others (mainly relating to Excel), so I understand the desire for as much information as possible. It's particularly frustrating when you simply can't understand the question, which happens all too often.

Anyway, I do appreciate your having spent so much time trying to help me.

Best regards

Antony
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 37797497
>>> I simply listed the standard folders I wanted my code to ignore

That explains the time difference ... my code was always going to be slower than that due to the error handling ... which in my view was unavoidable - but for your own scope clearly your code is better but in regard to the start up delay ... do you have anything like for example shared folders or anything of the sort?

Chris
0
 
LVL 2

Author Comment

by:alpha456
ID: 37798320
No. I am self-employed, so everything is local, I am the only user etc.

I do have a pretty large pst file (16 GB, I need to do some archiving soon). I wonder if that makes Outlook store certain information on the hard disk that would otherwise be in RAM (don't really know if that makes any sense at all).

A
0
 
LVL 59

Accepted Solution

by:
Chris Bottomley earned 500 total points
ID: 37799383
Anything I can suggest would be dirty code i.e. I don't understand why it should have the problem post initial boot of the pc.  Indeed I would question why it was so important to reduce the time so as to justify the use of a way around the observation.

That said, you could run a macro on startup of outlook to establish any folders and save them somewhere.  Whilst it wouldn't be perfect, (perhaps the simple act of running it on start up will mean smooth running when you later try to use it in anger.

Essentially try a copy of your code which requires no user interaction called out of application_startup and see if it helps.

Note however 16GB is large, I think the 2007/2010 limit is effectively 20GB so indeed this may be a factor.

Chris
0
 
LVL 2

Author Closing Comment

by:alpha456
ID: 37799464
Thanks for your time. I suspect I will just have to live with it.
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

Set OWA language and time zone in Exchange for individuals, all users or per database.
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.
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 …

762 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

25 Experts available now in Live!

Get 1:1 Help Now