Converting file path on Excel for Mac

mcomp72

Board Regular
Joined
Aug 14, 2016
Messages
160
I have written a Macro-Enabled Workbook on Windows, and am now trying to get it to work on Mac, on Excel 2011, 2016 & 2019. I'm new to Mac, so it's been a bit of a struggle, to say the least.

For this question, I'm asking about Excel 2019, because I haven't tested this part yet on Excel 2016 or 2011.

Part of my code asks the user to select a folder where they want to save a file. When the window opens, asking them where to save it, I would like the default folder to be the folder where the Workbook is located. I found some code online, and have modified it a bit to suit my needs. Unfortunately, it's not working correctly right now.

Code:
Dim MacFolderPath As String
Dim RootFolder As String
Dim scriptstr As String

RootFolder = ThisWorkbook.Path
Debug.Print RootFolder

scriptstr = "return posix path of (choose folder with prompt ""Select the folder you want""" & _
        " default location alias """ & RootFolder & """) as string"

MacFolderPath = MacScript(scriptstr)

In the immediate window, RootFolder equals: /Users/mattc/Desktop

The problem is on the MacScript line. It seems that because RootFolder is not a file path with a colon as the path separator (for instance, HD:Users:mattc:Desktop), it is resulting in an error on that line of code. The line of code DOES work if the RootFolder uses colons. But unfortunately, ThisWorkbook.Path doesn't return a value using the colons.

Is there a way to convert the path into the format that I need it in? Or can the MacScript string be rewritten to allow it to work with the path that I have?
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,137
Office Version
  1. 365
Platform
  1. Windows
Its simple to convert the slashes into colons. Based on what you have there try:

Code:
RootFolder = Replace(ThisWorkbook.Path, "/", ":")
 

mcomp72

Board Regular
Joined
Aug 14, 2016
Messages
160
Oh yes, of course. How could I forget about the Replace command?!

Thank you!
 

Watch MrExcel Video

Forum statistics

Threads
1,114,002
Messages
5,545,439
Members
410,684
Latest member
LakTik
Top