将多张图纸导出为PDF

Question

我想导出工作表“验证”和“设计概述”到一个单一的PDF。往下看，可以看到整个代码。

在使用时

Set wsA = ActiveSheet

代码运行得很好。但是，当我将该定义更改为

Set wsA = Sheets(Array("Verification", "Design Overview")).Select

代码深入到错误处理。

多个工作表选择是否与strName冲突，或者代码在哪里出错？

另外，我想取消选择多个工作表，只有活动表导出后选择。

Sub Export()

Dim wsA As Worksheet
Dim wbA As Workbook
Dim strTime As String
Dim strName As String
Dim strPath As String
Dim strFile As String
Dim strPathFile As String
Dim myFile As Variant
On Error GoTo errHandler

Set wbA = ActiveWorkbook
'Set wsA = ActiveSheet
Set wsA = Sheets(Array("Verification", "Design Overview")).Select
strTime = Format(Now(), "yyyymmdd\_hhmm")


'Get active workbook folder, if saved
strPath = wbA.Path
If strPath = "" Then
  strPath = Application.DefaultFilePath
End If
strPath = strPath & "\"

'Replace spaces and periods in sheet name
strName = Replace(wsA.Name, " ", "")
strName = Replace(strName, ".", "_")

'Create default name for savng file
strFile = "Example"
strPathFile = strPath & strFile

'User can enter name and select folder for the file
myFile = Application.GetSaveAsFilename _
(InitialFileName:=strPathFile, _
    FileFilter:="PDF (*.pdf), *.pdf", _
    Title:="Select folder and filename to save")

'Export to PDF if a folder is selected
If myFile <> "False" Then
wsA.ExportAsFixedFormat _
    Type:=xlTypePDF, _
    Filename:=myFile, _
    Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, _
    IgnorePrintAreas:=False, _
    OpenAfterPublish:=True
'Confirmation message with file info
'MsgBox "File has been created: " _
  '& vbCrLf _
  '& myFile
 End If

exitHandler:
Exit Sub
errHandler:
MsgBox "Could not export file", vbCritical, "Export"
Resume exitHandler

ThisWorkbook.Sheets("Design Overview").Select

End Sub

提前感谢！

Answer 1

如果你想根据一个变量来选择工作表，这是一个很好的方法：

Dim mySh As Sheets
Set mySh = Worksheets(Array(Worksheets(1).Name, Worksheets(2).Name))
mySh.Select

然而，正如评论中提到的，it is really very rare that you need to Select and Activate objects in Excel -相反，你可以引用它们，将它们合并到集合中，并使用它们来做任何你需要的事情：

Sub TestMe()

    Dim myWs As Worksheet
    Dim wsCol As New Collection

    wsCol.Add Worksheets(1)
    wsCol.Add Worksheets(2)

    For Each myWs In wsCol
        myWs.Cells(1, 1) = "TEST"
    Next myWs

    Dim mySh As Sheets
    Set mySh = Worksheets(Array(Worksheets(1).Name, Worksheets(2).Name))
    mySh.Select

End Sub

因此，你得到了两个好处--执行速度和对代码的更好控制(因此错误更少)。