Specify path to user folder in VBA for both Windows and Mac

Question

I am writing a tool which generates a file from Excel using VBA. The generated file is written to a folder, if it exists, in the user's Documents folder.

e.g. C:\Users\<username>\Documents\Some Folder\

If the last folder doesn't exist, then VBA creates it. I use the following line to ensure that the location of the folder works for different Windows users distributed in the organisation:

If Len(Dir(Environ("USERPROFILE") & "\Documents\Some Folder", vbDirectory)) = 0 Then
    MkDir Environ("USERPROFILE") & "\Documents\Some Folder"
End If
Open Environ("USERPROFILE") & "\Documents\Some Folder\" & "file.php" For Output As #1
Print #1, output
Close

My issue now though is that I also have to cater for Mac OSX users. At present I don't have access to a Mac to test, but I am assuming the above won't work.

What could could I use to point to the sub-folder in the user's documents, and how can I include code which conditionally uses either the Windows line or the Mac line?

Answer 1

You have two options:

1. Use Application.PathSeparator... See the MSDN here.

Code:

Dim PS as String
PS = Application.PathSeparator
If Len(Dir(Environ("USERPROFILE") & PS & "Documents" & PS & "Some Folder", vbDirectory)) = 0 Then
    MkDir Environ("USERPROFILE") & PS & "Documents" & PS & "Some Folder"
End If
Open Environ("USERPROFILE") & PS & "Documents" & PS & "Some Folder" & PS & "file.php" For Output As #1
Print #1, output
Close

2. Create an IF THEN to check for OS

Code:

Sub SaveDoc()
    Dim wksSheet As Worksheet

    If InStr(1, Application.OperatingSystem, "Windows") > 0 Then
      'Code with Windows Path Separator
      Exit Sub

    Else
      'Code with Mac Path Separator
      Exit Sub
    End If

End Sub