Open Folder in VBA Mac

JamesPW

New Member
Joined
Nov 4, 2005
Messages
45
Office Version
  1. 2019
Platform
  1. MacOS
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.
 

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

smozgur

BatCoder
Joined
Feb 28, 2002
Messages
1,352
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.

Code:
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.

VBA Code:
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.
Code:
on saysomething(paramString)
    display dialog paramString with icon caution
end saysomething

And execute the following VBA code:
VBA Code:
Sub saySomethingForMe()
    AppleScriptTask "MyExcelScripts.scpt", "saysomething", "AppleScript is cool!"
End Sub

1609187263247.png


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.
VBA Code:
Sub saySomethingForMeWithMacScript()
    MacScript "set paramString to ""AppleScript is Cool!""" & vbNewLine & _
                    "display dialog paramString with icon caution"
End Sub

Hope this helps.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,528
Messages
5,625,339
Members
416,096
Latest member
forevans

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top