excel - 从 Outlook VBA 激活工作表

Question

在 Windows 10 PC 上，我使用的是 Office Professional Plus 2013-32 位，包括 Outlook 和 Excel。我在 Outlook 中有一个发送电子邮件的宏。

该宏使用两个工作簿:一个作为数据源，另一个作为日志。

我无法激活数据源工作表。我没有收到 VBA 错误，但是当我测试时发现激活失败。

见行:varTest = ActiveSheet.Name .以下是宏的一些部分:

 Public objEmailWorkbook         As Excel.Workbook
 Public objEmailWorksheet        As Excel.Worksheet
 Public objLogWorkbook           As Excel.Workbook
 Public objLogWorksheet          As Excel.Worksheet
Sub Send_Emails()
 Dim objExcelApp                 As Excel.Application
 Dim strLogFullName              As String
 'Instantiate an instance of the Excel application.
 Set objExcelApp = CreateObject("Excel.Application")
 strXlsPathName = Environ("USERPROFILE") & "\Documents\"
 strLogFileName = "email_log.xlsx"
 strLogFullName = strXlsPathName & strLogFileName
 'Instantiate the Log Workbook object
 Set objLogWorkbook = objExcelApp.Workbooks.Open(strLogFullName, True, False)
 'There's only one worksheet in the Log workbook.  Identify the Log sheet name.
 strLogSheetName = objLogWorkbook.Sheets(1).Name
 'Instantiate the Log Worksheet object
 Set objLogWorksheet = objLogWorkbook.Sheets(strLogSheetName)
 objLogWorksheet.Activate

 'Instantiate the eMail Address Workbook object
 strEmailFileName = "emailTest.csv"
 strEmailFullName = strXlsPathName & strLogFileName
  'Instantiate the Email Worksheet object
 Set objEmailWorkbook = objExcelApp.Workbooks.Open(strEmailFullName, True, False)
 'There is only one sheet within a workbook.
 'Determine the eMail address sheet name.
 strEmailSheetName = objEmailWorkbook.Sheets(1).Name

 'Instantiate the eMail address worksheet object
 Set objEmailWorksheet = objEmailWorkbook.Sheets(strEmailSheetName)
 objEmailWorksheet.Activate
 '***Other Processing Code Here
 '***lngLastSheetCol and strSheetFieldName values are set here
 'Re-activate objEmailWorksheet
 '***The following ACTIVATE command doesn't appear to work
 objEmailWorksheet.Activate
 varTest = ActiveSheet.Name
 'Define rngSearchText as the header row in the worksheet
 Set rngSearchText = ActiveSheet.Range(Cells(1, 1), Cells(1, lngLastSheetCol))
 With rngSearchText
   'Find the column in the worksheet for strSheetFieldName
   Set rngFoundText = .Find(What:=strSheetFieldName, LookAt:=xlWhole, _
                                         MatchCase:=False, SearchFormat:=False)
   If Not rngFoundText Is Nothing Then
     lngFoundTextCol = rngFoundText.Column
   End If
 End With
Exunt:
Set objEmailWorksheet = Nothing
Set objEmailWorkbook = Nothing
Set objLogWorksheet = Nothing
Set objLogWorkbook = Nothing
Set objExcelApp = Nothing
Set rngSearchText = Nothing
Set rngFoundText = Nothing

End Sub

objEmailWorksheet.Activate命令永远不会起作用。当我单步执行代码时，varTest 的值为“log”。我知道电子邮件工作表的值是“emailTest”。

由于未激活电子邮件工作表，因此以下搜索不会返回正确的结果。

是否有另一种方法来激活工作表或以其他方式解决问题？

Answer 1

您必须指定 Excel 应用程序 objExcelApp

varTest = objExcelApp.ActiveSheet.Name

我建议始终激活 Option Explicit : 在 VBA 编辑器中转到工具 › 选项 › Require Variable Declaration .

但不是这个，我推荐以下

'objEmailWorksheet.Activate 'you don't need activate at all
'varTest = objEmailWorksheet.Name 'this line is not needed too

Set rngSearchText = objEmailWorksheet.Range(objEmailWorksheet.Cells(1, 1), objEmailWorksheet.Cells(1, lngLastSheetCol))

指定 每 Range和 Cells区域或单元格在哪个工作表中。否则 Excel 无法知道这一点。如果你这样做，你永远不需要 .Activate任何床单。

你可能会从阅读中受益
How to avoid using Select in Excel VBA . .Activate 也是如此应尽可能避免。

还有这3行

 strLogSheetName = objLogWorkbook.Sheets(1).Name
 Set objLogWorksheet = objLogWorkbook.Sheets(strLogSheetName)
 'objLogWorksheet.Activate 'don't need to activate!

只能减少到 1 行

 Set objLogWorksheet = objLogWorkbook.Sheets(1)

这两条线

strEmailSheetName = objEmailWorkbook.Sheets(1).Name
Set objEmailWorksheet = objEmailWorkbook.Sheets(strEmailSheetName)

只能减少到 1 行

Set objEmailWorksheet = objEmailWorkbook.Sheets(1)