Application.GetOpenFilename in MS Project

[jpjones23]

Has anyone managed to the standard file open window to display via VBA in MS Project? I have no problems with MS Word or MS Excel but Project throws up an error stating that the object doesn't support this method. So much for consistency across MS apps. ;-)

Hoping,
Jeff

 

[TonyJollans]

Hi Jeff,

Consistency across an MS Application Suite? Whatever will you ask for next?

Most Office apps do NOT support the GetOpenFileName Method. If you have XP or 2003 I think the Application.FileDialog Object is available in all apps although I don't know about Project particularly. If not I think you will have to use API calls - I can post code if you need it.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at [url=http://www.vbaexpress.

 

[jpjones23]

LOL. Thank you Tony.

I use the following in MS Excel "workProducts = Application.GetOpenFilename(Title:="Add Work Product(s)...", MultiSelect:=True)" (no quotes)and it works just fine.

In MS Word I use the following.

Dim FOrecord As Object: Set FOrecord = WordBasic.DialogRecord.FileOpen(False) 'Define a dialog record "FOrecord"
WordBasic.CurValues.FileOpen FOrecord 'Get the current values
FOrecord.Name = "*.doc" 'Place "*.doc" in the File Name box
On Error GoTo -1: On Error GoTo trap
'Go to the "trap" label if user the chooses Cancel
WordBasic.Dialog.FileOpen FOrecord 'Display the Open dialog box
WordBasic.FileOpen FOrecord 'Carry out FileOpen with changes
GoTo bye 'Go to the "bye" label
trap: 'Label for On Error Goto statement
WordBasic.MsgBox "PMPR comment merge request has been cancelled." 'Error message
canFlg = 1
Exit Sub
bye: 'Label for Goto statement
Subfile$ = WordBasic.[FileName$]() 'Returns the name of the SUB file

~~ end of code ~~

Both are cool. However, I've been forced use just an inputbox with MS Project.

I maintain various MS Office based apps across multiple operating systems with different versions of MS Office. There are potentially many hundreds of users. I've tried to keep things simple solely because of the differences across platforms.

It would be fun to see the API Calls but I don't know if I'll use them. I'll have to run some tests. I do know that the macros work on Win98, W2K and XP operating systems with various versions of MS Office so XP and Office 2003 aren't the answer to the MS Project opportunity. Oh well......

Jeff

 

[TonyJollans]

Hi Jeff,

Here is some API code - do as you will with it - stare wide eyed at it, use it, criticise it - it's up to you ..

1. Add these declarations at the start of your module, before the first procedure ..

[blue]Declare Function GetOpenFileName _
        Lib "comdlg32.dll" _
        Alias "GetOpenFileNameA" _
            (pOpenfilename As OPENFILENAME) _
        As Boolean

Private Type OPENFILENAME
    lStructSize         As Long
    hwndOwner           As Long
    hInstance           As Long
    lpstrFilter         As String
    lpstrCustomFilter   As String
    nMaxCustFilter      As Long
    nFilterIndex        As Long
    lpstrFile           As String
    nMaxFile            As Long
    lpstrFileTitle      As String
    nMaxFileTitle       As Long
    lpstrInitialDir     As String
    lpstrTitle          As String
    flags               As Long
    nFileOffset         As Integer
    nFileExtension      As Integer
    lpstrDefExt         As String
    lCustData           As Long
    lpfnHook            As Long
    lpTemplateName      As String
End Type[/blue]

2. Add this function somewhere in your module ..

[blue]Function FileName()

Dim ProjectFile        As OPENFILENAME

With ProjectFile

    .lStructSize = Len(ProjectFile)
    
    .hwndOwner = 0
    .hInstance = 0
    .lpstrFile = Space$(254)
    .nMaxFile = 255
    .lpstrFileTitle = Space$(254)
    .nMaxFileTitle = 255
    .lpstrInitialDir = Chr$(0)
    .flags = 0
    [green]' Following is the Dialog Title[/green]
    .lpstrTitle = "Select Project File"
    [green]' Following is a filter for the type of file -
    ' (a) a literal which is displayed in the [i]Files of Type[/i] box, and
    ' (b) a filter restricting the files shown
    ' Change these according to your needs.[/green]
    .lpstrFilter = "Microsoft Project Files (*.mp*)" + Chr$(0) + "*.mp*" + Chr$(0)

End With

If GetOpenFileName(ProjectFile) Then
    FileName = Trim$(ProjectFile.lpstrFile)
Else
    Exit Function
End If

End Function[/blue]

Finally call the function when you need it, for example ..

[blue]OpenFileName = FileName()[/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at [url=http://www.vbaexpress.

 

[jpjones23]

Thank you Tony. The function works like a charm. I just need to keep my fingers crossed that people using it have the correct object libraries. For example, I developed the macro using the MS Project 10.0 Object Library. Today a user couldn’t get the macros to work. In looking at th problem via NetMeeting I saw that she was using the 9.0 version of the MS Project Object Library.

So much for consistency, again......

Thank you again,
Jeff