excel - Excel VB Workbook_Open() 只能手动工作

Question

我有一个工作簿，可以打开另一个工作簿并复制一系列数据。之后，它会收集一些数据并发送一封电子邮件。我遇到的问题是 Workbook_Open() 函数在工作簿打开时自动运行它不能正常工作。如果我打开原始工作簿并从 VB 编辑器运行它，一切正常。但是，如果我让它自动运行，它会挂起并说“工作表类的选择方法失败”

Private Sub Workbook_Open()
 Call send_Mail
End Sub

Private Sub send_Mail()
   Dim Mail As CDO.Message
   Set Mail = New CDO.Message
   
   Dim strFilename As String: strFilename = "S:\Office\Requisition Logs\FY22\FY 2022.xlsx"
   Dim wb2 As Workbook
   Set wb2 = Workbooks.Open(Filename:=strFilename)
   Dim vals As Variant

   'Store the value in a variable:
   vals = wb2.Sheets("FY2022 Log").Range("A2:AJ500").Value
   'Close wb2:
   wb2.Close savechanges:=False
   
   Dim wb1 As Workbook
   Set wb1 = ThisWorkbook

   'Use the variable to assign a value to the other file/sheet:
   wb1.Sheets("FY2022 Log").Range("A2:AJ500").Value = vals
   wb1.Sheets("FY2022 Log").Select
   
   
   Dim x As Integer
   x = 1
   
   Dim lastRow As Integer
   Dim lastCol As Integer
   Dim startRow As Integer
   Dim numRows As Integer
   startRow = 2
   .............................

我在 ThisWorkbook 的 Workbook 选项卡中有 Workbook_Open()

标签： excelvba