英文:
Exporting dynamic range of sheets to a single PDF document
问题
I am currently trying to export a number of sheets (anywhere from 1 to 500), based on a cell containing a certain word ie: "Print" to a single PDF document.
Sub Save_Sheets_As_PDF()
Dim sheetsInPDF As Long
Dim replaceSelected As Boolean
Dim ws As Worksheet
Dim PDFfileName As String
With ThisWorkbook
sheetsInPDF = 0
replaceSelected = True
For Each ws In .Worksheets
If LCase(ws.Range("A1").Value) = "Print" Then
ws.Select replaceSelected
replaceSelected = False
sheetsInPDF = sheetsInPDF + 1
End If
Next
If sheetsInPDF > 0 Then
PDFfileName = Worksheets("QUOTE").Range("B6") & ".pdf"
.ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=PDFfileName, _
Quality:=xlQualityMinimum, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
MsgBox "Created '" & PDFfileName & "' containing " & sheetsInPDF & " Elevations"
Else
MsgBox "PDF file not created"
End If
.Worksheets(1).Select True
End With
End Sub
Any help would be much appreciated. Even if it may be best to replace this code completely.
TIA.
Brad
英文:
I am currently trying to export a number of sheets (anywhere from 1 to 500), based on a cell containing a certain word ie: "Print" to a single PDF document.
Below is what I have tried doing so far, however, even though the Sheets 8,9 and 10 contain "Print" in cell A1 I still get the message "PDF file not created".
Sub Save_Sheets_As_PDF()
Dim sheetsInPDF As Long
Dim replaceSelected As Boolean
Dim ws As Worksheet
Dim PDFfileName As String
With ThisWorkbook
sheetsInPDF = 0
replaceSelected = True
For Each ws In .Worksheets
If LCase(ws.Range("A1").Value) = "Print" Then
ws.Select replaceSelected
replaceSelected = False
sheetsInPDF = sheetsInPDF + 1
End If
Next
If sheetsInPDF > 0 Then
PDFfileName = Worksheets("QUOTE").Range("B6") & ".pdf"
.ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=PDFfileName, _
Quality:=xlQualityMinimum, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
MsgBox "Created '" & PDFfileName & "' containing " & sheetsInPDF & " Elevations"
Else
MsgBox "PDF file not created"
End If
.Worksheets(1).Select True
End With
End Sub
Any help would be much appreciated. Even if it may be best to replace this code completely.
TIA.
Brad
答案1
得分: 3
This line of code...
如果 LCase(ws.Range("A1").Value) = "Print" Then
...将始终评估为 False。请尝试以下替代方案...
如果 LCase(ws.Range("A1").Value) = "print" Then
要指定路径,请尝试...
PDFfileName = .Path & "" & Worksheets("QUOTE").Range("B6") & ".pdf"
实际上,如果Worksheets("QUOTE")位于ThisWorkbook内,请尝试以下替代方案...
PDFfileName = .Path & "" & .Worksheets("QUOTE").Range("B6") & ".pdf"
英文:
This line of code...
If LCase(ws.Range("A1").Value) = "Print" Then
...will always be evaluated to False. Try the following instead...
If LCase(ws.Range("A1").Value) = "print" Then
To specify the path, try...
PDFfileName = .Path & "\" & Worksheets("QUOTE").Range("B6") & ".pdf"
Actually, if Worksheets("QUOTE") is located within ThisWorkbook, try the following instead...
PDFfileName = .Path & "\" & .Worksheets("QUOTE").Range("B6") & ".pdf"
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论