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.
2012年7月13日 15:22I 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.
This may inspire you:
dim Openfile as project
dim folderaddress as string
dim Fileaddress as string
fileaddress=dir(folderaddress & "*.mpp")
do while len(fileaddress)>0
sub Handle (anaddress as string)
set nutask=openfile.tasks.add (nutaskname)
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.
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.
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 SubThis functionality is almost a copy of one of the examples in my book!
Rod Gill Project Management
- 已提議為解答 Amit Khare - Project Management Consultant 2012年7月16日 6:44
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 :-)
Thanks for your help.
It is greatly appreciated.
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.
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?
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.
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.
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.
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.
2012年7月18日 15:10I 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.
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