只能以PDF格式储存填充单元格的填充电子表格 [英] Only save populated spreadsheets with filled in cells as PDF

问题描述

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

I'm currently trying to modify a Visual Basic macro to only save spreadsheets in a workbook that have had populated cells.

当前的宏只将整个16张工作簿保存为PDF，但最多只有9张这些工作表有时未完成，但仍保存。

The current macro just saves the entire 16 sheet workbook as a PDF yet a maximum of 9 of these sheets are sometimes left uncompleted, yet are still saved.

我希望宏自动检查这些表格是否已填充，一旦点击保存按钮，然后继续只保存填写（完整）作为PDF格式。

I would like the macro to automatically check if these sheets have been populated, once the 'SAVE' button is clicked and then proceed to only save the filled out (complete) sheets as a PDF.

我会大力地处理任何帮助！

I would massively appricate any help!

下面的代码是当前保存宏整个工作簿。 （在将其保存到PDF之前，有一个IF语句检查。）

The code below is how the macro currently works when just saving the entire workbook. (There is an IF statement check before it is saved to PDF.)

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

推荐答案

这应该做这个工作
（编辑代码）

This should do the job (edited code)

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 $ 1then
上面的一个可能的替代方法是WorksheetFunction.CountA（Range（A1：Z100））<> 0

The answer might change a little depending on your definition of populated sheets. You would have to alter the condition "If .UsedRange.Address <> "$A$1" Then " One possible alternative to above is WorksheetFunction.CountA(Range("A1:Z100")) <> 0

如果您需要帮助条件或代码，请通知我。

Please let me know if you need any assistance with the condition or the code.

这篇关于只能以PDF格式储存填充单元格的填充电子表格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！