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
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 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?
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!