• Collect file names for opening (VBA for Project 2003)

    Home » Forums » AskWoody support » Productivity software by function » Visual Basic for Applications » Collect file names for opening (VBA for Project 2003)

    Author
    Topic
    #433819

    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

    Viewing 0 reply threads
    Author
    Replies
    • #1021353

      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 (although they are in a Word document, they will work in any VBA dialect).

      • #1021355

        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

        • #1021356

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

          • #1021357

            Bummer….trying the other method now………..

          • #1021361

            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

            • #1021363

              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:

              "H:Project This.doc That.doc Other.doc"

              You can use Split to parse this string.

              evilgrin

            • #1021367

              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

            • #1021370

              Doesn’t Project VBA recognize Split?

            • #1021374

              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

            • #1021375

              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

            • #1021385

              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”)
              msp.ConsolidateProjects(“c:documents and settingsrudidesktopplan1.mpp”, , False)
              msp.ConsolidateProjects(“c:documents and settingsrudidesktopplan2.mpp”, , False)

              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

            • #1021391

              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

              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
            • #1021388

              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

            • #1021393

              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!!! yep
              I will test it and see it it works..
              Tx

            • #1021401

              I’ll hang back then, I was creating a rather over engineered app here blush

            • #1021404

              Here is the code that is working perfectly…..

              tx Hans for all the help….and Jezza for the encouragement…

              Sub DialogTest()
                  Dim dlg As New CommonDialog
                  Dim i As Integer
                  Dim arr() As String
                  Dim strList As String
                  
                With dlg
                  .FileName = "*.mpp"
                  .InitDir = "C:Documents and SettingsAdministratorMy Documents"
                  .Filter = "Project Files (*.mpp)|*.mpp|All Files (*.*)|*.*"
                  .Flags = cdlHideReadOnly Or cdlFileMustExist Or _
                              cdlPathMustExist Or cdlAllowMultiSelect
                  If .OpenDialog() = True Then
                  arr = Split(.FileName)
                  For i = 1 To UBound(arr)
                      strList = ListSeparator & arr(i)
                      strList = Mid(strList, Len(ListSeparator) + 1)
                      Application.ConsolidateProjects FileNames:=strList
                  Next i
                  Else
                    MsgBox "No file(s) selected", vbInformation
                  End If
                End With
                Set dlg = Nothing
              End Sub
              
            • #1021680

              Hi Hans,
              The code worked well on a PC last week running Win XP Prof. Now I am running it on Win 2000 and the .Flag statement is saying Variable not defined on the argument values…

              Is this due to the Win 2000 operating system? Any ideas?

              Tx

            • #1021681

              There shouldn’t be a difference – the code should work on all versions of Windows from Windows 95 and Windows NT 4 up.
              – Is this exactly the same file that did work on Windows XP?
              – If so, try removing the Or cdl… bits one by one to see if the problem disappears. Please report the result.

            • #1021689

              This is exactly the code that I ran on XP and Project 2003 last week. The only difference is its on Win 2000 and Project 2003.

              I removed the cdl… bits one at a time. All of them threw up a error of Variable not defined????? If I comment out the .Flags property, them the code runs, but I get the dialog that does not allow for multiple selecting.

              PS: When I step through the code, the arguments get pointed out immediately….it does not have to get to the line before it fails.

              WAIT A MOMENT….this tells me something…. OPTION EXPLICIT!!!

              This module has Option Explicit…checking for valiable declaration. If I comment out Option Explicit….it does not error…but the .Flags value is set to 0 and the dialog is the one that does not allow multiple select??? Weird?

              Hope you have an answer here….?

            • #1021696

              You should NOT comment out Option Explicit – that’s like hiding your head in the sand.
              Did you copy the basGlobals module into this Project file? It contains the definitions of the cdl… constants.

            • #1021700

              I did have all the modules yes.

              PS: I just tested the code without the option explicit….don’t worry…I am well aware of its importance. After all, I had a good teacher called Hans that wropped me over the fingers each time i did not use this or dim variables… (Maybe you know him???)

              I ran the code on Win XP on another workstation. The code worked great there again. Are you sure it is not a Windows version problem???

            • #1021703

              As I said, the code should work on all recent versions of Windows. To make sure, I have just now tested it on a Windows 2000 machine, and it ran without problems.

              Is it the same file you’re testing on XP and 2000, or different files with the same code?

            • #1021705

              Its the same file – and this is why I find it really strange that this is happening!!

              Hans, consider this thread resolved for now. The code was created for a collegue of mine, and although it was created on a WinXP PC, and tested on a Win 2000 PC…the code most probably will be used on Win XP PC’s where I did not experience any hassles with the code.

              Tx so much for your assistance!

    Viewing 0 reply threads
    Reply To: Reply #1021404 in Collect file names for opening (VBA for Project 2003)

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information:




    Cancel