首页 文章

锁定Excel电子表格

提问于
浏览 次
1

我在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

我错过了什么？

编辑：

这是代码的基础，包括新的“退出”行：

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 vba outlook outlook-vba

3 回答

  • 1
    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

    你需要退出应用程序 xl.Quit Set "" = Nothing 并不是必需的

    回复于
  • 2

    你可以尝试这样的事情

    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
    回复于

相关问题