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

Question

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
    objDoc.PrintOut
    objWord.Quit
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
    
    ErrorHandler:
        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)
            objDoc.PrintOut
            objDoc.Close
        'Else
            'MsgBox "File doesn't exist!" & vbCrLf & sFilePath, vbCritical + vbOKOnly
        'End If
    
        objWord.Quit
    End If
    
◀ Go back