Glitchy GetOpenFilename method

Question

thread cross posted at Ozgrid at http://www.ozgrid.com/forum/showthread.php?t=145567

Working in XL07 Enterprise edition for home use. 

Why oh why does my GetOpenFilename statement result in the stupid file opening instead of waiting for me to take the string it's supposed to return and use that with a workbooks.open statement?  WHY?

Here is code that on my home laptop running aforementioned XL07 repeatedly results in the completely wacky result.  When I run it on my pc at work through remote portal, it works exactly the way all the documentation to be found says it should work - it returns a STRING holding the path and file name user selected.

Sub test()

    Dim wb As Workbook

    Dim f

    f = Application.GetOpenFilename()

    Set wb = Workbooks.Open(f)

End Sub

Slavish adulation will rain on whoever can provide a clue so I can move on with my project.

Answer 1

Alright, well, with no further suggestions forthcoming, I've resorted to taking a different approach, using the application dialog filepicker.  I'll post that against the off chance someone else might experience the same thing.  Many thanks to OssieMac and Chip for pitching in.

Sub test()

    Dim wb As Workbook

    With Application.FileDialog(msoFileDialogFilePicker)

        .Filters.Add "Excel Files", "*.xls"

        .AllowMultiSelect = False

        .Show

        If .SelectedItems.Count = 0 Then

            Exit Sub

        End If

        Set wb = Workbooks.Open(.SelectedItems(1))

    End With

End Sub

Answer 2

Chip, I do believe you are absolutely correct that what you're showing SHOULD work.  I'm guessing you may not have seen my reply to OssieMac.  The problem is that the selected file is getting opened immediately upon execution of the line "FName = Application.GetOpenFilename()".  It doesn't wait around for any if/then tests I might want to apply to it.  And I truly do understand what GetOpenFilename is SUPPOSED to return.  Unfortunately the command is NOT behaving as the object model references say it will.

I can temporarily accept that it does this on my home machine (running windows 7), but functions as intended at work (still on XP).  But when my company migrates to Windows 7 in a couple of months, I'm concerned that a functional routine based on its outcome could cease to work for my 30+ users, and our ability to produce quotes will come to a screeching halt.

Answer 3

OssieMac, thanks for that.  I did already know what each statement accomplishes.  What I am trying to describe is that, if I step through the code one line at a time, the line:

f = Application.GetOpenFilename

is the line that results in the file opening.  Then when execution gets to the set statement, it is RE-opening the file.  Essentially the GetOpenFilename method is NOT doing at all what every bit of available documentation says it will do.  I could delete the set wb statement entirely, and the file would be open.  However it doesn't do this on my work pc, only at home.  I've gone so far as to completely reinstall Office. 

If you have any other insight I'd love to hear it.  Or anyone else who has a clue?

Answer 4

Code like the following should do what you want:

    Dim FName As Variant

    Dim WB As Workbook

    FName = Application.GetOpenFilename()

    If FName = False Then

        Debug.Print "user cancelled"

    Else

        Debug.Print "file selected: " & FName

        Set WB = Workbooks.Open(FName)

    End If

GetOpenFileName returns a Boolean False if the user cancels the open dialog or a String file name of the complete path to the selected file.

---

Cordially, Chip Pearson Microsoft MVP, Excel Pearson Software Consulting, LLC www.cpearson.com

Answer 5

You are opening the file with this line of code

 Set wb = Workbooks.Open(f)

f actually holds the file name as follows.

Sub test()

    Dim wb As Workbook

    Dim f

    f = Application.GetOpenFilename()

    If f <> False Then

      MsgBox "File to open = " & f

    Else

      MsgBox "User did not select a file"

    End If

    'Following line opens the selected file

    'Set wb = Workbooks.Open(f)

End Sub

---

Regards, OssieMac