I want to print a PDF of specified sheets. I currently have a code that attaches the active sheet when I dont want it to

huangapple go评论60阅读模式
英文:

I want to print a PDF of specified sheets. I currently have a code that attaches the active sheet when I dont want it to

问题

以下是代码的中文翻译部分:

Sub Print_Investor_Summary()
    Save_PDF
End Sub

Function Save_PDF() As Boolean
    Dim Thissheet As String, ThisFile As String, PathName As String
    Dim SvAs As String
    Dim MyName As String ' 声明了变量MyName
    Dim ws As Worksheet ' 声明了变量ws
    
    Set ws = ThisWorkbook.Worksheets("Print Packages") ' 将"Print Packages"更改为实际的工作表名称
    
    MyName = Range("C3").Value & " 汇总" ' 使用Range("C3")的值设置MyName
    SvAs = ThisWorkbook.Path & "\" & MyName & ".pdf" ' 使用完整路径更新文件保存名称
    
    Dim lastRow As Long
    lastRow = ws.Cells(ws.Rows.Count, "E").End(xlUp).Row
    If lastRow < 13 Then lastRow = 13 ' 将lastRow的最小值设置为13
    
    Dim mySheets1() As Variant
    ReDim mySheets1(1 To lastRow - 12) ' 根据值的数量调整数组大小
    
    Dim i As Long
    For i = 13 To lastRow ' 从E列的第13行开始
        mySheets1(i - 12) = ws.Cells(i, "E").Value ' 将值添加到数组中
    Next i
    
    Application.ScreenUpdating = False
    
    Dim sheetName As Variant
    Dim sheetNames As String
    
    ' 将mySheets1的内容连接为字符串
    For Each sheetName In mySheets1
        sheetNames = sheetNames & sheetName & vbCrLf
    Next sheetName
    
    ' 在消息框中显示mySheets1的内容
    MsgBox "mySheets1 包含:" & vbCrLf & sheetNames
    
    ' 选择并激活必要的工作表
    For Each sheet In mySheets1
        ThisWorkbook.Worksheets(sheet).Select False
    Next sheet
    
    ' 设置打印质量
    On Error Resume Next
    ws.PageSetup.PrintQuality = 600
    Err.Clear
    On Error GoTo 0
    
    On Error GoTo SaveError
    
    Dim currentSheet As Worksheet
    For Each sheet In mySheets1
        Set currentSheet = ThisWorkbook.Worksheets(sheet)
        If currentSheet.Name <> ActiveSheet.Name Then
            currentSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=SvAs, Quality:=xlQualityMaximum, IncludeDocProperties:=False, IgnorePrintAreas:=False
        End If
    Next sheet
    
    On Error GoTo 0
    
    Save_PDF = True ' 返回True以指示成功执行
    
    ' 打开用户计算机上的PDF文件
    On Error Resume Next
    Shell "explorer.exe " & Chr(34) & SvAs & Chr(34), vbNormalFocus
    On Error GoTo 0
    
    Sheets("Print Packages").Select
    
EndMacro:
    Application.DisplayAlerts = True ' 重新启用显示警报
    Application.ScreenUpdating = True ' 重新启用屏幕更新
    Exit Function

SaveError:
    MsgBox "无法保存为PDF。请检查文件路径并重试。"
    Save_PDF = False ' 返回False以指示错误
    
    Resume EndMacro ' 转到EndMacro标签以重新启用显示警报和屏幕更新
    
End Function

请注意,我只提供了代码的翻译部分,没有包括问题陈述或额外的内容。如果您需要进一步的帮助或解释,请随时提问。

英文:

I will attach code below.
I essentially have a list of the names of sheets in the workbook that I want to use a macro button to print to a pdf. This list starts in cell e13 on the "Print Packages" sheet of the workbook and proceeds vertically, (next sheet contained in e14, and e15, and so on). For some reason, my code attaches the Print Packages sheet to the pdf it prints out, which I do not want it to do.

If there is a solution to this or a more efficient way to do this code as well please let me know.

CODE:

Sub Print_Investor_Summary()
    Save_PDF
End Sub

Function Save_PDF() As Boolean
    Dim Thissheet As String, ThisFile As String, PathName As String
    Dim SvAs As String
    Dim MyName As String &#39; Added variable declaration for MyName
    Dim ws As Worksheet &#39; Added variable declaration for the worksheet
    
    Set ws = ThisWorkbook.Worksheets(&quot;Print Packages&quot;) &#39; Change &quot;Print Packages&quot; to the actual sheet name
    
    MyName = Range(&quot;C3&quot;).Value &amp; &quot; Summary&quot;
    SvAs = ThisWorkbook.Path &amp; &quot;\&quot; &amp; MyName &amp; &quot;.pdf&quot; &#39; Updated the file save name with the full path
    
    Dim lastRow As Long
    lastRow = ws.Cells(ws.Rows.Count, &quot;E&quot;).End(xlUp).Row
    If lastRow &lt; 13 Then lastRow = 13               &#39; Set the minimum lastRow value to 13
    
    Dim mySheets1() As Variant
    ReDim mySheets1(1 To lastRow - 12)             &#39; Adjust the array size based on the number of values
    
    Dim i As Long
    For i = 13 To lastRow                          &#39; Start from row 13 in Column E
        mySheets1(i - 12) = ws.Cells(i, &quot;E&quot;).Value &#39; Add the value to the array
    Next i
    
    Application.ScreenUpdating = False
    
    Dim sheetName As Variant
Dim sheetNames As String

&#39; Concatenate the contents of mySheets1 into a string
For Each sheetName In mySheets1
    sheetNames = sheetNames &amp; sheetName &amp; vbCrLf
Next sheetName

&#39; Display the contents of mySheets1 in a message box
MsgBox &quot;mySheets1 contains:&quot; &amp; vbCrLf &amp; sheetNames

    &#39; Select and activate the necessary worksheets
    For Each sheet In mySheets1
        ThisWorkbook.Worksheets(sheet).Select False
    Next sheet
    
    &#39; Set Print Quality
    On Error Resume Next
    ws.PageSetup.PrintQuality = 600
    Err.Clear
    On Error GoTo 0

    On Error GoTo SaveError

Dim currentSheet As Worksheet
For Each sheet In mySheets1
    Set currentSheet = ThisWorkbook.Worksheets(sheet)
    If currentSheet.Name &lt;&gt; ActiveSheet.Name Then
        currentSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=SvAs, Quality:=xlQualityMaximum, IncludeDocProperties:=False, IgnorePrintAreas:=False
    End If
Next sheet

On Error GoTo 0
    
    Save_PDF = True &#39; Return True to indicate successful execution
    
    &#39; Open the PDF file on the user&#39;s computer
    On Error Resume Next
    Shell &quot;explorer.exe &quot; &amp; Chr(34) &amp; SvAs &amp; Chr(34), vbNormalFocus
    On Error GoTo 0

    Sheets(&quot;Print Packages&quot;).Select

EndMacro:
    Application.DisplayAlerts = True &#39; Added to re-enable display alerts
    Application.ScreenUpdating = True &#39; Added to re-enable screen updating
    Exit Function

SaveError:
    MsgBox &quot;Unable to save as PDF. Please check your file path and try again.&quot;
    Save_PDF = False &#39; Return False to indicate error
    
    Resume EndMacro &#39; Go to EndMacro label to enable re-enabling display alerts and screen updating

End Function

I have tried forcibly excluding it from the variant that contains the names of the sheets I desire to be printed as well as many other methods and just cant seem to get it to work.

You will see I even have a portion of the code to display the names of the sheets contained in mysheets1 that shows that the active sheet is not a part of the array but yet still shows up in the final product PDF.

答案1

得分: 1

ThisWorkbook.Worksheets(sheet).Select False 将工作表 sheet 添加到当前所选工作表集合中。对于第一个工作表,您需要使用 True,这样它会替换当前的工作表。

Function Save_PDF() As Boolean
    
    Dim rng As Range, c As Range, replacePrevious As Boolean
    Dim ws As Worksheet
    
    With ThisWorkbook.Worksheets("Print Packages")
        Set rng = .Range("E13:E" & .Cells(.Rows.Count, "E").End(xlUp).Row)
        replacePrevious = True  '第一个工作表将替换当前所选工作表
        For Each c In rng.Cells
            Set ws = ThisWorkbook.Sheets(c.Value)
            On Error Resume Next
            ws.PageSetup.PrintQuality = 600
            Err.Clear
            On Error GoTo 0
            ws.Select replacePrevious '选择工作表
            '切换为将工作表添加到已选择的工作表集合中
            replacePrevious = False
        Next c
    End With
    
    
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
       Filename:= "yourFilePathHere", Quality:= xlQualityStandard, _
       IncludeDocProperties:=True, IgnorePrintAreas:=False, _
       OpenAfterPublish:=True

End Function
英文:

ThisWorkbook.Worksheets(sheet).Select False adds the worksheet sheet to the current sheet selection. You need to use True for the first sheet, so it replaces the current sheet.

Function Save_PDF() As Boolean
    
    Dim rng As Range, c As Range, replacePrevious As Boolean
    Dim ws As Worksheet
    
    With ThisWorkbook.Worksheets(&quot;Print Packages&quot;)
        Set rng = .Range(&quot;E13:E&quot; &amp; .Cells(.Rows.Count, &quot;E&quot;).End(xlUp).row)
        replacePrevious = True  &#39;first sheet repalces whatever is selected
        For Each c In rng.Cells
            Set ws = ThisWorkbook.Sheets(c.Value)
            On Error Resume Next
            ws.PageSetup.PrintQuality = 600
            Err.Clear
            On Error GoTo 0
            ws.Select replacePrevious &#39;select the sheet
            &#39;switch to adding the sheet to the already-selected sheet(s)
            replacePrevious = False
        Next c
    End With
    
    
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
       Filename:= &quot;yourFilePathHere&quot;, Quality:= xlQualityStandard, _
       IncludeDocProperties:=True, IgnorePrintAreas:=False, _
       OpenAfterPublish:=True

End Function

huangapple
  • 本文由 发表于 2023年6月26日 22:25:17
  • 转载请务必保留本文链接:https://go.coder-hub.com/76557622.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定