Converting file path on Excel for Mac

mcomp72

Active Member
Joined
Aug 14, 2016
Messages
275
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2011
Platform
  1. Windows
  2. MacOS
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?
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Its simple to convert the slashes into colons. Based on what you have there try:

Code:
RootFolder = Replace(ThisWorkbook.Path, "/", ":")
 
Upvote 0
Oh yes, of course. How could I forget about the Replace command?!

Thank you!
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,558
Latest member
aivin

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
Back
Top