excel - 退出/重新启动控制 excel 的 word 宏

Question

我正在运行一个 word 宏

初始化 Excel

在 excel 中创建临时工作簿

退出word userform时，终止excel

但是，似乎有一些剩余的 Excel 实例/工作簿没有完全关闭，因为当我再次启动 word 宏时，我收到错误:462，远程服务器机器...

在 Word 文档中初始化用户表单:

private sub commandbutton1_click()
    dim exc as excel.application
    set exc as new excel.application
    dim wb as excel.workbook

    set wb = exc.workbook.saveas filename:="wordexc"      'creates temp workbook
    userform1.show
end sub

run excel processing via a userform1 commandbutton:

private sub commandbutton2_click()

    excel.workbook("wordexc").close   'closes temp workbook

    dim wb as excel.workbook
    set wb = excel.workbook.saveas filename:="wordexc"     'this wb is for actual use
    'i do not delete this wb after running cuz it has stored data that will be used
    'if user cliks commandbutton2 again, it will be deleted and new wbook with new data  
    'is created


    'processing code

end sub

private sub queryclose (etc...)
    'Close Excel
    Dim sKillExcel As String

     sKillExcel = "TASKKILL /F /IM Excel.exe"
     Shell sKillExcel, vbHide

end sub

顺便说一句，我认为问题是最后一部分:

 Dim sKillExcel As String

 sKillExcel = "TASKKILL /F /IM Excel.exe"
 Shell sKillExcel, vbHide

因为如果我停止宏并在任务管理器中终止 EXCEL，如果我再次运行宏，它不会给我这个问题......
一世
我还尝试了其他方法，例如调用我保存在目录中的excel工作簿，而不是通过一个子目录中的createobject(“excel.application”)exc.quit，但我必须使用上面的终止代码，否则EXCEL仍然显示在任务管理器中。

Answer 1

除了没有正确地声明你的对象之外，你也没有(如果我可以这么说的话)正确地冲马桶。

请参阅此示例(未测试)

我使用后期绑定(bind)而不是早期绑定(bind)，以便此代码与任何版本的 Excel 兼容

'~~> Define your Excel objects so that they are available 
'~~> in other userform sub procedures as well.
Dim oXLApp As Object, oXLwb As Object

Private Sub CommandButton1_Click()
    Set oXLApp = CreateObject("Excel.Application")

    '~~> Show Excel
    oXLApp.Visible = True

    '~~> Add a new workbook
    Set oXLwb = oXLApp.Workbooks.Add

    '
    '~~> Do some stuff
    '

    '~~> Save the file
    oXLwb.SaveAs "C:\wordexc.xlsx", FileFormat:=51

    oXLwb.Close SaveChanges:=False
End Sub

'~~> Close and Quit Excel (Flush Toilet)
Private Sub CommandButton2_Click()
    oXLApp.Quit
    Set oXLwb = Nothing
    Set oXLApp = Nothing
End Sub