Collect file names for opening (VBA for Project 2003)

Topic

New Reply

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

Reply | Quote

Replies

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).

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

Doesn’t Project VBA recognize Split?

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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….?

Reply | Quote

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.

Reply | Quote

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???

Reply | Quote

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?

Reply | Quote

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!

Reply | Quote