Trying to return value from xl macro using msoFilePicker

[Gregory_Davis]

Running the macro below, and I want to return the full path returned by ms FileDialog so that the next action can open it

I am an absolute newbie, I suspect that i need to define the macro output as some kind of enumerated filed, but I really dont understand how to do this

Regards Greg

Sub FileDialoguePicker(InputFile)

With Application.FileDialog(msoFileDialogFilePicker)
    .AllowMultiSelect = False
    .Filters.Clear
    .Filters.Add "Excel Files", "*.xls*;*.csv", 1
    .Title = "Select an Excel file to upload into S21 "
    InputFile = ""
    If .Show Then InputFile = .SelectedItems.Item(1)
End With

End Sub

[Unknown (Ronan van Stokkom)]

Hi @Gregory_Davis,

Yes, the return value of the ‘FileDialog(msoFileDialogFilePicker)’ method is an enumerable.
However, with the “.SelectedItems.Item(1)” will select the first and only item in the enum. It is the only one since you have “.AllowMultiSelect” set to false.

I tested you code and for me it works. Nonetheless, I think this should be a function. Try the following code:

Option Explicit

Sub Main()

Workbooks.Open (FileDialoguePicker)

End Sub


Function FileDialoguePicker() As String

Dim InputFile As String

With Application.FileDialog(msoFileDialogFilePicker)
    .AllowMultiSelect = False
    .Filters.Clear
    .Filters.Add "Excel Files", "*.xls*;*.csv", 1
    .Title = "Select an Excel file to upload into S21 "
    InputFile = ""
    If .Show Then InputFile = .SelectedItems.Item(1)
End With

FileDialoguePicker = InputFile

End Function

I hope this answers your question ^^

[Gregory_Davis]

Thank you for your remarkably quick response!

I actually explored the option to run the macro as a function,
I went with the subroutine because I envisage that the department I work in will end up with many projects, each related to a manual data entry task in our very outdated ERP.
If seems to me that it would be desirable to more precise identify what data entry task was about to be run, and to specify a default starting directory dependent on the task in hand (passing these attributes as input parameters)
It was really mostly a learning exercise for me.
I have discovered that I am a slow learner…
Is it possible to implement my preferred solution?

Regards Greg

[Unknown (Ronan van Stokkom)]

Hi @Gregory_Davis,

Subroutines cannout return values. They only have input arguments. You realy want to use a Sub and have it return a varlue, the only option would be using Global variables. That is the difference between functions and subs.

And I really liked your syntax within the With statement. So don’t be to harsh on yourself, you’re doing great!