Excel VBA Macro将一系列工作表保存为一个pdf，然后针对不同系列的工作表重复 [英] Excel VBA Macro to save series of worksheets to one pdf then repeat for different series of worksheets

问题描述

尝试使用将四个工作表的组打印到一个PDF的宏，直到已打印工作簿中的所有工作表组.

Trying for a macro that prints groups of four worksheets to one PDF until all groups of worksheets in the workbook have been printed.

我有一个工作簿，其中包含许多工作表.

I have a workbook that contains many sets of worksheets.

工作表分为四个报表: A1，A2，A3，A4，B1，B2，B3，B4 ... .

The worksheets are in sets of four reports: A1, A2, A3, A4, B1, B2, B3, B4....

我想创建一个宏，使我可以将工作表 A1-A4 打印到一个PDF，然后重复工作表 B1-B4，C1-C4 ... .

I would like to create a macro that allows me to print worksheets A1-A4 to one PDF, then repeat for worksheets B1-B4, C1-C4....

我想使用一系列单元格来命名PDF.也许在工作表A1上，单元格 A1:A3 (工作表 B1 ，单元格 A1:A3 等).

I would like to use a range of cells to name the PDFs. Perhaps on sheet A1, cells A1:A3 (sheet B1, cells A1:A3, etc).

最终结果将是一个文件夹，其中每个工作表1-4组都包含一个PDF.

The end result would be a folder that contains one PDF for each group of worksheets 1-4.

我已经看到了为一组工作表或选定的工作表执行此操作的宏，但是无法弄清楚如何在完成第一组工作表后使宏对另一组工作表重复该过程.

I have seen macros that do this for one set of worksheets or for selected worksheets, but cannot figure out how to have the macro repeat the process for another set of worksheets after it has completed the first set.

任何正确方向的提示，暗示，技巧，帮助或指向将是有益的，宽宏大量的.试图节省每次有人对数据进行一点更新时打印报告的时间...

Any tips, hints, tricks, help or points in the right direction would be beneficent, magnanimous. Trying to save gobs of time printing reports every time someone makes a little update to the data...

下面的宏(不是我的宏)可以很好地为选定的工作表完成所有操作，但不能一次又一次地为一系列4个工作表完成操作.

The following macro (not mine) does this all beautifully for selected worksheets, but not for series of 4 worksheets over and over again.

Sub PDFActiveSheetNoPrompt()

'www.contextures.com
'for Excel 2010 and later
Dim wsa As Worksheet
Dim wba As Workbook
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

For Each wsa In ActiveWorkbook.Worksheets

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

strName = wsa.Range("A1").Value _
          & " - " & wsa.Range("A2").Value _
          & " - " & wsa.Range("A3").Value

'create default name for saving file
strFile = strName & ".pdf"
strPathFile = strPath & strFile

'export to PDF in current folder
    wsa.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:=strPathFile, _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=False
    'confirmation message with file info
    'MsgBox "PDF file has been created: " _
     ' & vbCrLf _
      '& strPathFile

    Next

exitHandler:
    Exit Sub
errHandler:
    MsgBox "Could not create PDF file"
    Resume exitHandler



End Sub

推荐答案

您可以将每组4个工作表合并为一个工作表以创建PDF(例如，名为"A_PDF"的新工作表将包含从A1到A4的数据).使用公式或宏来复制数据，具体取决于每个工作表的大小和结构.然后修改您的PDF宏，使其一次只能创建一个PDF，并创建一个主宏以遍历* _PDF工作表:

You could amalgamate each set of 4 worksheets into a single worksheet for PDF creation (eg a new worksheet named "A_PDF" would contain data from A1 to A4). Use formulas or a macro to copy the data across, depending on the size and structure of each worksheet. Then modify your PDF macro so it only creates one PDF at a time, and create a master macro to loop through the *_PDF worksheets:

Sub MasterPDFCreator()

    Call PDFActiveSheetNoPrompt(Sheets("A_PDF"))
    Call PDFActiveSheetNoPrompt(Sheets("B_PDF"))
    Call PDFActiveSheetNoPrompt(Sheets("C_PDF"))

End Sub

Sub PDFActiveSheetNoPrompt(wsName as Worksheet)

'www.contextures.com
'for Excel 2010 and later
Dim wsa As Worksheet
Dim wba As Workbook
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 = wsName

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

strName = wsa.Range("A1").Value _
      & " - " & wsa.Range("A2").Value _
      & " - " & wsa.Range("A3").Value

'create default name for saving file
strFile = strName & ".pdf"
strPathFile = strPath & strFile

'export to PDF in current folder
    wsa.ExportAsFixedFormat _
    Type:=xlTypePDF, _
    Filename:=strPathFile, _
    Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, _
    IgnorePrintAreas:=False, _
    OpenAfterPublish:=False
'confirmation message with file info
'MsgBox "PDF file has been created: " _
 ' & vbCrLf _
  '& strPathFile

exitHandler:
    Exit Sub
errHandler:
    MsgBox "Could not create PDF file"
    Resume exitHandler

End Sub

也许不是最整洁的解决方案，但是我还没有找到将工作表合并到单个PDF中的另一种方法.

Not the neatest solution perhaps but I haven't yet found another way of merging sheets into a single PDF.

这篇关于Excel VBA Macro将一系列工作表保存为一个pdf，然后针对不同系列的工作表重复的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！