none
VBA Consolidation Routine working on machine and failing on another of the same build RRS feed

  • Question

  • Hi Fellow Forum Members

    I have an issue with a piece of consolidation code that works perfectly well on one machine but fails on another machine with exactly the same build.  The code is:

    Sub dirfilenames()
    Application.DisplayAlerts = False
    SelectTaskField Row:=1, Column:="Name"
    Mypath = "D:\CPP Build Template\CPP Build Plans\"
    MyName = Dir(Mypath)
    SelectRow Row:=0
    Do While MyName <> ""
        If Right$(MyName, 3) = "mpp" Then
            ConsolidateProjects FileNames:=Mypath & MyName, _
            Newwindow:=False, Hidesubtasks:=True, AttachToSources:=False
            SelectRow Row:=1
        End If
        MyName = Dir
    Loop
    Application.DisplayAlerts = True
    End Sub
    

    The code simply consolidates a number of plans contained in a pre-determined folder into a master plan.  The issue is that it appears to insert all 12 plans but the first 11 rows are blank.  The last project inserted can be viewed but the others cannot be seen.

    Any advice/guidance appreciated.

    Tony


    TKHussar

    Thursday, April 23, 2015 11:29 AM

Answers

  • Hi Rod

    I have implemented your code andit appears to work okay.  However this is an intermittent issue but I will monitor it going forward and hopfully I wont have to return to this issue :-).

    Many thanks for your help.

    Tony


    TKHussar

    • Marked as answer by TKHussar Wednesday, May 6, 2015 3:03 PM
    Wednesday, May 6, 2015 3:03 PM

All replies

  • Tony,

    I didn't try your code, but I see you are testing for the "mpp" in the file name. Do both machine have Windows set to display file extensions?

    John

    Thursday, April 23, 2015 4:21 PM
  • Try:

    Sub DirFileNames()
    Dim MyPath As String
    Dim MyName As String
        Application.DisplayAlerts = False
        SelectTaskField Row:=1, Column:="Name", rowrelative:=False
        MyPath = "D:\CPP Build Template\CPP Build Plans\"
        MyPath = "C:\@ProjectSystems\@Clients\Nonsuch\"
        MyName = Dir(MyPath & "*.mpp")
        Do While MyName <> ""
            ConsolidateProjects FileNames:=MyPath & MyName, _
            Newwindow:=False, Hidesubtasks:=True, AttachToSources:=False
            MyName = Dir
            SelectEnd
            SelectTaskField Row:=1, Column:="Name"
        Loop
        Application.DisplayAlerts = True
    End Sub
    


    Rod Gill
    Author of the one and only Project VBA Book
    www.project-systems.co.nz

    Friday, April 24, 2015 3:46 AM
    Moderator
  • Hi John,

    Both machines are set to display file extensions.

    Tony


    TKHussar

    Friday, April 24, 2015 1:32 PM
  • Hi Rod,

    Many thanks for your response.  We have tried your code and now we are getting the following error message:

    Method 'ConsolidateProjects' of object'_MSProject' failed

    Kind regards

    Tony


    TKHussar

    Friday, April 24, 2015 1:33 PM
  • On my Win 7 SP2 Project 2010 Sp2 PC it works fine.

    Can you single step through the code and tell me the full path. What MyPath & MyName holds.


    Rod Gill
    Author of the one and only Project VBA Book
    www.project-systems.co.nz

    Saturday, April 25, 2015 1:28 AM
    Moderator
  • Hi Rod

    Thanks for your response:

    MyPath = D:\CPP Build Template\CPP Build Plans\

    MyName = 201504 Defra Network Testing Draft

    Kind regards

    Tony


    TKHussar

    Sunday, April 26, 2015 10:53 AM
  • Tony,

    So where is the ".mpp" extension in the "MyName" variable? Is it not there or did you just forget to type it?

    Just for reference, Rod's code works fine for me also. Same setup, Windows 7 with Project 2010 fully updated.

    Here's a suggestion, change the path to your C:\ directory and see if that work on both machines.

    John



    Sunday, April 26, 2015 4:02 PM
  • Hi,

    I encountered this a long time ago, so maybe it has been fixed by a service pack, do you have the latest installed?

    I've checked my standard code that I reuse all the time and it has a test built in, so that may be why I haven't seen the issue for a while?!

    Try:

    Sub DirFileNames()
    Dim MyPath As String
    Dim MyName As String
        Application.DisplayAlerts = False
        SelectTaskField Row:=1, Column:="Name", rowrelative:=False
        MyPath = "D:\CPP Build Template\CPP Build Plans\"
        MyPath = "C:\@ProjectSystems\@Clients\Nonsuch\"
        MyName = Dir(MyPath & "*.mpp")
        Do While MyName <> ""
            If Right(MyName, 4) <> ".mpp" Then
               MyName = MyName & ".mpp"    'Name must include .mpp
            End If
            ConsolidateProjects FileNames:=MyPath & MyName, _
            Newwindow:=False, Hidesubtasks:=True, AttachToSources:=False
            MyName = Dir
            SelectEnd
            SelectTaskField Row:=1, Column:="Name"
        Loop
        Application.DisplayAlerts = True
    End Sub


    Rod Gill
    Author of the one and only Project VBA Book
    www.project-systems.co.nz

    Sunday, April 26, 2015 9:37 PM
    Moderator
  • Hi Rod

    I have implemented your code andit appears to work okay.  However this is an intermittent issue but I will monitor it going forward and hopfully I wont have to return to this issue :-).

    Many thanks for your help.

    Tony


    TKHussar

    • Marked as answer by TKHussar Wednesday, May 6, 2015 3:03 PM
    Wednesday, May 6, 2015 3:03 PM