Using a MAC, print a Word document using a macro in Excel


I am using a MAC and I'm trying to create a macro that will print a Word document from my Excel worksheet. The user does not need to see the word document, they just need to print it.

After looking through some of the previous questions answered in this forum I managed to write some code that worked on my PC at home.

However, I changed the file path and filename and copied the code to my MAC that I am using at work and the code no longer seems to work. I wonder if the code has to be different when using a MAC?

I am using Microsoft Excel for Mac 2011, version 14.6.8.

ans = MsgBox(Prompt:="Document 1", Buttons:=vbYesNo, Title:="Print")
If ans = vbYes Then
    Dim objWord
    Dim objDoc
    Set objWord = CreateObject("Word.Application")
    'Enter filename and path here
    Set objDoc = objWord.Documents.Open("/Volumes/.../Document 1.docx")
    objWord.Visible = False
End If

Sometimes the code gets stuck at CreateObject("Word.Application") and sometimes the code get stuck where I have written the file path and filename.

I'm not entirely sure that I have written the file path correctly..?

Any help would be much appreciated.

Show source
| vba   | ms-word   | excel-vba-mac   | printing   | word-vba-mac   2016-10-05 12:10 1 Answers

Answers ( 1 )

  1. 2016-10-05 12:10

    Is your file on a network?

    Use that code in Word VBE to get the path of your file in the immediate window :

    Sub get_path()
       Debug.Print ActiveDocument.Path
    End Sub

    Try this function to test if the file exists (not sure how to make this work on Mac...) :

    Public Function File_Exist(sFilePath As String) As Boolean
        Dim sProv As String
        On Error GoTo ErrorHandler
            sProv = Dir(sFilePath, vbDirectory)
            File_Exist = (sProv <> "")
        On Error GoTo 0
        Exit Function
        MsgBox Prompt:="Error on test file= " & sFilePath & vbCrLf & Err.Number & vbCrLf & Err.Description
    End Function

    And your code with some improvements :

    ans = MsgBox(Prompt:="Document 1", Buttons:=vbYesNo, Title:="Print")
    If ans = vbYes Then
        Dim objWord As Object
        Dim objDoc As Object
        Dim sFilePath As String
        ''If the file is a network, you should start the path with //
        sFilePath = "New_path_from_Word_immediate_window"
        On Error Resume Next
        Set objWord = GetObject(, "Word.Application")
        If Err.Number <> 0 Then
            Set objWord = CreateObject("Word.Application")
        End If
        On Error GoTo 0
        objWord.Visible = False
        'Enter filename and path here
        'If File_Exist(sFilePath) Then
            Set objDoc = objWord.Documents.Open(sFilePath)
            'MsgBox "File doesn't exist!" & vbCrLf & sFilePath, vbCritical + vbOKOnly
        'End If
    End If
◀ Go back