gpt4 book ai didi

excel - SaveAs 功能适用于 Microsoft PC,但不适用于 MAC

转载 作者:行者123 更新时间:2023-12-03 02:35:31 24 4
gpt4 key购买 nike

我有 VBA 代码,可以控制用户以 .xls、.xlsm 或 .pdf 以外的任何其他格式保存文件。这是为了防止在保存过程中剥离宏。

我插入了一行来检查操作系统是否为 OSx(...如“Mac”),它适用于其他宏,但不适用于此宏。该过程失败,并显示“找不到文件对象或库”,并突出显示“msoFileDialogSaveAs”。

这是我的代码:

    Option Explicit
Option Compare Text

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel As Boolean)

Dim fso As Object 'FileSystemObject
Dim PdfSave As Boolean
Dim SheetName As String
If Not Application.OperatingSystem Like "*Mac*" Then
SheetName = ActiveSheet.Name
'Save-As action?
If SaveAsUI Then
Set fso = CreateObject("Scripting.FileSystemObject")
'Abort excel's dialog
Cancel = True
'Create our own
With Application.FileDialog(msoFileDialogSaveAs)
'Select the XLSM filter by default
.FilterIndex = 2
Again:
'Ok clicked?
If .Show = -1 Then
'Which extension should we save?
Select Case fso.GetExtensionName(.SelectedItems(1))
Case "xlsm"
'Okay
Case "xls"
'Okay
Case "pdf"
PdfSave = True
'Okay
Case Else
MsgBox "Invalid file type selected!" _
& vbCr & vbCr & "Only the following file formats are permitted:" _
& vbCr & " 1. Excel Macro-Enabled Workbook (*.xlsm)" _
& vbCr & " 2. Excel 97-2003 Workbook (*.xls)" _
& vbCr & " 3. PDF (*.pdf)" _
& vbCr & vbCr & "Please try again." _
& vbCr & vbCr & "NOTE: 'Excel 97-2003 Workbook (*.xls)' format should be used for" _
& vbCr & "backwards compatability only!", vbOKOnly + vbCritical
GoTo Again
End Select
'Prevent that we call ourself
Application.EnableEvents = False
'Save the file
If PdfSave = True Then
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=ActiveWorkbook.Path & "\" & SheetName & ".pdf", Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
Else
ThisWorkbook.SaveAs .SelectedItems(1)
End If
Application.EnableEvents = True
End If
End With
End If
End If
End Sub

任何人都可以提出更改建议,以便此代码适用于 PC 和 MAC 上的 Office,或者使用不同的代码来实现相同的功能。

谢谢

迈克

最佳答案

在 Mac 和 PC 环境中工作时,您正走向 map 的边缘,我必须经常这样做,而且波涛汹涌的大海是肯定的!我的建议是坚持下去,你走在正确的道路上。

首先,我有一个类似的操作系统检查:-

BlnIsAPC = IIf(Left(Trim(UCase(Application.OperatingSystem)), 1) = "M", False, True)

这只是试图找到最适合 future 的方法来使操作系统正确运行。

其次,很高兴您迟到绑定(bind)到 Scripting.FileSystemObject,因为它不在 Mac Office 中(它是 Windows 的一部分,而不是 Office)。

第三,FileDialog 也不是,因此出现错误“找不到文件对象或库”。还有一个替代方案,您最终需要引用一下它。它是一个名为 MacScript 的内置函数。

您需要弄清楚如何在 AppleScript 中执行此操作,然后创建该脚本并通过 VBA 中的 MacScript 运行它。下面是我工作的一个精简示例,其中我的代码要么在 PC 上使用 Application.FileDialog(msoFileDialogOpen) ,要么在 Mac 上使用 MacScript ,具体来说这只是显示 Mac 端。

Public Function GetFilePath(ByVal StrTitle As String, ByVal StrButtonName As String, ByVal BlnMultiSelect As Boolean, ByVal StrFilters As String) As String
' StrTitle = The title to go on the dialog box
' StrButtonName = What to show on the OK button
' BlnMultiSelect = Can the user select more than one file
' StrFilters = What can be selected pipe and colon delimited i.e. [name]:[suffix]|[name]:[suffix]

If Procs.Global_IsAPC Then
GetFilePath = GetFilePath_PC(StrTitle, StrButtonName, BlnMultiSelect, StrFilters)
Else
GetFilePath = GetFilePath_Mac(StrTitle, StrButtonName, BlnMultiSelect, StrFilters)
End If

End Function

Private Function GetFilePath_PC(ByVal StrTitle As String, ByVal StrButtonName As String, ByVal BlnMultiSelect As Boolean, StrFilters As String) As String
...
End Function

Private Function GetFilePath_Mac(ByVal StrTitle As String, ByVal StrButtonName As String, ByVal BlnMultiSelect As Boolean, StrFilters As String) As String
Dim AryTemp2() As String
Dim LngCounter As Long
Dim StrContainer As String
Dim StrPath As String

StrContainer = "tell application " & """" & "Finder" & """" & Chr(13)
StrContainer = StrContainer & "choose file with prompt " & """" & StrTitle & """"

If StrFilters <> "" Then
StrContainer = StrContainer & " of type {"
'Code was here that prepared the filters into AryTemp2
For LngCounter = 0 To UBound(AryTemp2, 1)
If Right(StrContainer, 1) <> "{" Then StrContainer = StrContainer & ", "
StrContainer = StrContainer & """" & AryTemp2(LngCounter2) & """"
Next
StrContainer = StrContainer & "} "
End If

StrContainer = StrContainer & "without invisibles" & IIf(BlnMultiSelect, "", " and multiple selections") & " allowed" & Chr(13)
StrContainer = StrContainer & "end tell"
StrPath = MacScript(StrContainer)

If Left(StrPath, 6) = "alias " Then StrPath = Right(StrPath, Len(StrPath) - 6)

GetFilePath_Mac = StrPath

End Function

仅供引用,在 MacScript 中执行时,StrContainer 如下所示:-

tell application "Finder"
choose file with prompt "Select the required Config stub" of type {"Config_Stub"} without invisibles and multiple selections allowed
end tell

最后,VBA 并非在所有版本的 Office for Mac 上都可用,并且它们之间的工作方式存在细微的差异,不幸的是,您只能通过经验才能发现这些差异。就像我说的“你正在离开 map 的边缘”进入未知的水域。

关于excel - SaveAs 功能适用于 Microsoft PC,但不适用于 MAC,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/37410565/

24 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com