none
Loop Through Files - worked good, stopped working

    Pertanyaan

  • Tom,

    Following on from our previous conversation, I am using this VBA below, and it works great in a folder with 59 files and another file where the 59 are subprojects, and there is no resource pool.

    However, when I change the path name from what works

    FolderPath = "X:\PROJECTS\PLANNING\Perfect Project Planning\Draft Plans\Not Yet OK\5000 Series Jobs\"

    to 

    FolderPath = "X:\PROJECTS\PLANNING\Perfect Project Planning\Draft Plans\OK\"

    it doesn't work and it stops at

    FileOpenEx Name:=FileName, ReadOnly:=False, FormatID:="MSProject.MPP".

    In the OK folder, there is a shared resource pool, and that's the only difference I can put my finger on. So I figure I should open the pool before running the macro, but that doesn't help

    Sub Loop_Files()
    ' Macro Loop_Files
    ' Macro 22/07/18 21:28 by Trevor Rabey.
    Dim FolderPath As String 'store the folderpath in memory.
    Dim FileName As String 'store the file name in memory.
    'error handling. If there is an error the macro will go to the bottom of the macro where the error name is
    'On Error GoTo ErrHandler
    'speed up macro.
    'True or False allows or suppresses updating screen while macro runs.
    Application.ScreenUpdating = True
    'Prevents showing dialog boxes while macro runs.
    Application.DisplayAlerts = True
    'Call Dir the first time, pointing it to all Microsoft Project files in the folder path.
    FolderPath = "X:\PROJECTS\PLANNING\Perfect Project Planning\Draft Plans\OK\"
    'Check to make sure that the right most character is a backslash
    If Right(FolderPath, 1) <> "\" Then FolderPath = FolderPath + "\"
    FileName = Dir(FolderPath & "*.mpp")
    ' Loop until Dir returns an empty string.
    Do While FileName <> ""
    FileOpenEx Name:=FileName, ReadOnly:=False, FormatID:="MSProject.MPP"
    'Put whatever you want to do here.
    BaselineSave All:=True, Copy:=0, Into:=0
    OutlineShowTasks OutlineNumber:=pjTaskOutlineShowLevel1
    ProjectSummaryInfo StatusDate:="27/07/18 17:00"
    CalculateAll
    FileSave
    FileCloseEx
    ' Use Dir to get the next file name.
    FileName = Dir()
    'end of loop
    Loop
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    End Sub
    27 Juli 2018 7:21

Semua Balasan

  • Hi Trevor,

    Not knowing anything about your files, here are the steps I would take to debug:

    1. Find out how far the macro gets before crashing and what the conditions are when it crashes.  The quickest way to do this is to enter two commands into your code:

    • After the Do While statement and before the FileOpenEx statement, type: Debug.Print FileName
    • Before the FileName=Dir() statement, type: Debug.Print "Success"

    These commands print to the Immediate window in the VB editor, so make sure it's visible.

    2. When the macro fails, inspect the values in the Locals window for anything unexpected.

    Since you've highlighted a uniqueness related to the resource pool file, you may want to modify the code to make sure that the resource pool file is a)opened first by the code; b)excluded from any actions in the Do Loop, including attempts to re-open or to close. (e.g. If Not FileName = "ResourcePool.mpp" then....); and c) closed last, after the loop is finished.

    Eventually, get used to a) stepping through your code one line at a time using the F8 key and/or b) Entering breakpoints to suspend execution and allow inspection of local variables at key points and/or c) Using the Debug.Assert command to suspend execution and allow inspection when certain conditions are met.

    Your code has no error handling at all.  Virtually all commands can encounter unexpected conditions and throw an error.  Absent any other error-handling instructions, VBA will just suspend code and enter debug-mode, highlighting the command that causes the error.  Three typical error-handling commands are:

    On Error Resume Next - this stores an exception variable "Err" and proceeds to the next command.  You can make the next command: If Err.number <> 0 then [do something special to handle the error], or you could skip that step and just allow code to proceed and ignore all errors.

    On Error GoTo ErrHandler - this transfers control to another point in the code identified by a label, which in this case is "ErrHandler:"

    On Error GoTo 0 - reverts to the default behavior.

    Eventually you may want to buy Rod's book, if you haven't already.  It's a good investment.

    Let me know how it goes.

      

    27 Juli 2018 13:56
  • Tom,

    thinks. Lots of handy hints there.

    the problem turned out to be something connected with the filepath and the mapped X: drive.
    I got it working.

    ... and, yes, I know its as rough as, patched together with fencing wire and tape.

    I've got Rod's book, and a lot more VBA coming.

    29 Juli 2018 8:00
  • Glad you've got it sorted.
    30 Juli 2018 13:29