none
MS Project VBA ActiveProject.Resources RRS feed

  • Question

  • Hi

    I'm trying to access my ms project resources to export them. In my Resource Usage view in project I can see almost 100 rows. Although not all rows have unique "Resource Name", there is 30ish unique Resource names.

    The problem is that in my VBA code the  ActiveProject.Resources does not contain any Resources.  Why can't I see these resources from my vba code?

    Sub ResourceTest()
        Dim rs As Resources
        Dim r As Resource
        Set rs = ActiveProject.Resources
      
        For Each r In rs
               ...

        Next r
    End Sub

    Please help.

    Wednesday, October 12, 2011 6:44 PM

Answers

  • kurgaan,

    I would have been real helpful if you had told us in the beginning that you were working with a master/subproject file structure. It makes all the difference in the world! You still could have file corruption since you indicate you have the linked file structure spread over multiple directories. That type of structure is a very good starting point for corruption.

    Okay, now back to the issue. Early on Brian gave you a simple macro to insure you were looking at the correct project. Your response was "yes" and you could see all the resource on the resource sheet. Well, here's what you were seeing. You most likely ran Brian's code at the master file level and that of course showed you the master file project name. The Resource Sheet you saw visually was actually looking back into the subproject's as a combined resource sheet displayed at the master level. However, those resources are not at the master level so the code to look for them will come up with what? No resources at master level.

    When run on a master file, code to examine data within subprojects needs to point to the subproject data. The following code is an example of how you do that:

    Sub Macro1()

    Dim i As Integer

    Dim subproj As MSProject.Subproject

        For Each subproj In ActiveProject.Subprojects

            i = i + 1

            Debug.Print subproj.SourceProject.Name & " number " & i & " index " & subproj.index 

        Next

    End Sub

    Try that approach and see if it solves your problem.

    John

    • Marked as answer by kurgaan Thursday, October 20, 2011 5:12 PM
    Thursday, October 20, 2011 4:11 PM

All replies

  • Go to the resource sheet view. how many resources are there?

    When you are running this code are you sure that the active project is actually the project with the resources?

    run this code and see what project name comes up

     

    Sub ResourceTest()
    Dim rs As Resources
    Dim r As Resource

    msgbox activeproject.name
    Set rs = ActiveProject.Resources

    For Each r In rs
      msgbox r.name

    Next r
    End Sub


    Brian Kennemer – DeltaBahn Senior Architect
    Blog | Twitter | LinkedIn
    Wednesday, October 12, 2011 6:50 PM
    Moderator
  • Brian,

    Thanks for your response. In the resource sheet view there are 1232 resources.

    When I ran the code the pop-up showed the correct file name.

    Any ideas?

    Wednesday, October 12, 2011 7:06 PM
  • did the macro I gave you pop any other messege boxes after the name of the project?

     


    Brian Kennemer – DeltaBahn Senior Architect
    Blog | Twitter | LinkedIn
    Wednesday, October 12, 2011 8:00 PM
    Moderator
  • No other pop up.

    P.Resources.Count = 0

     

    Wednesday, October 12, 2011 8:05 PM
  • Can you upload a screenshot of your resource sheet?

    Is it just this project or does it happen with other projects also?

    What if you open a new project, type in one resource, open the VBA editor and then in the immediate window type:

    debug.print activeproject.resources.count


    Brian Kennemer – DeltaBahn Senior Architect
    Blog | Twitter | LinkedIn
    Wednesday, October 12, 2011 8:08 PM
    Moderator
  • debug.print activeproject.resources.count    returns 1 for my created project, so it works here.

    I'm not sure I want to upload the resource sheet image with our names and rates.

    But the above demonstrates that there could be something in the individual project that is causing the problem.

    Wednesday, October 12, 2011 8:15 PM
  • OK.

    Try copying all the tasks and pasting them into a new, blank project.

    that will bring over all the tasks and the assigned resources.

     

    What about copying the resources out and pasting them into a new project, deleting the resources from your file and then pasting them back in?

     

    Other than that: how was this project created? Was it created in a previous version? Was it programmatically created?


    Brian Kennemer – DeltaBahn Senior Architect
    Blog | Twitter | LinkedIn
    Wednesday, October 12, 2011 8:21 PM
    Moderator
  • kurgaan,

    Sounds like file corruption to me. Go to the MVP website at, http://project.mvps.org/faqs.htm, and try one of the methods described in FAQ 43 - Handling Project file corruption and/or bloat.

    John

    Wednesday, October 12, 2011 8:31 PM
  • Brian

    It is starting to appear the the project file was created incorrectly and there are no actual resources in the file. I have tried the copying and pasting resources but I seem to loose some of the data. I find it odd that I can see this list of what looks like resources in the Resource Usage view but they aren't really resources.
    Wednesday, October 19, 2011 2:59 PM
  • THanks John

    I tried that but it had no effect. I need to find a way to add new resources with the information from the Resource Usage view. Any ideas?

    Wednesday, October 19, 2011 3:00 PM
  • kurgaan,

    I'm really surprised clearing corruption had no effect on your file since everything you've described to those of us who have responded sounds really strange. In another post Jan asked if you could send him the file. I'll ask the same thing. Your issue is just strange enough that the best way we can figure it out is by actually looking at your file. So, if you could either send the file to Jan at the address he posted or to my address below, we can get to the bottom of this.

    John

    jensenljatatfastmaildotdotfm

    (remove obvious redundancies)

    Wednesday, October 19, 2011 3:08 PM
  • John

    Thanks for your response. I appreciate Jan and your offers to review the actual project file but unfortunately due to the sensitivity of the data involved, I can't send it out.

    I am trying to fix potential corruption through FAQ 43, but it is difficult with a lot of linked projects in different directories,.... then I started thinking. If the main project has no resources, but the linked projects contain them. Maybe I just don't know how to access them in that scenario?

     

    Thursday, October 20, 2011 1:12 PM
  • kurgaan,

    I would have been real helpful if you had told us in the beginning that you were working with a master/subproject file structure. It makes all the difference in the world! You still could have file corruption since you indicate you have the linked file structure spread over multiple directories. That type of structure is a very good starting point for corruption.

    Okay, now back to the issue. Early on Brian gave you a simple macro to insure you were looking at the correct project. Your response was "yes" and you could see all the resource on the resource sheet. Well, here's what you were seeing. You most likely ran Brian's code at the master file level and that of course showed you the master file project name. The Resource Sheet you saw visually was actually looking back into the subproject's as a combined resource sheet displayed at the master level. However, those resources are not at the master level so the code to look for them will come up with what? No resources at master level.

    When run on a master file, code to examine data within subprojects needs to point to the subproject data. The following code is an example of how you do that:

    Sub Macro1()

    Dim i As Integer

    Dim subproj As MSProject.Subproject

        For Each subproj In ActiveProject.Subprojects

            i = i + 1

            Debug.Print subproj.SourceProject.Name & " number " & i & " index " & subproj.index 

        Next

    End Sub

    Try that approach and see if it solves your problem.

    John

    • Marked as answer by kurgaan Thursday, October 20, 2011 5:12 PM
    Thursday, October 20, 2011 4:11 PM
  • Thanks John

    That seems to work.

    Thursday, October 20, 2011 5:12 PM
  • I needed to use

    Set rs = subproj.SourceProject.Resources

    For some reason I thought that SourceProject would be the master.

    Thursday, October 20, 2011 5:17 PM
  • kurgaan,

    You're welcome and thanks for the feedback.

    The "source project" is the subproject. The master is the "active project".

    John

    Thursday, October 20, 2011 5:19 PM
  • I tried this code in MS Excel with an open active MS Project 2016 file.  It would not run.
    Wednesday, October 12, 2016 7:28 PM
  • thiirane,

    This is a really old thread. What code are you trying to run and what are you trying to do?

    We need a lot more information before we can begin to help you.

    John

    Thursday, October 13, 2016 12:31 AM