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

所有回覆

• 2012年7月13日 15:22

   

   
  

  0
  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.
  • 回覆
  • 引述

   
• 2012年7月13日 17:32

  版主

   

   
  

  0

  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,

  
  

  • 已編輯 Jan De Messemaeker - Project MVPMVP, Moderator 2012年7月13日 17:33
  • 已提議為解答 Amit Khare - Project Management Consultant 2012年7月16日 6:44
  • 已標示為解答 Jan De Messemaeker - Project MVPMVP, Moderator 2012年7月16日 7:31
  •  
  • 回覆
  • 引述

   
• 2012年7月13日 18:32

   

   
  

  0

  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.

  • 回覆
  • 引述

   
• 2012年7月15日 23:23

  版主

   

   
  

  2

  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

  • 已提議為解答 Amit Khare - Project Management Consultant 2012年7月16日 6:44
  •  
  • 回覆
  • 引述

   
• 2012年7月16日 9:11

  版主

   

   
  

  0

  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,

  • 回覆
  • 引述

   
• 2012年7月16日 16:13

   

   
  

  0

  Thanks for your help.

  It is greatly appreciated.

  • 回覆
  • 引述

   
• 2012年7月16日 18:16

   

   
  

  0

  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

  • 回覆
  • 引述

   
• 2012年7月16日 18:57

   

   
  

  0

  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? 

  • 回覆
  • 引述

   
• 2012年7月16日 23:16

   

   
  

  0

  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

  • 回覆
  • 引述

   
• 2012年7月17日 13:18

   

   
  

  0

  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.

  • 回覆
  • 引述

   
• 2012年7月17日 15:10

   

   
  

  0

  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

  • 回覆
  • 引述

   
• 2012年7月17日 15:43

  版主

   

   
  

  0

  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,

  • 回覆
  • 引述

   
• 2012年7月18日 15:10

   

   
  

  0
  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.
  • 回覆
  • 引述

   
• 2012年7月18日 19:18

  版主

   

   
  

  0

  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

  • 回覆
  • 引述