Open Folder in VBA Mac

[JamesPW]

I'm kinda embarrassed to ask this question but I've spent an hour doing searches and I haven't found the answer.
Anyway- Running VBA on a macbook pro w/ Big Sur.
I have a macro that takes the open excel file and copies it to another ("Backups") directory with a new filename that includes today's date.
The macro is names "MakeDatedBackup and thats what it does.
I know the path where I put it and it's always the same directory.

What happens is after a while the Backups directory gets cluttered with backup files.
What I want to happen is after the copy is made and put in the backup directory I need code to pop open Finder at the Backups directory, showing the user all the files in that dir. [The macro might end here]. The user then goes thru the files and manually deletes the ones he doesn't want anymore just as if he was in Finder (he IS in Finder). Then he closes Finder and everything's back to normal. This would the last function of the macro.

I'm thinking it's a simple one line command but I can't figure it out.

 

[smozgur]

I'm thinking it's a simple one line command but I can't figure it out.

It was a simple one-line command before, but not anymore

There is (was) a beautiful(!) function called MacScript in VBA (Office for Mac 2011). However, it is deprecated due to restrictions of the sandbox on Mac, and although It can do some simple stuff like displaying messages, etc., it cannot "tell" what to do to other applications Like Finder anymore. So, even you have 2011, it won't let you open a folder in Finder.

The good news, now we have AppleScriptTask since Office for Mac 2016. But it is not a one-line process. If you keep reading the following, then I can give you a sample of how to make it work.

Step1: You need to create an AppleScript file to call in VBA. This AppleScript file must be saved in a specific location for the application that you are going to "call" it. It is Excel in this case. And the path for the AppleScript files that will be called by Excel is:
~/Library/Application Scripts/com.microsoft.Excel/

Launch Script Editor, create a new document, copy and paste the following code in this new script, and save it - I named it as MyExcelScripts. The full name will be MyExcelScripts.scpt that we are going to use to call it in VBA.

on openfolder(myPath)
    set myPath to myPath as POSIX file
    tell application "Finder"
        open myPath
        Activate
    end tell
end openfolder

openfolder is similar to a function that is called as "script handler".
myPath is the parameter that we will provide as the path to be opened in Finder.

Step2: Now we have the script saved in the expected place. We can go to VBE, create a new module, and copy and paste the following code into this new module.

Sub openSpecificFolderInFinder()
    AppleScriptTask "MyExcelScripts.scpt", "openfolder", "/Users/smozgur/desktop"
End Sub

AppleScriptTask function takes three parameters;

1. The name of the script file in the caller application's script folder (~/Library/Application Scripts/com.microsoft.Excel/).
2. The name of the script handler.
3. The parameter string.

Once we provide these parameters, which you need to change the path to test, we can run the VBA code, then it should work as expected and open the requested path in Finder.

It is surely not a one-liner, and I can hear that anybody reading my post would say that "why this much difficult?". It is the Apple security, and it might sound silly, but honestly, it was silly before, giving all permissions to destroy my computer to a one-liner command.

Bonus material: Display a message dialog:
Add the following code to the same AppleScript.

on saysomething(paramString)
    display dialog paramString with icon caution
end saysomething

And execute the following VBA code:

Sub saySomethingForMe()
    AppleScriptTask "MyExcelScripts.scpt", "saysomething", "AppleScript is cool!"
End Sub

And just to mention it with a sample here, the following is the MacScript equivalent of the same process above (one-liner version). Although it is deprecated, this simple task is still possible with MacScript.

Sub saySomethingForMeWithMacScript()
    MacScript "set paramString to ""AppleScript is Cool!""" & vbNewLine & _
                    "display dialog paramString with icon caution"
End Sub

Hope this helps.