• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 581
  • Last Modified:

VBA woes in Office 2011 for Mac

I've used VBA since Office 2007 for Windows came out.  I have a client who needs a solution in Office 2011 for Mac, and I've been a PC guy all my life so I'm struggling with some basic concepts.

Filename and path syntax is frustrating me.  Trying this simple test to get to a file I know is there but it says the file does not exist.  Is something wrong with my path?

file2open = "/Library/Application Support/HPC_Content/Animals/Cat.tif"
ActivePresentation.Slides(2).Shapes.AddPicture fileName:=file2open, linktofile:=msoFalse, savewithdocument:=msoTrue, Left:=100, Top:=100

Also, can you point me to how I would open a file picker dialog in VBA for mac?

I'm sure I'll have lots more questions as I struggle through this transition.  Anybody know of a good resource for "Making the transition from VBA for Windows to VBA for Mac"?

thanks.
0
Bryce Bassett
Asked:
Bryce Bassett
  • 2
2 Solutions
 
Rgonzo1971Commented:
HI,

Here is a good source of information

http://www.rondebruin.nl/mac.htm

for example

Sub TestFile()
'First argument 1 = file and 2 = folder.
'Note: This macro uses the FileOrFolderExistsOnMac function.
    If FileOrFolderExistsOnMac(1, _
         "Macintosh HD:Users:YourUserName:Documents:YourFileName.xlsx") = True Then
        MsgBox "File exists."
    Else
        MsgBox "File does not exist."
    End If
End Sub

Regards
0
 
MacroShadowCommented:
<<Filename and path syntax is frustrating me.  Trying this simple test to get to a file I know is there but it says the file does not exist.  Is something wrong with my path?>>
There are some basic concepts you must know when dealing with file paths for mac.
Here is a good reference.

<<Also, can you point me to how I would open a file picker dialog in VBA for mac?>>
Function myGetOpenFileName(Optional sPath As String) As String
    Dim sFile As String
    Dim sMacScript As String

    If isMac Then 'mac
        If sPath = vbNullString Then
            sPath = "the path to documents folder"
        Else
            sPath = " alias """ & sPath & """"
        End If
        sMacScript = "set sFile to (choose file of type ({" & _
                     """com.microsoft.Excel.xls"", ""org.openxmlformats.spreadsheetml.sheet""," & _
                     """public.comma-separated-values-text"", ""public.text"", ""public.csv""," & _
                     """org.openxmlformats.spreadsheetml.sheet.macroenabled""}) with prompt " & _
                     """Select a file to import"" default location " & sPath & ") as string" _
                     & vbLf & _
                     "return sFile"
        Debug.Print sMacScript
        sFile = MacScript(sMacScript)
    Else    'windows
        sFile = Application.GetOpenFilename("CSV files,*.csv,Excel 2007 files,*.xlsx", 1, _
                                            "Select file to import from", "&Import", False)
    End If

    myGetOpenFileName = sFile
End Function

Function isMac() As Boolean
    isMac = Application.OperatingSystem Like "*Mac*"
End Function

Open in new window


<<Anybody know of a good resource for "Making the transition from VBA for Windows to VBA for Mac"?>>
You'll find some useful information on Ron de Bruin's site.
0
 
Rgonzo1971Commented:
Hi,

I forgot to put the function neede for the code above

Function FileOrFolderExistsOnMac(FileOrFolder As Long, FileOrFolderstr As String) As Boolean
'By Ron de Bruin
'30-July-2012
'Function to test whether a file or folder exist on a Mac.
'Uses AppleScript to avoid the problem with long file names
    Dim ScriptToCheckFileFolder As String
    ScriptToCheckFileFolder = "tell application " & Chr(34) & "Finder" & Chr(34) & Chr(13)
    If FileOrFolder = 1 Then
        ScriptToCheckFileFolder = ScriptToCheckFileFolder & "exists file " & _
                                  Chr(34) & FileOrFolderstr & Chr(34) & Chr(13)
    Else
        ScriptToCheckFileFolder = ScriptToCheckFileFolder & "exists folder " & _
                                  Chr(34) & FileOrFolderstr & Chr(34) & Chr(13)
    End If
    ScriptToCheckFileFolder = ScriptToCheckFileFolder & "end tell" & Chr(13)
    FileOrFolderExistsOnMac = MacScript(ScriptToCheckFileFolder)
End Function

Open in new window

0
 
Bryce BassettFreelance VBA programmerAuthor Commented:
Thanks, both.  I found some resources that said Mac OS no longer uses the colon to separate folders, but obviously it does.

Helpful resources.  The MacScript method is especially helpful.  I adjusted it to pick any file and not filter for certain types, but you pointed me in the right direction
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now