Excel VBA代码在Mac上工作，创建PDF功能 [英] Excel VBA code to work on Mac, Create PDF Function

问题描述

我编码了以下功能。但是，我无法让它在办公室Mac上工作。我不知道如何找到EXP_PDF.DLL mac等价物的程序

 函数Create_PDF（Myvar As Object，FixedFilePathName As String， _ 
 OverwriteIfFileExist As Boolean，OpenPDFAfterPublish As Boolean）As String 
 
 Dim FileFormatstr As String 
 Dim FName As Variant 
 
'测试如果Microsoft Add- in安装
如果Dir（Environ（commonprogramfiles）&\Microsoft Shared\OF​​FICE_& Format（Val（Application.Version），00）&\EXP_PDF.DLL ）<> 然后
如果FixedFilePathName =然后
'打开GetSaveAsFilename对话框以输入pdf 
 FileFormatstr =PDF文件（* .pdf），* .pdf$的文件名b $ b FName = Application.GetSaveAsFilename（，filefilter：= FileFormatstr，_ Title：=创建PDF）
'如果取消此对话框退出函数
如果FName = False则退出函数
 Else 
 FName = FixedFilePathName 
 End If 
'如果OverwriteIfFileExist = False，我们测试文件夹中是否已经存在PDF 
'，如果该值为True，则退出该函数
如果OverwriteIfFileExist = False然后
如果Dir（FName）<> 然后退出函数
结束如果
 
'现在文件名正确我们发布到PDF 
错误恢复下一步
 Myvar.ExportAsFixedFormat _ 
类型：= xlTypePDF，_ 
文件名：= FName，_ 
质量：= xlQualityStandard，_ 
 IncludeDocProperties：= True，_ 
 IgnorePrintAreas：= False，_ 
 OpenAfterPublish：= OpenPDFAfterPublish 
错误GoTo 0 
 
'如果发布确定，函数将返回文件名
如果Dir（FName）<> 然后Create_PDF = FName 
 
如果
结束函数
  

解决方案

没有必要检查该特定DLL的存在，因为在MacOS下，PDF导出支持是本机的。您的代码只有在删除加载项检查并删除FileFilter字符串时才起作用：

 函数Create_PDF（Myvar As Object，FixedFilePathName As String，_ 
 OverwriteIfFileExist As Boolean，OpenPDFAfterPublish As Boolean）As String 
 
 Dim FileFormatstr As String 
 Dim FName As Variant 
 
如果FixedFilePathName = 然后
'打开GetSaveAsFilename对话框以输入pdf 
的文件名FName = Application.GetSaveAsFilename（，Title：=创建PDF）
'如果取消此对话框退出函数
如果FName = False然后退出函数
 Else 
 FName = FixedFilePathName 
 End If 
'如果OverwriteIfFileExist = False，我们测试PDF 
 '已存在于文件夹中并退出该函数，如果该值为True 
如果OverwriteIfFileExist = False则
如果Dir（FName）<> 然后退出函数
结束如果
 
'现在文件名正确我们发布到PDF 
错误恢复下一步
 Myvar.ExportAsFixedFormat _ 
类型：= xlTypePDF，_ 
文件名：= FName，_ 
质量：= xlQualityStandard，_ 
 IncludeDocProperties：= True，_ 
 IgnorePrintAreas：= False，_ 
 OpenAfterPublish：= OpenPDFAfterPublish 
错误GoTo 0 
 
'如果发布确定，函数将返回文件名
如果Dir（FName）<> 然后Create_PDF = FName 
 
结束函数
  

但 GetSaveAsFilename 是在MacOS上瘫痪，不允许通过filetype过滤文件。如果您需要限制用户使用某种文件类型，可以使用AppleScript并执行以下操作：

 函数Create_PDF_Mac（Myvar As Object，FixedFilePathName As String，_ 
 OverwriteIfFileExist As Boolean，OpenPDFAfterPublish As Boolean）As String 
 
 Dim FileFormatstr As String 
 Dim FName As Variant 
 
 If FixedFilePathName =然后
'打开GetSaveAsFilename对话框以输入pdf 
'FName = Application.GetSaveAsFilename（，.PDF，Title：=创建PDF）的文件名
 
 On Error Resume Next 
 ThePath = MacScript（将文件夹的路径转换为String）
 
 TheScript = _ 
设置applescript的文本项目分隔符为，& vbNewLine& _ 
将文件设置为（选择文件名，并提示另存为文件& _ 
默认名称untitled.pdf默认位置别名& _ 
 ThePath&）作为字符串& vbNewLine& _ 
如果文件不是以.pdf结尾，那么将文件设置为文件.pdf& vbNewLine& _ 
将applescript的文本项分隔符设置为& vbNewLine& _ 
返回文件
 
 FName = MacScript（TheScript）
错误GoTo 0 
 
'如果取消此对话框退出函数
如果FName = False然后退出函数
 Else 
 FName = FixedFilePathName 
 End If 
'如果OverwriteIfFileExist = False，我们测试PDF 
'是否已经存在文件夹并退出该功能，如果这是True 
如果OverwriteIfFileExist = False然后
如果Dir（FName）<> 然后退出函数
结束如果
 
'现在文件名正确我们发布到PDF 
错误恢复下一步
 Myvar.ExportAsFixedFormat _ 
类型：= xlTypePDF，_ 
文件名：= FName，_ 
质量：= xlQualityStandard，_ 
 IncludeDocProperties：= True，_ 
 IgnorePrintAreas：= False，_ 
 OpenAfterPublish：= OpenPDFAfterPublish 
错误GoTo 0 
 
'如果发布确定，函数将返回文件名
如果Dir（FName）<> 然后Create_PDF = FName 
 
结束函数
  

您可以使用操作系统选择器开关为每个操作系统运行相应的功能操作系统：

 ＃如果Mac然后
 savedFileName = Create_PDF_Mac（...）
 #Else 
 savedFileName = Create_PDF_PC（...）
 #End如果
  

MacOS中的默认VB功能，这是 Microsof't建议的方法。

I have coded the following function. However, I cannot get it to work on office Mac. I am not sure of the procedure to find the EXP_PDF.DLL mac equivalent

Function Create_PDF(Myvar As Object, FixedFilePathName As String, _                    
OverwriteIfFileExist As Boolean, OpenPDFAfterPublish As Boolean) As String    

Dim FileFormatstr As String    
Dim FName As Variant

'Test If the Microsoft Add-in is installed    
If Dir(Environ("commonprogramfiles") & "\Microsoft Shared\OFFICE" _& Format(Val(Application.Version), "00") & "\EXP_PDF.DLL") <> "" Then
        If FixedFilePathName = "" Then            
           'Open the GetSaveAsFilename dialog to enter a file name for the pdf            
           FileFormatstr = "PDF Files (*.pdf), *.pdf"            
           FName = Application.GetSaveAsFilename("", filefilter:=FileFormatstr, _                                                                                       Title:="Create PDF")
            'If you cancel this dialog Exit the function            
            If FName = False Then Exit Function        
         Else            
            FName = FixedFilePathName        
        End If
        'If OverwriteIfFileExist = False we test if the PDF        
         'already exist in the folder and Exit the function if that is True        
        If OverwriteIfFileExist = False Then            
            If Dir(FName) <> "" Then Exit Function        
        End If

       'Now the file name is correct we Publish to PDF        
       On Error Resume Next        
       Myvar.ExportAsFixedFormat _                
                Type:=xlTypePDF, _                
                FileName:=FName, _                
                Quality:=xlQualityStandard, _                
                IncludeDocProperties:=True, _                
                IgnorePrintAreas:=False, _                
                OpenAfterPublish:=OpenPDFAfterPublish        
        On Error GoTo 0

        'If Publish is Ok the function will return the file name        
         If Dir(FName) <> "" Then Create_PDF = FName    

End If
End Function

解决方案

There is no need to check for the existence of that specific DLL, because under MacOS, PDF export support is native. Your code simply works if you remove the Add-in check and remove the FileFilter string:

Function Create_PDF(Myvar As Object, FixedFilePathName As String, _
OverwriteIfFileExist As Boolean, OpenPDFAfterPublish As Boolean) As String

Dim FileFormatstr As String
Dim FName As Variant

    If FixedFilePathName = "" Then
       'Open the GetSaveAsFilename dialog to enter a file name for the pdf
       FName = Application.GetSaveAsFilename("", Title:="Create PDF")
        'If you cancel this dialog Exit the function
        If FName = False Then Exit Function
     Else
        FName = FixedFilePathName
    End If
    'If OverwriteIfFileExist = False we test if the PDF
     'already exist in the folder and Exit the function if that is True
    If OverwriteIfFileExist = False Then
        If Dir(FName) <> "" Then Exit Function
    End If

   'Now the file name is correct we Publish to PDF
   On Error Resume Next
   Myvar.ExportAsFixedFormat _
            Type:=xlTypePDF, _
            Filename:=FName, _
            Quality:=xlQualityStandard, _
            IncludeDocProperties:=True, _
            IgnorePrintAreas:=False, _
            OpenAfterPublish:=OpenPDFAfterPublish
    On Error GoTo 0

    'If Publish is Ok the function will return the file name
     If Dir(FName) <> "" Then Create_PDF = FName

End Function

But GetSaveAsFilename is crippled on MacOS and does not allow filtering files by filetype. If you need to restrict users to a certain filetype, you can resort to AppleScript and do the following:

Function Create_PDF_Mac(Myvar As Object, FixedFilePathName As String, _
OverwriteIfFileExist As Boolean, OpenPDFAfterPublish As Boolean) As String

Dim FileFormatstr As String
Dim FName As Variant

    If FixedFilePathName = "" Then
       'Open the GetSaveAsFilename dialog to enter a file name for the pdf
       'FName = Application.GetSaveAsFilename("", ".PDF", Title:="Create PDF")

        On Error Resume Next
        ThePath = MacScript("return (path to documents folder) as String")

        TheScript = _
        "set applescript's text item delimiters to "","" " & vbNewLine & _
        "set theFile to (choose file name with prompt ""Save As File"" " & _
            "default name ""untitled.pdf"" default location alias """ & _
            ThePath & """ ) as string" & vbNewLine & _
        "if theFile does not end with "".pdf"" then set theFile to theFile & "".pdf"" " & vbNewLine & _
        "set applescript's text item delimiters to """" " & vbNewLine & _
        "return theFile"

           FName = MacScript(TheScript)
        On Error GoTo 0

        'If you cancel this dialog Exit the function
        If FName = False Then Exit Function
     Else
        FName = FixedFilePathName
    End If
    'If OverwriteIfFileExist = False we test if the PDF
     'already exist in the folder and Exit the function if that is True
    If OverwriteIfFileExist = False Then
        If Dir(FName) <> "" Then Exit Function
    End If

   'Now the file name is correct we Publish to PDF
   On Error Resume Next
   Myvar.ExportAsFixedFormat _
            Type:=xlTypePDF, _
            Filename:=FName, _
            Quality:=xlQualityStandard, _
            IncludeDocProperties:=True, _
            IgnorePrintAreas:=False, _
            OpenAfterPublish:=OpenPDFAfterPublish
    On Error GoTo 0

    'If Publish is Ok the function will return the file name
     If Dir(FName) <> "" Then Create_PDF = FName

End Function

The you can use an OS selector switch to run the appropriate function for each OS:

#If Mac Then
    savedFileName = Create_PDF_Mac(...)
#Else
    savedFileName = Create_PDF_PC(...)
#End If

Given the limitations of default VB functions in MacOS, this is Microsof't suggested method as well.

这篇关于Excel VBA代码在Mac上工作，创建PDF功能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！