Can I remove one of these loops?

[ClimoC]

Hi guys

Anyone see a more memory-efficient way of running this code?

It's importing from Excel to Project resources.

The Loop goes through 800 excel rows, same for the 'For' which goes through 800 resources.

Despite having calculation set to manual and screenupdating off, this takes nearly 2 1/2 minutes to run

(nb: this is the engine of the macro everything else before it is just declarations and seeting arrays)

Dim xrow As Long, y As Long
    xrow = 2

Dim mailname As String, loginINIT As String, Dept As String, subDept As String, Phone As String, fmIDx As Integer


    Do While xrow <= xlsheet.Range("A" & Rows.count).End(xlUp).row
        If CleanStr(xlcel(xrow, activecol)) <> "inactive" Then
            resofmidx = CleanStr(xlcel(xrow, fmidxcol))
            y = 0
                    For Each r In myProject.resources
                    If r.Text28 Like resofmidx Then

                
                                    mailname = CleanStr(xlcel(xrow, mailnamecol))
                                    loginINIT = CleanStr(xlcel(xrow, loginINITcol))
                                    Dept = CleanStr(xlcel(xrow, deptcol))
                                    subDept = CleanStr(xlcel(xrow, subdeptcol))
                                    Phone = CleanStr(xlcel(xrow, phonecol))
                                    fmIDx = CleanStr(xlcel(xrow, fmidxcol))


                                    r.initials = loginINIT
                                    r.Text29 = subDept
                                    r.group = Dept
                                    r.Text30 = Phone
                                    r.Text28 = fmIDx
                                    y = 5
                
                Else
                    y = y
                End If
            
                Next
                
                If y < 5 Then

                                    mailname = CleanStr(xlcel(xrow, mailnamecol))
                                    loginINIT = CleanStr(xlcel(xrow, loginINITcol))
                                    Dept = CleanStr(xlcel(xrow, deptcol))
                                    subDept = CleanStr(xlcel(xrow, subdeptcol))
                                    Phone = CleanStr(xlcel(xrow, phonecol))
                                    fmIDx = CleanStr(xlcel(xrow, fmidxcol))
                                    
                                    
                                    Set myUser = myProject.resources.Add(mailname)
                                    myUser.initials = loginINIT
                                    myUser.Text29 = subDept
                                    myUser.group = Dept
                                    myUser.Text30 = Phone
                                    myUser.Text28 = fmIDx
                                    
                                    resources.Add loginINIT, myUser

                                    ResourceNames = myUser.name
                Else
                End If
            
            

Else
End If


xrow = xrow + 1
Loop

Any suggestions for speeding this up? I have noticed people always saying "I don't to use too many loops..." and I can see the illogic in looping within a loop... my brain just can't do it any differently because since excel has far more objects and properties, I never need more than one loop to do anything I want!

Cheers
C

 

[Andrew Poulsom]

Excel has a Find method. You could loop around the Resources and use that to return the row number that contains the contents of Text28 in column fmidxcol. But that might not be possible as you are applying a CleanStr function to the cell contents.

 

[ClimoC]

I had thought about that, but this purposely goes the other way around because it updates the resources it finds, and if it can't find it, adds it to the resources list.

This is all because my boss wants it to update data all the time, but she also wants this macro to run everytime something else happens to do with the core of the Gantt sheet.

She just expects this stuff will happen magically, and when I try to explain to her that it's slow, she just gets frustrated and says "Well can't you do something about it?"


*sigh*

 

[Andrew Poulsom]

When you say "everytime something else happens" what does that mean? If it's a cell change you can use the Worksheet_Change event procedure, in which case you may not need to loop around all the rows.

 

[ClimoC]

Oh - sorry Andrew I was meaning in Project. Whenever we synchronise our MPP files with new data from and SQL databse pulled through as an excel file, she wants the resources to get updated. It currently takes 2:30 to run this, and only 0:18 to sync up 3000+ tasks in the Gantt sheet!

Interestingly, I threw in the code for this thing to update a userform with an integer to show what row in excel it's up to... It starts by jumping up quite quickly (gives the impression it it running in groups of 4-5 at a time) and by the end is running at about 2 rows every second, and counts up 1 by 1 in the userform.

So it starts ok, and then just gets slower and slower... even when it hasn't had to add any new resource rows and is still just performing the field-match checks.

Odd....

 

[ClimoC]

Is there a 'dump' or something I need to do when it starts getting overloaded like this?

 

[Andrew Poulsom]

Sorry I don't know anything about MS Project so I don't know what may be slowing it down. Do the resources have names that you could match with something on the worksheet?

 

[ClimoC]

All you need to know about Project is that it's ****... 1/10 the objects of excel, and best case scenario 5 properties for an object...

Can't do it by 'name' as such, as that's one of the fields that gets updated. That's why we're doing it off a unique ID created by the export.

This will just have to do. At least it works. They'll just have to go get a coffee or do something else whilst it's running!