Excel另存为PDF并覆盖现有

Question

这是我遇到的问题。

我创建了一个 Excel 项目。在我的项目中，我将当前工作簿保存为 PDF，并使用日志信息和 PDF 的超链接更新现有工作簿( Step1 )。

在项目的另一点，我将项目重新保存为同一个 PDF(以覆盖现有 PDF)。此时我尝试重新保存收到的 PDF:

run-time error-1004

文档未保存，可能已打开或遇到错误。这是我使用的 2 个脚本。

我想这可能与 的第二部分有关。第1步脚本(打开工作簿并更新日志信息时，它可能无法正常关闭)

请任何帮助将不胜感激，因为我将继续排除故障。

第一步:

Sub Step1()
Dim rng As Range
Dim nwb As Workbook
Dim FileName As String
Dim var
Dim var1
Dim var2
Dim var3
Dim var4
Dim var5
Dim var6
var1 = frmsetup.cmbauditor.Text
var2 = frmsetup.lblsequence.Caption
var3 = frmsetup.cmbtrimstyle.Text
var = "SEQ-" & frmsetup.lblsequence.Caption & " "
var4 = frmsetup.lbldate.Caption
FileName = var & var4

With Sheets(Array("END RESULTS", "DRIVER SEAT", "PASSENGER SEAT", "40% SEAT", "60% SEAT", "RSC SEAT")).Select
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:= _
    "H:\APPLICATIONS\SEAT AUDIT\QUERY RESULTS\SEAT AUDIT - PDF\" & FileName & ".pdf", Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
    False
End With
Application.ScreenUpdating = False
Application.WindowState = xlMaximized

这是 的下一部分第1步 ，它会打开一个现有的工作簿并添加日志信息，包括指向 pdf 的超链接:

Set nwb = Workbooks.Open("H:\APPLICATIONS\SEAT AUDIT\LOG FILES\Seat Audit Log.xlsm")
With Sheets("Seat Audit Log")
nextrow = Range("B" & Rows.Count).End(xlUp).Row + 1
Cells(nextrow, 1).Value = var1
Cells(nextrow, 2).Value = var2
Cells(nextrow, 3).Value = var3
Cells(nextrow, 4).Value = var4
Set rng = .Range("E" & nextrow)
rng.Parent.Hyperlinks.Add Anchor:=rng, Address:="H:\APPLICATIONS\SEAT AUDIT\QUERY RESULTS\SEAT AUDIT - PDF\" & FileName & ".pdf", TextToDisplay:="CLICK HERE!"
End With
Application.ScreenUpdating = True
ActiveWorkbook.Save
ActiveWindow.Close
End Sub

这是 第二步重新另存为 PDF 以覆盖步骤 1 中的现有 PDF:

Sub Step2()
Dim FileName As String
Dim var
Dim var4
var = "SEQ-" & frmsetup.lblsequence.Caption & " "
var4 = frmsetup.lbldate.Caption
FileName = var & var4

With Sheets(Array("END RESULTS", "DRIVER SEAT", "PASSENGER SEAT", "40% SEAT", "60% SEAT", "RSC SEAT", "ACTIONS")).Select
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:= _
    "H:\APPLICATIONS\SEAT AUDIT\QUERY RESULTS\SEAT AUDIT - PDF\" & FileName & ".pdf", Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
    False
End With

End Sub

Answer 1

您的大部分 With block 似乎构造不佳，可能会导致一些错误(或者，也许不是，很难确定)。通常，您会将对象限定为 With目的。就您而言，除了一两行代码外，您似乎没有这样做。

您不需要变量来表示表单控件，这样做会使您的代码更难阅读。我已经相应地修改了 Step1。

但是，主要问题是:通常当您尝试 Save一个文件，你首先需要检查同一个文件是否已经存在，如果存在，删除它。

Sub Step1()
Dim rng As Range
Dim nwb As Workbook
Dim FileName As String


FileName = "SEQ-" & frmsetup.lblsequence.Caption & " " & frmsetup.lbldate.Caption
'## Add the PATH and EXTENSION to the filename
FileName = "H:\APPLICATIONS\SEAT AUDIT\QUERY RESULTS\SEAT AUDIT - PDF\" & FileName & ".pdf"

With Sheets(Array("END RESULTS", "DRIVER SEAT", "PASSENGER SEAT", "40% SEAT", "60% SEAT", "RSC SEAT")).Select
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:= _
    FileName, Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
    False
End With
Application.ScreenUpdating = False
Application.WindowState = xlMaximized

**'This is the next part of the step1, it opens a existing workbook and adds log information including hyperlink to pdf'**

Set nwb = Workbooks.Open("H:\APPLICATIONS\SEAT AUDIT\LOG FILES\Seat Audit Log.xlsm")
With Sheets("Seat Audit Log")
    nextrow = Range("B" & .Rows.Count).End(xlUp).Row + 1
    .Cells(nextrow, 1).Value = frmsetup.cmbauditor.Text
    .Cells(nextrow, 2).Value = frmsetup.lblsequence.Caption
    .Cells(nextrow, 3).Value = frmsetup.cmbtrimstyle.Text
    .Cells(nextrow, 4).Value = frmsetup.lbldate.Caption
    .Set rng = .Range("E" & nextrow)
    rng.Parent.Hyperlinks.Add Anchor:=rng, Address:=FileName, TextToDisplay:="CLICK HERE!"
End With
Application.ScreenUpdating = True
ActiveWorkbook.Save
ActiveWindow.Close
End Sub

Sub Step2()
Dim FileName As String

FileName = "SEQ-" & frmsetup.lblsequence.Caption & " " & frmsetup.lbldate.Caption
'## Add the PATH and EXTENSION to the filename
FileName = "H:\APPLICATIONS\SEAT AUDIT\QUERY RESULTS\SEAT AUDIT - PDF\" & FileName & ".pdf"

'## Check to see if this file exists, and delete it if it does
If Dir(FileName) <> vbNullString Then
    Kill FileName
End If

With Sheets(Array("END RESULTS", "DRIVER SEAT", "PASSENGER SEAT", "40% SEAT", "60% SEAT", "RSC SEAT", "ACTIONS")).Select
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:= _
    FileName, Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
    False
End With

End Sub