Exporting dynamic range of sheets to a single PDF document.

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

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"

huangapple
  • 本文由 发表于 2023年5月17日 08:09:40
  • 转载请务必保留本文链接:https://go.coder-hub.com/76267793.html
匿名

发表评论

匿名网友

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

确定