英文:
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 ' Added variable declaration for MyName
Dim ws As Worksheet ' Added variable declaration for the worksheet
Set ws = ThisWorkbook.Worksheets("Print Packages") ' Change "Print Packages" to the actual sheet name
MyName = Range("C3").Value & " Summary"
SvAs = ThisWorkbook.Path & "\" & MyName & ".pdf" ' Updated the file save name with the full path
Dim lastRow As Long
lastRow = ws.Cells(ws.Rows.Count, "E").End(xlUp).Row
If lastRow < 13 Then lastRow = 13 ' Set the minimum lastRow value to 13
Dim mySheets1() As Variant
ReDim mySheets1(1 To lastRow - 12) ' Adjust the array size based on the number of values
Dim i As Long
For i = 13 To lastRow ' Start from row 13 in Column E
mySheets1(i - 12) = ws.Cells(i, "E").Value ' Add the value to the array
Next i
Application.ScreenUpdating = False
Dim sheetName As Variant
Dim sheetNames As String
' Concatenate the contents of mySheets1 into a string
For Each sheetName In mySheets1
sheetNames = sheetNames & sheetName & vbCrLf
Next sheetName
' Display the contents of mySheets1 in a message box
MsgBox "mySheets1 contains:" & vbCrLf & sheetNames
' Select and activate the necessary worksheets
For Each sheet In mySheets1
ThisWorkbook.Worksheets(sheet).Select False
Next sheet
' 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 <> 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 ' Return True to indicate successful execution
' Open the PDF file on the user's computer
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 ' Added to re-enable display alerts
Application.ScreenUpdating = True ' Added to re-enable screen updating
Exit Function
SaveError:
MsgBox "Unable to save as PDF. Please check your file path and try again."
Save_PDF = False ' Return False to indicate error
Resume EndMacro ' 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("Print Packages")
Set rng = .Range("E13:E" & .Cells(.Rows.Count, "E").End(xlUp).row)
replacePrevious = True '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 'select the sheet
'switch to adding the sheet to the already-selected sheet(s)
replacePrevious = False
Next c
End With
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:= "yourFilePathHere", Quality:= xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, _
OpenAfterPublish:=True
End Function
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论