用于Mac的Excel VBA更改 [英] Excel VBA alteration for MAC

问题描述

我有以下脚本可以在Windows环境中完美运行.保存excel文件后，它将打开分配的word文档，更新链接，然后将其另存为PDF，保存在以C6单元格内容命名的同一文件夹中.不幸的是，我也需要它在MAC上工作.我将路径从"c:\ Prop"更改为"/Prop/"(我在Mac HD的根目录上创建了一个名为Prop的文件夹)，并且可以正常工作到打开单词的位置，但是随后得到了不受支持的功能消息...任何人都知道MAC office 16的不同功能是什么?

I have the following Script which works perfectly in a windows environment. Upon saving the excel file it will open the assigned word document, update the links and then save it as a PDF in the same folder named off the C6 cell contents. Unfortunately, I need it to work on a MAC as well. I changed the path from "c:\Prop" to "/Prop/" (I created a folder on the root of the mac HD called Prop) and it works up to the point where is opens word, but then I get an unsupported function message... anyone know what the different function is for MAC office 16???

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim objWord, objWordDoc, objField As Object
    Dim boolSuccess, boolUpdated As Boolean
    Dim strFolderPath, strWordFileName, strPdfFileName, strOutput As String

    strFolderPath = "c:\Prop\"
    strWordFileName = "Prop.docm"
    strPdfFileName = ActiveWorkbook.Sheets(1).Cells(6, 3).Value & ".pdf"
    strOutput = "There are problems with updating the next fields:" & vbCrLf
    boolSuccess = True

    On Error GoTo Error
        Err.Clear

        Set objWord = CreateObject("Word.Application")
        Set objWordDoc = objWord.Documents.Open(strFolderPath & strWordFileName)

        If Not objWordDoc Is Nothing Then
            For Each objField In objWordDoc.Fields
                boolUpdated = objField.Update
               If Not boolUpdated Then
                    boolSuccess = False
                    strOutput = strOutput & "Field" & CStr(objField.Index) & vbCrLf
                End If
            Next
            objWordDoc.Save
            objWordDoc.ExportAsFixedFormat strFolderPath & strPdfFileName, 17
            objWordDoc.Close
            If boolSuccess Then
                MsgBox strWordFileName & " was updated successfully and " & strPdfFileName & " was saved in " & strFolderPath
            Else
                MsgBox strOutput
                MsgBox strWordFileName & " was updated with problems and " & strPdfFileName & " was saved in " & strFolderPath
            End If
        End If
Error:
    If Err.Description <> "" Then
        MsgBox "Error: " & Err.Description, , "Error"
    End If

    objWord.Quit

    Set objWordDoc = Nothing
    Set objWord = Nothing
End Sub

推荐答案

CreateObject在Office 2011的Mac上可以正常工作...我认为问题是路径字符串.在Mac上，路径是不同的.我相信应该是这样的:

CreateObject works fine with me on mac with office 2011... I think the problem is the path string. On mac paths are different. I believe it should be something like this:

"OSX:Local:Prop:Prop.docm"，或者如果您希望将其分开:

"OSX:Local:Prop:Prop.docm" or if you want it separated:

strFolderPath = "OSX:Local:Prop:"
strWordFileName = "Prop.docm"

告诉我它是否有效:)

这篇关于用于Mac的Excel VBA更改的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！