Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Collect file names for opening (VBA for Project 2003)

    Hi all,
    In excel I use the GetOpenFileName method to collect files, store in variable and then use the Open method to open the collected files. Can anyone tell me how to do this in MS Project?

    I am using the Application.ConsolidatedProjects method to inset projects into a master project. I need the Open dialog to pop up and the user must be able to select the couple of projects, and when choosing OK, these projects will be inserted. I need to know how to activate and store the selected files and then use them in the ConsolidatedProjects method to insert them.

    TIA
    Regards,
    Rudi

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Collect file names for opening (VBA for Project 2003)

    I don't have MS Project, so I can't test. If you set a reference to the Microsoft Office 11.0 Object Library in Tools | References, can you use the FileDialog object?

    If not, you could use the modules and class module from <post:=505,867>post 505,867</post:> (although they are in a Word document, they will work in any VBA dialect).

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Collect file names for opening (VBA for Project 2003)

    Tx for the reply Hans,

    I have a reference set to the Office Objects in VBA. And then I collected the help file example of the FileDialog object. The help file code that I want to tweek debugs though...see the attached graphic. Any ideas here?

    Tx
    Regards,
    Rudi

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Collect file names for opening (VBA for Project 2003)

    Apparently FileDialog hasn't been implemented in MS Project (so much for consistency). Try the API code from the post I referred to.

  5. #5
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Collect file names for opening (VBA for Project 2003)

    Bummer....trying the other method now...........
    Regards,
    Rudi

  6. #6
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Collect file names for opening (VBA for Project 2003)

    Hi Hans,

    I imported the modules from your post reference into my Project File. The end result is collecting and displaying the file I choose in a msgbox. This is great as I can just change this into a variable to eventually insert into the ConsolidatedProjects Method.

    The only thing is that this code allows me only to choose one file at a time, meaning I need to run the macro 20 times for 20 files. Can the code be tweeked to allow for multiple selection?

    Tx
    Regards,
    Rudi

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Collect file names for opening (VBA for Project 2003)

    You can add Or cdlAllowMultiSelect to the Flags.
    The resulting .Filename will be a string consisting of the path and of the selected filenames (without a path), separated by spaces (file names with spaces are converted to "short" format). Example:
    <code>
    "H:Project This.doc That.doc Other.doc"
    </code>
    You can use Split to parse this string.

    <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>

  8. #8
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Collect file names for opening (VBA for Project 2003)

    Tx Hans...I will need to see if I can find some Split Function code so i can split those files...
    Many tx for the guidance so far!!
    Cheers
    Regards,
    Rudi

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Collect file names for opening (VBA for Project 2003)

    Doesn't Project VBA recognize Split?

  10. #10
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Collect file names for opening (VBA for Projec

    Hans

    From what I see Excel uses Split() as a function but in Project it has a completely different context where it splits a task into portions

    a typical syntax would be:

    ActiveProject.Tasks(WhichTask).Split SplitFrom, SplitTo


    Where SplitFrom, SplitTo are inputs set at variant

    HTH
    Jerry

  11. #11
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Collect file names for opening (VBA for Project 2003)

    You misunderstand. The code i refer to is sample code to show me how to use the Split Function to populate a variable with each file name so that I can assign it to the ConsolidatedProject method.

    If you can give me some advice.

    The code looks like this:
    Application.ConsolidateProject FileNames:="Name1" & ListSeperator & "Name2"

    I need to split the multiple files and get each one into the name area of the method...How do i split multiple files and assign them to many variables, or one variable with an index number, ie: Name(i)

    Tx
    Regards,
    Rudi

  12. #12
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Collect file names for opening (VBA for Projec

    Hi Rudi

    I haven't got a lot of time to reseach this fully as a bit busy at moment but you can do soemthing like this I thought:


    Sub ConsolidateMe()

    Dim msp As New MSProject.Application
    Dim project As New MSProject.Application
    msp.DisplayAlerts = False

    msp.FileOpen("c:documents and settingsrudidesktopConRep.mpt")
    <span style="background-color: #FFFFFF;"><font color=black>msp.ConsolidateProjects("c:documents and settingsrudidesktopplan1.mpp", , False)
    msp.ConsolidateProjects("c:documents and settingsrudidesktopplan2.mpp", , False)</font color=black> </span hiwhite>
    msp.FileSaveAs("c:documents and settingsrudidesktopreport1.mpp")

    msp.FileCloseAll()
    msp.DisplayAlerts = True
    msp.FileSaveAs("c:documents and settingsrudidesktopConSolReport.mpp", PjFileFormat.pjMPP, , , , , , , , , , , , , , , , , )
    msp.FileCloseAll()

    End Sub

    You could always repalce the hilighted area with array data when you loop the directory
    Jerry

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Collect file names for opening (VBA for Project 2003)

    OK, so Split is a method in Project VBA. But it is also a "standard" VBA function. If you use Split without prefix, you should get the standard function.

    You should be able to do something like the following

    Dim i As Integer
    Dim arr() As String
    Dim strList As String
    With dlg
    ...
    arr = Split(.Filename)
    For i = 1 To UBound(arr) ' skip item with index 0
    strList = ListSeparator & arr(i)
    Next i
    strList = Mid(strList, Len(ListSeparator) + 1)
    Application.ConsolidateProject FileNames:=strList
    End With

  14. #14
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Collect file names for opening (VBA for Projec

    Tx for the code, but i need a variable to store multiple mpp files...so that the ConsolidateProjects method can insert the ones the user selects. i cannot refer to them directly as plan1 and plan2...

    Here is what i have at the moment...help with any improvements...pleeeeeaase! TX

    <pre>Sub DialogTest()
    Dim dlg As New CommonDialog
    Dim myFileList As String
    Dim myFile As String
    Dim x As Variant
    Dim i As Integer

    With dlg
    .FileName = "*.mpp"
    .InitDir = "C:Windows"
    .Filter = "Project Files (*.mpp)|*.mpp|All Files (*.*)|*.*"
    .Flags = cdlHideReadOnly Or cdlFileMustExist Or _
    cdlPathMustExist Or cdlAllowMultiSelect
    If .OpenDialog() = True Then
    'MsgBox .FileName, vbInformation
    myFileList = .FileName
    x = Split(myFileList, " ")
    For i = 0 To UBound(x)
    MsgBox x(i)
    'ConsolidateProjects Filenames:=x(i), _
    NewWindow:=False, HideSubtasks:=True
    Next i
    Else
    MsgBox "No file(s) selected", vbInformation
    End If
    End With
    Set dlg = Nothing
    End Sub</pre>

    Regards,
    Rudi

  15. #15
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Collect file names for opening (VBA for Project 2003)

    Hans,

    I have just seen your reply and it looks good. I am glad to say that I was not too far off from what you have given...Somewhere inbetween the true code lies!!! <img src=/S/yep.gif border=0 alt=yep width=15 height=15>
    I will test it and see it it works..
    Tx
    Regards,
    Rudi

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •