Tag: VBA Automation

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

Excel VBA Programming – The Complete Guide

Excel

Excel VBA & Scripts

Formulas

Power Query

Python

Outlook + Excel VBA

Others

Free Guide

The ONE Critical Excel Skill You Need

Tag: VBA Automation

Win and MAC Compatible File Selector

Update VBA Code From Github(Single Code Source).

Replace Space From Power Query Step Name Using VBA

Excel VBA Programming – The Complete Guide

Free Guide

The ONE Critical Excel Skill You Need

Stay Ahead with Weekly Insights!

Dive into Excel, Al and other essential tech news:

carefully crafted for the modern professional.