如何在Outlook VBA程序中终止Excel进程，使Excel电子表格不被锁定？

tv6aics1 于 2022-11-26 发布在其他

关注(0)|答案(3)|浏览(179)

我在Outlook VBA中编写了一个程序，它根据Excel电子表格的内容创建电子邮件。
当程序终止时，我继续运行一个“EXCEL.EXE”进程，该进程锁定电子表格，因此其他人无法打开它。
在代码中，我有三个Excel对象：

Dim xl As Excel.Application
Dim xlwb As Excel.Workbook
Dim xlsheet As Excel.Worksheet

最后，我关闭工作簿并将所有变量设置为Nothing：

xlwb.Close

Set xlsheet = Nothing
Set xlwb = Nothing
Set xl = Nothing

以下是代码的基本结构，包括新的“Quit”行：

Dim xl As Excel.Application
Dim xlwb As Excel.Workbook
Dim xlsheet As Excel.Worksheet
Dim ol As Outlook.Application
Dim Mail As MailItem
Set xl = Excel.Application
Set ol = Outlook.Application
Set xlwb = xl.Workbooks.Open("C:\sheet.xlsx", ReadOnly)

For Each xlsheet In xlwb.Worksheets
    for xlrow = 1 to 5
        If xlsheet.Cells(xlRow, 1).Value = "John" Then
           msg=msg & xlsheet.Cells(xlRow, 2).Value
        end if
    next
next

Set Mail = ol.CreateItem(olMailItem)
   
Mail.To = "A@b.c"
Mail.Subject = "John's email"
Mail.Body = msg
Mail.Send

xlwb.Close

xl.Quit

Set ol = Nothing
Set xlsheet = Nothing
Set xlwb = Nothing
Set xl = Nothing

excel

来源：https://stackoverflow.com/questions/33777031/how-to-terminate-excel-process-in-outlook-vba-program-so-excel-spreadsheet-is-no

3条答案

按热度按时间

pjngdqdw1#

您需要退出应用程序xl.QuitSet "" = Nothing不是真正必要的

赞(0）回复(0）举报 2022-11-26

ndasle7k2#

xl.quit

这将关闭应用程序（您只是关闭工作簿，而不是代码中的应用程序），因此只需在将变量设置为空之前放置此变量。
编辑：请将您的子目录更改为：

Dim xl As New Excel.Application
Dim xlwb As Excel.Workbook
Dim xlsheet As Excel.Worksheet
Dim ol As Outlook.Application
Dim Mail As MailItem

Set ol = Outlook.Application
Set xlwb = xl.Workbooks.Open("C:\sheet.xlsx", ReadOnly)
For Each xlsheet In xlwb.Worksheets

For xlRow = 1 To 5

If xlsheet.Cells(xlRow, 1).Value = "John" Then
    msg = msg & xlsheet.Cells(xlRow, 2).Value

End If

Next

Next

Set Mail = ol.CreateItem(olMailItem)

Mail.To = "A@b.c"
Mail.Subject = "John's email"
Mail.Body = msg
Mail.Send

xlwb.Close

xl.Quit

Set ol = Nothing
Set xlsheet = Nothing
Set xlwb = Nothing
Set xl = Nothing

赞(0）回复(0）举报 2022-11-26

ogq8wdun3#

你可以试试这样

Option Explicit
Sub Excel()
    '//  Declare variables
    Dim xlApp As Excel.Application
    Dim xlWb As Excel.Workbook
    Dim xlSheet As Excel.Worksheet
    Dim xlStarted As Boolean

    On Error Resume Next
    Set xlApp = GetObject(, "Excel.Application")

    If Err <> 0 Then
        Application.StatusBar = "Please wait while Excel source is opened ... "
        Set xlApp = CreateObject("Excel.Application")
        xlStarted = True
    End If


    ' your code here



    '// Close & SaveChanges
    xlWb.Close SaveChanges:=True
    If xlStarted Then
        xlApp.Quit
    End If

    '// clean up
    Set xlApp = Nothing
    Set xlWb = Nothing
    Set xlSheet = Nothing
 End Sub

赞(0）回复(0）举报 2022-11-26

我来回答

如何在Outlook VBA程序中终止Excel进程，使Excel电子表格不被锁定？

3条答案

相关问题

热门标签

最新问答