我通过使用应用程序事件WorkbookOpen使其工作。当文件被拖到打开的Excel表格上时,它将尝试在Excel中作为单独的工作簿打开该文件,从而触发上述事件。这有点麻烦,但我使用了此链接https://bettersolutions.com/vba/events/excel-application-level-events.htm 作为参考。
唯一的问题是,如果文件不是Excel文件,则会弹出一个窗口,由于事件不会运行直到您解决弹出窗口,因此无法运行VBScript来摆脱它。下面是我的代码部分:
Public WithEvents App As Application
Private Sub App_WorkbookOpen(ByVal Wb As Workbook)
Dim path, pathExt As String
path = Wb.Name
pathExt = Mid(path, InStrRev(path, "."))
If pathExt = ".pdf" Then
Application.DisplayAlerts = False
Workbooks(Wb.Name).Windows(1).Visible = False
Dim n As String
n = Wb.FullName
Wb.Close
Call DragnDrop.newSheet(n)
Application.DisplayAlerts = True
End If
End Sub
编辑:
忘记了您需要通过在任何模块中发布以下代码来初始化应用程序事件
Option Explicit
'Variable to hold instance of class clsApp
Dim mcApp As clsApp
Public Sub Init()
'Reset mcApp in case it is already loaded
Set mcApp = Nothing
'Create a new instance of clsApp
Set mcApp = New clsApp 'Whatever you named your class module
'Pass the Excel object to it so it knows what application
'it needs to respond to
Set mcApp.App = Application 'mcApp.Whatever you named this Public
'WithEvents App As Application
End Sub
然后将此代码粘贴到ThisWorkbook Workbook_Open()中。
'Initialize the Application Events
Application.OnTime Now, "'" & ThisWorkbook.FullName & "'!Init"