Results 1 to 3 of 3

Thread: Converting file path on Excel for Mac
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Aug 2016
    Posts
    151
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Converting file path on Excel for Mac

    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?

  2. #2
    Board Regular steve the fish's Avatar
    Join Date
    Oct 2009
    Location
    Midlands, UK
    Posts
    7,649
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Converting file path on Excel for Mac

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

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

  3. #3
    Board Regular
    Join Date
    Aug 2016
    Posts
    151
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Converting file path on Excel for Mac

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

    Thank you!

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •