Anmelden
Microsoft.com
 
Microsoft
 
 
 
Microsoft >
> 
> 
Open file name macro

Odpovědět Open file name macro

  • Montag, 23. April 2012 15:49
     
     
    Anmelden
    0

    Hi,

    This is my 1st attempt at using macros with project 2010 so please be patient with me for asking possibly a simple question!!!

    I have tried to create a macro that passes 1 table & 1 field from the global mpt to an open project file then save & close. I wanted this so i could add a new custom column with lookup field to all my project files ( around 180 of them ).  This seemed to be simple task until i needed to find the vba code for adding opened file name to the macro as highligthed below in red text.

    I hope my question makes sense,  can anyone advise please?

    Sub Macro2()

    ' Macro Macro2

    ' Macro Recorded Mon 23/04/12 by Steve Hartwell.

        OrganizerMoveItem Type:=1, FileName:="Global.MPT", ToFileName:="Project1", Name:="TRW Gantt"

        OrganizerMoveItem Type:=9, FileName:="Global.MPT", ToFileName:="Project1", Name:="Project File Owner (Text9)"

        ViewApply Name:="Gantt Chart"

        FileSaveAs Name:="D:\SharedProjects\TRW Base Files\Project1.mpp", FormatID:="MSProject.MPP"

        FileCloseEx

    End Sub

    Regards

    Steve

     

Alle Antworten

  • Montag, 23. April 2012 15:55
     
     Beantwortet
    Anmelden
    0

    Steve.

    The current active file name is:

    Activeproject.Name

    John

    • Als Antwort markiert Steve TRW Montag, 23. April 2012 16:00
    •  
     
  • Montag, 23. April 2012 16:02
     
     
    Anmelden
    0

    Thanks John,

    To push my question to the next level would it be possible to program the macro to open all files in a particular folder and run the same macro on them automatically? Where would i find out how to do this on the net?

    Regards

    Steve

     
  • Montag, 23. April 2012 16:06
     
     
    Anmelden
    0

    John,

    I have added the code as below but it hasnt worked, what did i do wrong?

    Regards

    Steve

    Sub Steve_Macro1()

    ' Macro Steve_Macro1

    ' Macro Recorded Mon 23/04/12 by Steve Hartwell.

        OrganizerMoveItem Type:=1, FileName:="Global.MPT", ToFileName:="Activeproject.Name", Name:="TRW Gantt"

        OrganizerMoveItem Type:=9, FileName:="Global.MPT", ToFileName:="Activeproject.Name", Name:="Project File Owner (Text9)"

        ViewApply Name:="Gantt Chart"

        FileSave

        FileCloseEx

    End Sub

     
  • Montag, 23. April 2012 16:07
    Moderator
     
     Beantwortet
    Anmelden
    0
    take the quotes off of Activeproject.name

    Brian Kennemer - Project MVP
    DeltaBahn Senior Architect
    endlessly obsessing about Project Server…so that you don’t have to.
    Blog | Twitter | LinkedIn

    • Als Antwort markiert Steve TRW Montag, 23. April 2012 16:10
    •  
     
  • Montag, 23. April 2012 16:10
     
     
    Anmelden
    0

    brian,

    Thankyou that works. easy when you know how eh!

    Steve

     
  • Montag, 23. April 2012 16:14
    Moderator
     
     Beantwortet Enthält Code
    Anmelden
    0

    Yes it is possible but I dont have the code available i front of me right now.

    This code will not open all the files in a folder but it will loop through all the projects you currently have open.

    So you would  manually open the projects into Pro all at once or in groups and then run the macro.

    Try it on some TEST PROJECTS.

    Notice that instead of Activeproject.name I changed it to P.Name and put all the code in a loop where P is a project. the loop grabs all the open projects and makes P equal one of them. The code does it's thing and then moves to the next one.

    Sub AllOpen()
Dim P As Project

For Each P In Application.Projects
    OrganizerMoveItem Type:=1, FileName:="Global.MPT", ToFileName:=P.Name, Name:="TRW Gantt"
    OrganizerMoveItem Type:=9, FileName:="Global.MPT", ToFileName:=P.Name, Name:="Project File Owner (Text9)"
    ViewApply Name:="Gantt Chart"
    FileSave
    FileCloseEx
Next P
End Sub

    Brian Kennemer - Project MVP
    DeltaBahn Senior Architect
    endlessly obsessing about Project Server…so that you don’t have to.
    Blog | Twitter | LinkedIn

    • Als Antwort markiert Steve TRW Montag, 23. April 2012 16:50
    •  
     
  • Montag, 23. April 2012 16:57
     
     Beantwortet
    Anmelden
    0

    Brian,

    Thanks, i have tried both, they both only seem to work on new blank projects not existing projects? Also it does not seem to be closing the files after saving?

    I get the following (obviously the file name in the error is different for each file i try) error on all projects other than new blank ones.

    Run-time error '1101':

    The file "PR18616" was not found

    regards

    steve


    Does the macro look for the file name or a name within the open file its self? wondering if i have made some sort of school boy error in the creation of the files i wish to change
    • Bearbeitet Steve TRW Montag, 23. April 2012 17:05
    • Als Antwort markiert Steve TRW Montag, 23. April 2012 18:00
    •  
     
  • Montag, 23. April 2012 17:34
    Moderator
     
      Enthält Code
    Anmelden
    0

    OK not sure why it is doing that but Im seeing some odd behavior as well. So instead of that loop try this one, which is actually what you wanted to start with.

    Change the C:\Foo\ to your directory where your MPP files are located. Then run the macro.

    Sub x()
    Dim strFilename As String
    Dim strPath As String
    Dim P As Project
    Application.ScreenUpdating = False
    
    strPath = "C:\Foo\"
    
    strFilename = Dir(strPath & "*.mpp")
    Do While Len(strFilename) > 0
        Application.FileOpenEx (strPath & strFilename)
        Set P = Application.ActiveProject
        
        OrganizerMoveItem Type:=1, FileName:="Global.MPT", ToFileName:=P.Name, Name:="TRW Gantt"
        OrganizerMoveItem Type:=9, FileName:="Global.MPT", ToFileName:=P.Name, Name:="Project File Owner (Text9)"

        FileClose pjSave
        strFilename = Dir
    Loop
    Application.ScreenUpdating = True
End Sub

    Brian Kennemer - Project MVP
    DeltaBahn Senior Architect
    endlessly obsessing about Project Server…so that you don’t have to.
    Blog | Twitter | LinkedIn

     
  • Montag, 23. April 2012 18:03
     
     
    Anmelden
    0

    Brian,

    Thats fantastic thanks. However i still have the same error

    Run-time error '1101':

    The file "project1" was not found

    I just created 3 or 4 blank projects entered them into a test folder and added that folder destination to the macro. It obviously found the folder and opened the 1st file but not sure why it says file was not found

    Regards

    Steve


    • Bearbeitet Steve TRW Montag, 23. April 2012 18:05 spelling!!
    •  
     
  • Montag, 23. April 2012 18:12
    Moderator
     
     
    Anmelden
    0

    Was one of the files called Project1?

    Can you post the code you ran?

    Brian Kennemer - Project MVP
    DeltaBahn Senior Architect
    endlessly obsessing about Project Server…so that you don’t have to.
    Blog | Twitter | LinkedIn

     
  • Montag, 23. April 2012 18:17
     
     
    Anmelden
    0

    Brian,

    Yes I just made some blank projects to test project1, project2 etc etc etc. I used the code as you entered above and edited just the filepath.

    Thanks

    Sub x()
        Dim strFilename As String
        Dim strPath As String
        Dim P As Project
        Application.ScreenUpdating = False
       
        strPath = "C:\files to dopy and paste\"
       
        strFilename = Dir(strPath & "*.mpp")
        Do While Len(strFilename) > 0
            Application.FileOpenEx (strPath & strFilename)
            Set P = Application.ActiveProject
           
            OrganizerMoveItem Type:=1, FileName:="Global.MPT", ToFileName:=P.Name, Name:="TRW Gantt"
            OrganizerMoveItem Type:=9, FileName:="Global.MPT", ToFileName:=P.Name, Name:="Project File Owner (Text9)"

            FileClose pjSave
            strFilename = Dir
        Loop
        Application.ScreenUpdating = True
    End Sub

     
  • Montag, 23. April 2012 18:23
    Moderator
     
     
    Anmelden
    0

    I dont know what to say. This code runs perfectly on my machine.

    I have three projects in my folder: Project1.mpp, foo.mpp, foo2.mpp

    I run the code and it copies a table called Table1 into those projects, saves and closes them. (I have this line in my code rather than your TRW Gantt: OrganizerMoveItem Type:=1, FileName:="Global.MPT", ToFileName:=P.Name, Name:="Table 1")

    What if you close all open projects before you run the code?

    Brian Kennemer - Project MVP
    DeltaBahn Senior Architect
    endlessly obsessing about Project Server…so that you don’t have to.
    Blog | Twitter | LinkedIn

     
  • Montag, 23. April 2012 20:36
     
     
    Anmelden
    0

    Noooooooooooooooooooo, so close i can taste the success!!!

    You dont suppose its anything to do with my OS do you i am running windows 7 ? This has caused us some user issues on other functions compared with XP.


    Scrap that i have tried both
    • Bearbeitet Steve TRW Montag, 23. April 2012 20:40
    •  
     
  • Dienstag, 24. April 2012 07:47
     
     
    Anmelden
    0

    Brian,

    My hunch was correct after all, i have just passed the macro and files to a collegues computer who is running xp pro and it works fine so i guess its a windows 7 issue AGAIN!

    If i made a new post on here do you think i may get an answer?

    Steve