Problems with running macros on Office for Mac

Question

Hey everyone,

I have a macro code written for me on Microsoft Excel 2010.

When I try running the macro on my Excel 2011 for mac I encounter some problems.

The action the macro is supposed to do is load images from a link and place them in the 'trim image' column.

It runs just fine on the PC but on the mac it gives me "run-time error '76' " (path not found)

My guess is that the Mac refers to file pathes in a different way. I would be grateful if someone here can tell me how to modify my code in order to find the files on the mac.

This is the yellow marked line by the Excel for mac:

if Dir(pixpath) <> vbNullString then

this is my code:

Public Sub PictureInCell()

Dim mypix As Shape

Dim rng As Range

Dim cellrng As Range

Dim pixpath As String

Dim lastrow As Integer

Dim refcol As Integer

refcol = 0

Dim ReportSheet As Worksheet

Set ReportSheet = ActiveSheet

lastrow = ReportSheet.Range("A65535").End(xlUp).Row

For i = 1 To 70

    If UCase(Trim(ReportSheet.Cells(1, i))) = UCase("TRIM IMAGE") Or UCase(Trim(ReportSheet.Cells(1, i))) = UCase("proto sketch") Then

        refcol = i

        Exit For

    End If

Next

If refcol = 0 Then

MsgBox "No Trim Image column found"

Exit Sub

End If

For i = 2 To lastrow

pixpath = ReportSheet.Cells(i, refcol)

If pixpath <> "" Then

    If Dir(pixpath) <> vbNullString Then

        On Error Resume Next

        Set rng = ReportSheet.Cells(i, refcol)

        Set mypix = ReportSheet.Shapes.AddPicture(pixpath, False, True, ReportSheet.Columns(rng.Column).Left, ReportSheet.Rows(rng.Row).Top, 100, 100)

        If Not (mypix Is Nothing) Then

         mypix.ScaleHeight 1, msoCTrue

        mypix.ScaleWidth 1, msoCTrue

            If mypix.Height > 409 Then

               Call mypix.ScaleHeight(409 / mypix.Height, msoFalse)

            End If

            If mypix.Width * 0.19 > 255 Then

                Call mypix.ScaleWidth(255 / (mypix.Width * 0.19), msoFalse)

            End If

            Set rng = ReportSheet.Cells(i, refcol)

            rng.ClearContents

            If rng.ColumnWidth < mypix.Width * 0.19 Then

                rng.ColumnWidth = mypix.Width * 0.19

            End If

            rng.RowHeight = mypix.Height

'            mypix.Top = ReportSheet.Rows(rng.Row).Top

'            mypix.Left = ReportSheet.Columns(rng.Column).Left

            Set mypix = Nothing

        End If

    End If

End If

Next

End Sub

Thanks a lot for your help!

Answer 1

Your VBA code looks properly formed and other than a missing declare of the "i" variable it compiles and appears to be fine. However, you did not show us how you are currently listing the path strings.

Office for the Mac uses the colon character as the path separator versus the backslash on a PC. So that is the first thing you must change in your path strings. The second thing you must change are the directory location names.

In all of my coding on the Mac I use the following to obtain the base directory strings and then add as appropriate any remaining subfolders. Note the use of colons (":").

Sub getPaths()

    Dim UserHome As String, UserLibrary As String, UserAppData As String

    Dim UserDesktop As String, UserDocuments As String, UserMovies As String

    Dim UserMusic As String, UserPictures As String, UserPreferences As String

    Dim UserTemplates As String

    UserHome = MacScript("return (path to home folder) as string")

    UserLibrary = UserHome & "Library:"

    UserAppData = UserLibrary & "Application Support:"

    UserDesktop = MacScript("return (path to desktop folder) as string")

    UserDocuments = MacScript("return (path to documents folder) as string")

    UserMovies = MacScript("return (path to movies folder) as string")

    UserMusic = MacScript("return (path to music folder) as string")

    UserPictures = MacScript("return (path to pictures folder) as string")

    UserPreferences = MacScript("return (path to preferences folder) as string")

    UserTemplates = Options.DefaultFilePath(wdUserTemplatesPath) & ":My Templates:"

End Sub

The above will work in all Mac Office applications (Word, Excel, PowerPoint) as long as you include a Reference to each application's Object Model in the VBE > Tools > References settings, which then allows the "wdUserTemplatesPath" property to work in your Excel or PowerPoint VBA code.

In case the above paragraph didn't make sense. Open the VBE (Visual Basic Editor) using the shortcut Option+F11 or FN+Option+F11 on some keyboards and go to Tools > References... and set it like this:

Hope this helps

Answer 2

How long is your path ?

When you test it with this is it working for you then ?

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

Answer 3

What is the contents of the cells containing the picture path?  If they contain slashes, as needed for Windows, it will not work on the Mac.