none
Write project summary data for all files in folder to new file RRS feed

  • Question

  • MSOP 2010 Professional

    I need to create a summary file for all the projects in a folder.  13 files at present.

    Is there an easy way to do this via VBA?  I only need to copy the Task Name, Start Date, Finish Date and Duration.  This is a summary report for a weekly meeting. 

    I don't need any of the detailed tasks.

    Thanks.

    Friday, July 13, 2012 3:04 PM

Answers

  • Hi,

    This may inspire you:

    dim Openfile as project

    sub allfiles

    dim folderaddress as string

    dim Fileaddress as string

    folderaddress= ......

    set openfile=activeproject

    fileaddress=dir(folderaddress & "*.mpp")

    Handle fileaddress

    do while len(fileaddress)>0

    fileaddress=dir

    handle fileadress

    loop

    end sub

    sub Handle (anaddress as string)

    fileopen (anaddress)

    Nutaskname=activeproject.projectsummarytask.name

    set nutask=openfile.tasks.add (nutaskname)

    nutask.start=activeproject.projectsummarytask.start

    etc.

    fileclose pjdonotsave

    end sub

    Not tested,but from here you will no doubt find what you need.

    Oh and DO NOT enter start, finish AND duration - one can be clculated from the other two.

    Greetings,


    Friday, July 13, 2012 5:32 PM
    Moderator

All replies

  • I also need the Notes field.  I am currently doing this manually.  Using Subprojects and collapsing to subproject level is not an option as management wants a "Clean" master with only one line item per project.
    Friday, July 13, 2012 3:22 PM
  • Hi,

    This may inspire you:

    dim Openfile as project

    sub allfiles

    dim folderaddress as string

    dim Fileaddress as string

    folderaddress= ......

    set openfile=activeproject

    fileaddress=dir(folderaddress & "*.mpp")

    Handle fileaddress

    do while len(fileaddress)>0

    fileaddress=dir

    handle fileadress

    loop

    end sub

    sub Handle (anaddress as string)

    fileopen (anaddress)

    Nutaskname=activeproject.projectsummarytask.name

    set nutask=openfile.tasks.add (nutaskname)

    nutask.start=activeproject.projectsummarytask.start

    etc.

    fileclose pjdonotsave

    end sub

    Not tested,but from here you will no doubt find what you need.

    Oh and DO NOT enter start, finish AND duration - one can be clculated from the other two.

    Greetings,


    Friday, July 13, 2012 5:32 PM
    Moderator
  • Hi,

    Thanks for your reply.  I will take this and see if I can figure it out.  I am very limited with VBA skills.  Am wondering if I created a Macro from an open file to open each individual files and copy the needed columns (Name, Start and Finish) if this would get the single line report.  Will give that a try first and maybe can build on your posts.

    Thanks very much.

    Friday, July 13, 2012 6:32 PM
  • Jan has given you the basics. It creates one row per project. Here's a little more help:

    Sub allfiles()
    Dim FolderAddress As String
    Dim FileAddress As String
        FolderAddress = "Enter folder path here"
        Set Openfile = ActiveProject
        FileAddress = Dir(FolderAddress & "*.mpp")
        Do While Len(FileAddress) > 0
            Handle FileAddress
            FileAddress = Dir
        Loop
    End Sub
     
    Sub Handle(AnAddress As String)
    Dim Tsk As Task
        FileOpen AnAddress
        With ActiveProject.ProjectSummaryTask
            Set Tsk = Openfile.Tasks.Add(.Name)
            Tsk.Start = .Start
            Tsk.Notes = .Notes
            'etc
        End With
        FileClose pjDoNotSave
    End Sub
    
    This functionality is almost a copy of one of the examples in my book!

    Rod Gill

    The one and only Project VBA Book

    Rod Gill Project Management

    Sunday, July 15, 2012 11:23 PM
    Moderator
  • Hi Rod,

    No wonder it looks like the example in your book - it is also practically a copy of the Help of the Dir function which I've been using for over 10 years now - years before your book was released :-)

    Greetings,

    Monday, July 16, 2012 9:11 AM
    Moderator
  • Thanks for your help.

    It is greatly appreciated.

    Monday, July 16, 2012 4:13 PM
  • CLB24,

    Although you may have a fixed directory where all your files are stored you could make a tweak to Rod's code that wouldn't require the path to be hard coded into the macro itself. If you open any one of the files you can use the following statement for the FolderAddress variable:

    FolderAddress = ActiveProject.Path

    It just makes the macro a little more generic.

    John

    Monday, July 16, 2012 6:16 PM
  • Thanks John and Rod.

    I am running MSOP 2010.  Will the code run OK in this version as written?  I am new to the code side of MSOP.  Do I copy and paste the above into a module under the VBE? 

    Monday, July 16, 2012 6:57 PM
  • CLB,

    You're welcome and thanks for the feedback. Rod's code is pretty simple and straightforward and doesn't use any version unique objects or properties so yes, you should be able to simply copy it into a new module in the VBE. Before you copy and paste it into the VBE window I suggest you close all open projects. That way the macro will get put into your Global file and and not be tied to a specific project file.

    John

    Monday, July 16, 2012 11:16 PM
  • John,

    I copied the code as you suggested and pasted it into a new module from the VBE.  I inserted the directory path where my files are (where Rod has indicated "Enter folder path here") and ran the macro but nothing was written to my open file.  I know I must be doing something wrong but haven't a clue what.

    Can you point me in the right direction?

    Many thanks for your assistance.

    Tuesday, July 17, 2012 1:18 PM
  • CLB24,

    You're right, it doesn't work. I found two issues. The first is that there should be a backslash added to the FileAddress statement (unless you already added a trailing backslash to the FolderAddress line):

    FileAddress = Dir(FolderAddress & "\*.mpp")

    The second has to do with the Handle subroutine. A runtime error is generated on the statement:

    Set Tsk = Openfile.Tasks.Add(.name)

    I don't know what the problem is at this point and I won't be able to troubleshoot it until later. If Jan or Rod checks back in, hopefully they will shed some light.

    John

    Tuesday, July 17, 2012 3:10 PM
  • Hi John and all,

    The extra backcslash isn't always necessary, that is as far as I remember an option in File OPtions in Windows; but there is no harm inadding it, even when this yilds a double backslash it works.

    As such the code cannot work since Openfile is not known to the Handle sub. One could write a dim statement in the module.

    Greetings,

    Tuesday, July 17, 2012 3:43 PM
    Moderator
  • I inserted the Set Openfile = ActiveProject in the handle sub and still nothing happens.  I know I must be doing something wrong.  Any additional advice would be appreciated.
    Wednesday, July 18, 2012 3:10 PM
  • Hi,

    When running Handle Openfile is no longer the activeproject (since you just openend an other one)! As I suggest put the Dim statement BEFORE the first sub statement in the module in such a way Openfile is defined for the whole module

    Wednesday, July 18, 2012 7:18 PM
    Moderator