如何在启动时检索文件名 [英] How to retrieve filename on startup

问题描述

我试图根据文件名打开excel时有条件地启动用户表单.但是，我认为代码在文件完全启动之前就已经在运行，这给了我一个错误.

I'm attempting to conditionally launch a user form when excel opens based on the file's name. However, I believe that the code is running before the file fully launches, giving me an error.

Private Sub Workbook_Open()
    Dim name As String
    name = ActiveWorkbook.FullName
    If InStr(name, "Export Checksheet") > 0 Then
        UserForm1.Show
    End If
End Sub

在已经打开的文件上进行测试可以很好地运行，但是尝试使其在文件打开时自然运行会导致运行时错误"91"，并指向定义 name 的行变量.

Testing this on a file that's already open runs fine, but trying to let it run naturally as the file opens causes a Run-time error '91' and points me at the line defining the name variable.

推荐答案

看起来像 ThisWorkbook 正在加载之前您要打开的其他任何工作簿，因此 ActiveWorkbook是 Nothing ，对它的任何成员调用都将引发错误91.

Looks like ThisWorkbook is loading before whatever other workbook you're opening, so ActiveWorkbook is Nothing and any member call against it will raise error 91.

Workbook_Open 处理程序将仅针对 ThisWorkbook (托管VBA项目的文档)运行-您需要在应用程序级别连接另一个处理程序，这样，只要打开任何工作簿，您就可以运行代码.

The Workbook_Open handler will only ever run for ThisWorkbook (the document that's hosting the VBA project) - what you need is to wire up another handler, at application level, so that you can run code whenever any workbook is opened.

更改您的 Workbook_Open 处理程序，以将对 Application 实例的引用捕获到模块级的 WithEvents 对象变量中:

Change your Workbook_Open handler to capture a reference to the Application instance into a module-level WithEvents object variable:

Option Explicit
Private WithEvents app As Excel.Application

Private Sub Workbook_Open()
    Set app = Me.Application
End Sub

现在从左侧的下拉菜单中选择 app ，然后为 WorkbookOpen 事件创建处理程序- 是您想要的位置验证是否需要弹出该对话框:

Now select app from the left-hand dropdown, and create a handler for the WorkbookOpen event - that is where you'll want to verify whether you need to pop up that dialog:

Private Sub app_WorkbookOpen(ByVal Wb As Workbook)
    If InStr(Wb.FullName, "Export Checksheet") > 0 Then
        With New UserForm1
            .Show
        End With
    End If
End Sub

请注意，应用程序范围的事件为提供了 Workook 对象，因此您不必关心它是否是 ActiveWorkbook

Note that the application-wide event provides you with the Workook object, so you don't need to care whether it's the ActiveWorkbook.

这篇关于如何在启动时检索文件名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！