Converting file path on Excel for Mac

[mcomp72]

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.

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?

 

[steve the fish]

Its simple to convert the slashes into colons. Based on what you have there try:

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

 

[mcomp72]

Oh yes, of course. How could I forget about the Replace command?!

Thank you!