Recently I have found one post on File Selector for windows to use in power query. I would suggest you to checkout that post before.
That post focus on Windows machines only.
In this post I am going to share code that can be used in both Mac and Windows. Thanks to Ron de Bruin for Apple script for Mac Version. Here is the complete code and you just need to copy paste this code and use PutSelectedFilePath sub.
Option Explicit
Private Sub PutSelectedFilePath(ToRange As Range)
ToRange.Cells(1).Value = GetSelectedFilePath("Select file:")
End Sub
'This will give the selected file path as string.
'Example call : GetSelectedFilePath("Select Correct CSV","*.csv")
Public Function GetSelectedFilePath(ByVal GivenTitle As String _
, Optional ByVal GivenFilter As String = "*.*") As String
If IsMacOS() Then
GetSelectedFilePath = GetSelectedFileOrFilesOnMac(GivenTitle, True)
Else
Dim SelectedFilePath As FileDialogSelectedItems
Set SelectedFilePath = GetSelectedFilesPathOnWindows(GivenTitle, GivenFilter, False)
If SelectedFilePath.Count = 0 Then
GetSelectedFilePath = vbNullString
Else
GetSelectedFilePath = SelectedFilePath.Item(1)
End If
End If
End Function
Public Function IsMacOS() As Boolean
Const WindowsIdentifierPattern As String = "*Windows*"
IsMacOS = Not (Application.OperatingSystem Like WindowsIdentifierPattern)
End Function
Private Function GetSelectedFileOrFilesOnMac(Prompt As String, IsOneFile As Boolean) As Variant
Dim MyScript As String
Dim MyFiles As String
On Error GoTo HandleError
If IsOneFile Then
MyScript = "set theFile to (choose file with prompt """ & Prompt & """" _
& " without multiple selections allowed) as string" & vbNewLine & _
"return posix path of theFile"
Else
MyScript = _
"set theFiles to (choose file with prompt """ & Prompt & """" _
& " with multiple selections allowed)" & vbNewLine & _
"set thePOSIXFiles to {}" & vbNewLine & _
"repeat with aFile in theFiles" & vbNewLine & _
"set end of thePOSIXFiles to POSIX path of aFile" & vbNewLine & _
"end repeat" & vbNewLine & _
"set {TID, text item delimiters} to {text item delimiters, ASCII character 10}" & vbNewLine & _
"set thePOSIXFiles to thePOSIXFiles as text" & vbNewLine & _
"set text item delimiters to TID" & vbNewLine & _
"return thePOSIXFiles"
End If
MyFiles = MacScript(MyScript)
If IsOneFile Then
GetSelectedFileOrFilesOnMac = MyFiles
Else
GetSelectedFileOrFilesOnMac = Split(MyFiles, Chr(10))
End If
Exit Function
HandleError:
GetSelectedFileOrFilesOnMac = vbNullString
End Function
'This will give the selected file path as string.
'Example call : GetSelectedFilePath("Select Correct CSV","*.csv",True)
Private Function GetSelectedFilesPathOnWindows(ByVal GivenTitle As String _
, ByVal GivenFilter As String _
, Optional ByVal IsMultiSelected As Boolean = False) As FileDialogSelectedItems
Dim FilePicker As FileDialog
Set FilePicker = Application.FileDialog(msoFileDialogFilePicker)
With FilePicker
.AllowMultiSelect = IsMultiSelected
.Title = GivenTitle
.InitialFileName = CurDir$()
.Filters.Clear
.Filters.Add "Filter : ", GivenFilter
.Show
Set GetSelectedFilesPathOnWindows = .SelectedItems
End With
End Function
How to use this code
- Copy paste this code into any module
- Create a public sub like below in the same module (Change Range address as you like)
Public Sub PlaceSelectedFilePath()
PutSelectedFilePath Range("B3")
End Sub
- Insert a button or shape in excel (Check out on how to do it)
- Assign button macro to “PlaceSelectedFilePath” sub.
- Now you can click that button/shape and it will work for both Mac and Win machines.
Here how it will look like once you click the button in Mac
