gpt4 book ai didi

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

转载 作者:行者123 更新时间:2023-12-04 18:06:07 25 4
gpt4 key购买 nike

我目前正在尝试修改 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

最佳答案

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

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

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

关于excel - 仅将填充了单元格的电子表格保存为 PDF,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/26234627/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com