GetOpenFilename to Default Directory

[Excel Jr]

Hello,

I am trying to update my code to open the dialogue box to my desktop. Not quite sure how to do this with the GetOpenFileName method... I did some googling, and tried the following, but no luck. Any advice appreciated.

    Dim reconFile As String

    ChDrive "C:\"
    ChDir "C:\Users\Public\Desktop\"

    reconFile = XL.Application.GetOpenFilename(FileFilter:="Microsoft Excel Workbooks (*.xls;*.xlsx;*.xlsm),*.xls;*.xlsx;*.xlsm", _
    Title:="Select reconciliation file you wish to update")

 

[GWteB]

@johnnyL, your assumption is wrong.
The Outlook.Application object doesn't have a member with the name FileDialog (so your code will not work).
That's why I wrapped it in a separate function and did qualify al members explicitly.
The additional BuildProperFolderPath function ensures that the string involved ends with a (just one!) backslash character.
That way one doesn't have to check each time whether a folder string ends with a "\" or not when composing a path to a certain file.
In this case it's used to end up in that folder without having the file name: input box of the dialog populated with the name of the desired folder (which imo makes no sense ...).

 

[johnnyL]

@GWteB You said my code wouldn't work and that leaves me confused. Nothing new there.

You suggested to the OP to add a couple of functions and then replace the following:

    reconFile = XL.Application.GetOpenFilename(FileFilter:="Microsoft Excel Workbooks (*.xls;*.xlsx;*.xlsm),*.xls;*.xlsx;*.xlsm", _
    Title:="Select reconciliation file you wish to update")

with:

tempSaveLocation = "C:\Users\" & Environ("UserName") & "\Downloads\"
reconFile = PickReconciliationFile(tempSaveLocation, XL)

Which results with a full file path to the file chosen saved into the variable 'reconFile'.


I suggest replacing:

    reconFile = XL.Application.GetOpenFilename(FileFilter:="Microsoft Excel Workbooks (*.xls;*.xlsx;*.xlsm),*.xls;*.xlsx;*.xlsm", _
    Title:="Select reconciliation file you wish to update")
    
    tempSaveLocation = "C:\Users\" & Environ("UserName") & "\Downloads\"

with:

    With Application.FileDialog(msoFileDialogFilePicker)
        .Filters.Clear
        .Filters.Add "Excel Files", "*.xls; *.xlsx; *.xlsm", 1
        .Title = "Select reconciliation file you wish to update"
        .AllowMultiSelect = False
''        .InitialFileName = "C:\Users\" & Environ("UserName") & "\Desktop"
        .InitialFileName = "C:\Users\" & Environ("UserName") & "\Downloads"
'
        If .Show = True Then reconFile = .SelectedItems.Item(1)
    End With

Which results with a full file path to the file chosen saved into the variable 'reconFile'.


So can you please explain what I am missing? It seems to yield the same result.

 

[GWteB]

@johnnyL, your assumption is wrong.
The Outlook.Application object doesn't have a member with the name FileDialog (so your code will not work).

The OP is running his code in Outlook VBA, that's what you are missing

 

[johnnyL]

Dang it!
I don't see where that was stated. Thank you for the clarification though @GWteB!