Modify this Query to work with a Mac RRS feed

  • General discussion

  • I have a simple, straight forward function in Word (but it can work in just about any Office application). The function reads a string within a text file to return a complete path that I will use later in the code. I'm trying to make this work on a Mac, but I know very little about them. Can someone with Mac knowledge pimp my query so it'll work in Excel for Mac? Feel free to make any modifications, suggestions, alternate ideas, whatever you think will help. 

    'Before using this function, create a new folder on the C drive and name it ClientDB
    'Put a text file in the new folder and name it dbpath.txt
    'In the text file enter the following line (no quotes or anything else):
    'dbPath: C:\DatabasePath\Database.accdb
    'Save the file and close it

    Sub WhereIsMyDatabase()
    msgbox GetDBPath
    End SUb

    Function GetDBPath()
        Dim myfile As String
        Dim textline As String
        Dim text As String
        myfile = "c:\ClientDB\dbpath.txt"
        Open myfile For Input As #1
        Do Until EOF(1)
            Line Input #1, textline
            text = text & textline
        Close #1
        GetDBPath = Trim(Mid(text, InStr(text, ": ") + 1, 1000))
    End Function

    Thursday, June 9, 2016 7:03 PM

All replies

  • Actually you're not far off. The trick is how file paths are formatted on a Mac. In VBA, you can actually use HFS or POSIX style paths; either one will work for your "myfile=" string. discusses path styles

    You can also use the MacScript function in VBA to pull an AppleScript function to get a path. So rather than hard-coding the full path, let's say that the dbpath.txt file is going to be on the user's desktop. You could hard-code the full path like so (either option works identically, change "username" to the actual user folder):

    myfile = "Macintosh HD:Users:username:desktop:dbpath.txt"
    myfile = "/Users/username/desktop/dbpath.txt"

    You could also use a bit of AppleScript to determine the path to the current user's desktop folder, which is of course helpful if you need this code to run on multiple machines without hard-coding the path:

    myfile = MacScript("return (path to desktop folder) as text") & "dbpath.txt"

    In this case, the myfile variable will end up looking identical to the first example above, HFS style path.

    So with that one code change, everything works fine. The second change is of course the contents of your dbpath.txt, it needs to have a path in a useable format as well. But whatever string is in there, gets returned by your GetDBPath function.

    Sean Franklin / Franklin Consulting LLC /

    Friday, July 1, 2016 3:35 AM
  • Thanx for the info.
    Wednesday, July 13, 2016 12:15 PM