GetOpenFilename in a Loop

I’d like your opinion on something. I have this workbook that puts a Favorites item on the File menu (similar to the Work menu in Word). My goal was to have an easy (read: keyboard only) way to open some frequently used files without relying on the MRU. I’m trying to keep this simple by limiting it to menu commands and not having userforms or some other unnecessarily complicated user-interface.

On to my specific question. If the user (me) selects a file from the Favorites list that doesn’t exist, the app gives them options. One option is to go find the file using the GetOpenFilename method. If the user clicks Cancel on the GetOpenFilename dialog, I want to loop back around to the message box. I’ve accomplished this with a Boolean variable and a Do Loop, but it doesn’t seem very elegant.

I’d like to hear how you have or would do it, although I’m happy to hear any criticism you have about any of the code. If you want to see the code in context, you can download Favorites.zip. Be warned that this workbook is not ready for prime time. Here’s the code in question:

Sub OpenFavorite()
Dim sCap As String
Dim wb As Workbook
Dim lResp As Long
Dim sPrompt As String
Dim sFile As String
Dim bCancelFileOpen As Boolean

sPrompt = "Workbook not found" & vbCrLf & vbCrLf & _
"Click Yes to find the workbook yourself, No to remove the item from the list."

With CommandBars.ActionControl
'Try to open the file
sCap = HandleAmp(Right(.Caption, Len(.Caption) - 3), False)
On Error Resume Next
Set wb = Workbooks.Open(sCap)
On Error GoTo 0

Do 'Loop until Not bCancelFileOpen
bCancelFileOpen = False 'Initialize
If wb Is Nothing Then 'if file not found
lResp = MsgBox(sPrompt, vbYesNoCancel, "File Not Found")
Select Case lResp
Case vbYes
sFile = Application.GetOpenFilename(",*.xls")
'if user cancels, loop around and ask again
If sFile = "False" Then
bCancelFileOpen = True
Else
DeleteCurrent HandleAmp(sCap)
Set wb = Workbooks.Open(sFile)
AddCurrent
End If
Case vbNo
DeleteCurrent HandleAmp(sCap)
End Select
End If
Loop Until Not bCancelFileOpen
End With

End Sub

3 thoughts on “GetOpenFilename in a Loop

  1. I would probably just use a plain Do Loop (no until/while), with an Exit Do after AddCurrent and another after the end of Case vbNo. I can’t imagine that it makes much difference.

    – Jon

  2. I’d do it the Exit Do method, but there’s nothing wrong with your approach (probably more wrong with an Exit Do approach)

    One thing – I believe that GetOpenFilename actually returns a Variant.
    If Cancel is pressed variant is boolean.
    TypeName(sFile) = “Boolean”

    So if you made sFile a Variant, you could do the following:
    If sFile = False Then

    Only advantage is that you can now load a filename of “False” – yay.

  3. GetOpenFileName does return a variant. You can check

    If vFile = “False” Then

    if MultiSelect is false, because the response is always interpretable as a string.

    You can set MultiSelect = True to return an array of file names to open, in which case vFile is type Variant(), or Boolean if canceled, and you need to test TypeName(vFile).

    – Jon


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.