excel - 仅将填充了单元格的电子表格保存为 PDF

Question

我目前正在尝试修改 Visual Basic 宏，以便仅将电子表格保存在已填充单元格的工作簿中。

当前宏仅将整个 16 张工作簿保存为 PDF，但其中最多 9 张有时未完成，但仍被保存。

我希望宏在单击“保存”按钮后自动检查是否已填充这些工作表，然后继续仅将填写好的(完整)工作表保存为 PDF。

我会非常乐意提供任何帮助!

下面的代码是宏在保存整个工作簿时的当前工作方式。 (在保存为 PDF 之前有一个 IF 语句检查。)

Sub SaveAsPDF()

    With ThisWorkbook.Sheets("COVERPage1PRINT")
        If (Len(.Range("C24")) = 0) Then
            MsgBox "Ensure Serial Number or Stamp number are filled."
            Exit Sub
        ElseIf (Len(.Range("H17")) = 0) Then
            MsgBox "Ensure Serial Number or Stamp Number are filled."
            Exit Sub

        Else
            ChDir _
            "P:\Cells\Spool & Sleeves Cell\Flow Plot Records\EFA\Saved EFA PDF Archive"
        fname = Sheets("COVERPage1PRINT").Range("H17")
        ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
            "P:\Cells\Spool & Sleeves Cell\Flow Plot Records\EFA\Saved EFA PDF Archive\" & fname, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
            :=False, OpenAfterPublish:=True

        End If
    End With
End Sub

Answer 1

这应该可以完成工作(编辑代码)

Sub test1()

Dim wbBook As Workbook
Dim wsSheet As Worksheet
Dim test() As String
Dim i As Integer
Dim pdfpath As String
Dim sheets_to_be_checked() As Variant
Dim a As Boolean
pdfpath = ActiveWorkbook.Path 'YOU CAN ADD YOUR PDF SAVING LOCATION e.g. "C\Users\ABC\Desktop"

i = 0
sheets_to_be_checked = Array("Sheet1", "Sheet3")
Set wbBook = ActiveWorkbook

With ThisWorkbook.Sheets("COVERPage1PRINT")
    If (Len(.Range("C24")) = 0) Then
        MsgBox "Ensure Serial Number & Tag Number or Stamp number are filled."
        Exit Sub
    ElseIf (Len(.Range("H16")) = 0) Then
        MsgBox "Ensure Serial Number & Tag Number or Stamp Number are filled."
        Exit Sub
    ElseIf (Len(.Range("H19")) = 0) Then
        MsgBox "Ensure Serial Number & Tag Number or Stamp Number are filled."
        Exit Sub
    Else:
        For Each wsSheet In wbBook.Worksheets
            With wsSheet
                If IsInArray(wsSheet.Name, sheets_to_be_checked) Then
                    wsSheet.Activate
                    If WorksheetFunction.CountA(Range("D4:D9, E10:E15, F4:F9, G10:G15, H4:H9, I10:I15, J4:J9, K10:K15")) = 48 Then
                        ReDim Preserve test(i)
                        test(i) = wsSheet.Name
                        i = i + 1
                    End If
                Else:
                    ReDim Preserve test(i)
                    test(i) = wsSheet.Name
                    i = i + 1
                End If
            End With
        Next wsSheet
    End If
End With

ThisWorkbook.Sheets(test()).Select

ActiveSheet.ExportAsFixedFormat _
     Type:=xlTypePDF, _
     Filename:=pdfpath & "\ouput.pdf", _
     Quality:=xlQualityStandard, _
     IncludeDocProperties:=True, _
     IgnorePrintAreas:=False, _
     OpenAfterPublish:=True
End Sub


Function IsInArray(stringToBeFound As String, arr As Variant) As Boolean
  IsInArray = (UBound(Filter(arr, stringToBeFound)) > -1)
End Function

根据您对填充工作表的定义，答案可能会略有不同。您将不得不更改条件 "If .UsedRange.Address <> "$A$1"Then "一种可能的替代方法是 WorksheetFunction.CountA(Range("A1:Z100")) <> 0

如果您需要有关条件或代码的任何帮助，请告诉我。