☰

运行宏时打开隐藏的个人宏工作簿以关闭 ActiveWorkbook

tag-icon • tag-icon microsoft-excel vba
运行宏时打开隐藏的个人宏工作簿以关闭 ActiveWorkbook

我编写了一个宏，用于在运行一系列格式化并将其保存为 PDF 后关闭活动工作簿。但是，它会立即打开个人宏工作簿的隐藏版本，我不确定为什么或如何阻止它这样做。

有人能帮助解释为什么吗？或者 VBA 代码应该有所不同？

隐藏工作簿的图片出现

我认为第一部分可能是这样做的原因，但目前还无法证明。

        If closeOption = vbYes Then
            ActiveWorkbook.Close SaveChanges:=False
        End If

仅供参考，个人宏工作簿已被隐藏，因此这不是问题。

以下是完整代码：

Sub ActionItemReportPRO()
    ' Prompt for Report Type and Association Name
    Dim associationName As String
    associationName = InputBox("Enter the Association Name:", "Association Name")
    Dim reportName As String
    reportName = InputBox("What Kind Of Report Is This?", "Report Type", "ACTION ITEM REPORT")


    With ActiveSheet
        .Cells.Select
        With Selection
            .HorizontalAlignment = xlLeft
            .VerticalAlignment = xlTop
            .WrapText = True
            '... (other cell formatting)
        End With
    End With
    
    Application.PrintCommunication = False
    With ActiveSheet.PageSetup
        .PrintTitleRows = "$1:$1"
        .PrintTitleColumns = ""
        '... (other page setup configurations)
    End With
    Application.PrintCommunication = True
    
    ActiveSheet.PageSetup.PrintArea = ""
    
    Application.PrintCommunication = False
    With ActiveSheet.PageSetup
        .PrintTitleRows = ""
        .PrintTitleColumns = ""
    End With
    Application.PrintCommunication = True
    ActiveSheet.PageSetup.PrintArea = ""
    Application.PrintCommunication = False
    With ActiveSheet.PageSetup
        .CenterHeader = "&""-,Bold""" & associationName & Chr(10) & reportName
        .LeftFooter = "&B Confidential&B"
        .CenterFooter = "&D"
        .RightFooter = "Page &P"
        .PrintTitleRows = "$1:$1"
        .PrintTitleColumns = ""
        .LeftMargin = Application.InchesToPoints(0.25)
        .RightMargin = Application.InchesToPoints(0.25)
        .TopMargin = Application.InchesToPoints(0.75)
        .BottomMargin = Application.InchesToPoints(0.75)
        .HeaderMargin = Application.InchesToPoints(0.3)
        .FooterMargin = Application.InchesToPoints(0.3)
        .PrintHeadings = False
        .PrintGridlines = False
        .PrintComments = xlPrintNoComments
        .CenterHorizontally = False
        .CenterVertically = False
        .Orientation = xlPortrait
        .Draft = False
        .PaperSize = xlPaperLetter
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = False
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = 0
        .PrintErrors = xlPrintErrorsDisplayed
        .OddAndEvenPagesHeaderFooter = False
        .DifferentFirstPageHeaderFooter = False
        .ScaleWithDocHeaderFooter = True
        .AlignMarginsHeaderFooter = True
        .EvenPage.LeftHeader.Text = ""
        .EvenPage.CenterHeader.Text = ""
        .EvenPage.RightHeader.Text = ""
        .EvenPage.LeftFooter.Text = ""
        .EvenPage.CenterFooter.Text = ""
        .EvenPage.RightFooter.Text = ""
        .FirstPage.LeftHeader.Text = ""
        .FirstPage.CenterHeader.Text = ""
        .FirstPage.RightHeader.Text = ""
        .FirstPage.LeftFooter.Text = ""
        .FirstPage.CenterFooter.Text = ""
        .FirstPage.RightFooter.Text = ""
    End With
    Application.PrintCommunication = True
    
    ActiveWindow.FreezePanes = False
    ActiveWindow.View = xlPageLayoutView
    
    ' Default file name
    Dim pdfFileName As String
    pdfFileName = Application.GetSaveAsFilename(FileFilter:="PDF Files (*.pdf), *.pdf", Title:="Save As PDF", InitialFileName:="17 AIR")
    
    If pdfFileName <> "False" Then
        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=pdfFileName, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False
        
        
        ' Ask if the user wants to close the file
        Dim closeOption As VbMsgBoxResult
        closeOption = MsgBox("Would you like to close this file?", vbYesNo + vbQuestion, "Your File Has Been Saved!")
        
        If closeOption = vbYes Then
            ActiveWorkbook.Close SaveChanges:=False
        End If
    End If
    
End Sub

答案1

我认为您正在使用 VBA 关闭 PERSONAL.xlsb 之外唯一打开的工作簿。在这种情况下，您的隐藏工作簿并非“正在打开” - 它已经在后台打开。您实际上可以通过隐藏唯一可见的工作簿来复制此体验。

如果您希望 Excel 完全关闭，请将其添加到代码末尾。

If Workbooks.Count = 1 Then
    Application.Quit
End If

如果唯一剩余的工作簿是 PERSONAL.xlsb，则仅关闭 Excel。

注意：这也会强制关闭 VBA。执行此步骤时，对代码所做的任何更改都需要保存或丢弃。您可能会完全丢失未保存的代码。

祝您旅途愉快！

相关内容