从MS Access打开时，VBA Excel实例无法关闭-后期绑定仍然存在相同错误 [英] VBA Excel instance doesn't close when opened from MS Access - late binding still same error

问题描述

这是我的原始帖子

并且由于某种原因，当文件以这种方式放置时，出现了幻影，下一个.xlsm文件不会运行启动宏.如果我杀死Excel，然后打开.xlsm文件，则启动宏运行良好.

这是我正在使用的代码

这是从MS Access中打开excel文件的原因

 设置ExcelApp = CreateObject("Excel.Application")设置ExcelWbk = ExcelApp.Workbooks.Open(CurPath&"CurrentProjects.xlsm"，True)ExcelApp.Visible =假ExcelApp.Quit设置ExcelWbk = Nothing设置ExcelApp = Nothing 

这是启动宏.这放置在THIS WORKBOOK模块中.

  Private Sub Workbook_Open()CreateReporting结束子 

这是excel文件中的代码

 选项显式公共子CreateReporting()Dim MonthlyPath作为字符串，CurPath作为字符串，ProjectDate作为字符串，FullName作为字符串，CurLastRow作为长，CurRowNum作为长昏暗i作为范围，CurCell作为变体，CurRange作为范围昏暗的wbkM作为工作簿，wbkNewFile作为工作簿，wksReportDates作为工作表，wksCopyFrom作为工作表，wksCopyTo作为工作表，wks3作为工作表Dim rngCopyFrom作为范围，rngCopyTo作为范围CurPath = ThisWorkbook.Path&"\"使用ThisWorkbook.Sheets("QReportDates")MonthlyPath = .Range("A2").ValueProjectDate = .Range("B2").Value结束于全名= ProjectDate&"当前项目.设置wbkM ＝ Workbooks("CurrentProjects.xlsm")设置wksReportDates = wbkM.Sheets("QReportDates")如果Dir(MonthlyPath，vbDirectory)="然后MkDir(MonthlyPath)万一设置wbkNewFile = Workbooks.AddwbkNewFile.SaveAs MonthlyPath&全名设置wbkNewFile = Workbooks(FullName)wbkM.Sheets("TEMPLATEReporting").在以下位置复制:= wbkNewFile.Sheets(1)wbkNewFile.Sheets(2).Name =当前项目"；与wbkNewFile.Sheets("Sheet1").删除结束于设置wksCopyFrom = wbkM.Sheets("QCurrentProjects")设置wksCopyTo = wbkNewFile.Sheets(当前项目")使用wksCopyFromCurLastRow = .Cells(Rows.Count，"A").End(xlUp).Row设置rngCopyFrom = .Range("A2:K"& CurLastRow)结束于使用wksCopyTo设置rngCopyTo = .Range("A2:K"& CurLastRow)结束于rngCopyTo.Value = rngCopyFrom.Value对于wksCopyTo.Range("F2:F"& CurLastRow)中的每个i含工作表(1)超链接.添加锚点:=范围(``F''& i.Row)，地址:=范围(``F''& i.Row)，TextToDisplay:=范围(``C''& i.Row)).价值结束于下一个wbkNewFile.SavewbkNewFile.Close设置wbkNewFile = Nothing设置wksCopyTo = Nothing设置rngCopyTo = Nothing设置wksCopyFrom = Nothing设置rngCopyFrom = NothingwbkM.Worksheets("QReportDates").删除wbkM.Worksheets("QCurrentProjects").删除wbkM.Save关闭设置CurCell =否:设置CurRange =否:设置wbkM =否:设置wbkNewFile =否:设置wksReportDates =否:设置wksCopyFrom =否:设置wksCopyTo =否结束子 

请注意，在此版本中，我正在从excel中关闭.xlsm文件

  wbkM.Close 

但是我也尝试过从MS Access中关闭它，并从Excel中删除wbkM.Close.结果相同.

 设置ExcelApp = CreateObject("Excel.Application")设置ExcelWbk = ExcelApp.Workbooks.Open(CurPath&"CurrentProjects.xlsm"，True)ExcelApp.Visible =假ExcelWbk.关闭ExcelApp.Quit设置ExcelWbk = Nothing设置ExcelApp = Nothing 

解决方案

在@Parfait的帮助下(或者实际上，他通过3个线程为我修复了此问题)，现在所有方法都可以完美地工作.发布代码以供参考

这是打开xlsm文件，运行宏然后关闭xlsm文件的原因

 设置ExcelApp = CreateObject("Excel.Application")设置ExcelWbk = ExcelApp.Workbooks.Open(CurPath& MainProjectName&".xlsm"，True)ExcelApp.Visible =假ExcelApp.Run"MainProcedure"ExcelWbk.关闭ExcelApp.Quit设置ExcelWbk = Nothing设置ExcelApp = Nothing 

使用此代码，我不再有ghost xlsm文件

here's my original post VBA Excel instance doesn't close when opened from MS Access - late binding it was answered and i didn't realize that the xlsm file still isn't closed when I accepted the answer.

The reason why this is such a huge problem, is because when there's an open ghost .xlsm file, the next .xlsm file that opens doesn't run the startup macro. so, all my automations are failing because the previous .xlsm is sitting in memory.

So, I have many automations scheduled to run. They're all similar in a way that an MS Access program is run, then, from the MS Access program the .xlsm is opened, then the MS Access program emails the results. More than 1 automation can be running at the same time, so, sometimes when the .xlsm is opened, there might be other excel files open, or other .xlsm files, or none at all.

What's happening is that, for some reason, using the code below, the .xlsm file doesn't close fully. It's not visible in the taskbar and the only reason I know that it's still opened somewhere is by going into the VBA Projects window (see the screenshot below).

Here's a screenshot that shows whats on my taskbar and what's in the VBA Projects.

And, for some reason, when the file is sitting like this, ghosted, the next .xlsm file doesn't run the startup macro. If i kill Excel, and then open the .xlsm file, the startup macro runs fine.

Here's the code I'm using

This is what opens the excel file from MS Access

Set ExcelApp = CreateObject("Excel.Application")
Set ExcelWbk = ExcelApp.Workbooks.Open(CurPath & "CurrentProjects.xlsm", True)
ExcelApp.Visible = False
ExcelApp.Quit
Set ExcelWbk = Nothing
Set ExcelApp = Nothing

This is the startup macro. This is placed in the THIS WORKBOOK module.

Private Sub Workbook_Open()
    CreateReporting
End Sub

Here's the code in the excel file

Option Explicit

Public Sub CreateReporting()

    Dim MonthlyPath As String, CurPath As String, ProjectDate As String, FullName As String, CurLastRow As Long, CurRowNum As Long
    Dim i     As Range, CurCell As Variant, CurRange As Range
    Dim wbkM  As Workbook, wbkNewFile   As Workbook, wksReportDates As Worksheet, wksCopyFrom   As Worksheet, wksCopyTo   As Worksheet, wks3  As Worksheet
    Dim rngCopyFrom As Range, rngCopyTo As Range

    CurPath = ThisWorkbook.Path & "\"
    
    With ThisWorkbook.Sheets("QReportDates")
        MonthlyPath = .Range("A2").Value
        ProjectDate = .Range("B2").Value
    End With
    
    FullName = ProjectDate & " Current Projects.xlsx"

    Set wbkM = Workbooks("CurrentProjects.xlsm")
    Set wksReportDates = wbkM.Sheets("QReportDates")
 
    If Dir(MonthlyPath, vbDirectory) = "" Then
        MkDir (MonthlyPath)
    End If

    Set wbkNewFile = Workbooks.Add
    wbkNewFile.SaveAs MonthlyPath & FullName

    Set wbkNewFile = Workbooks(FullName)

    wbkM.Sheets("TEMPLATEReporting").Copy after:=wbkNewFile.Sheets(1)
    wbkNewFile.Sheets(2).Name = "Current Projects"

    With wbkNewFile
        .Sheets("Sheet1").Delete
    End With

    Set wksCopyFrom = wbkM.Sheets("QCurrentProjects")
    Set wksCopyTo = wbkNewFile.Sheets("Current Projects")
                                                           
    With wksCopyFrom
        CurLastRow = .Cells(Rows.Count, "A").End(xlUp).Row
        Set rngCopyFrom = .Range("A2:K" & CurLastRow)
    End With
                         
    With wksCopyTo
        Set rngCopyTo = .Range("A2:K" & CurLastRow)
    End With
                        
    rngCopyTo.Value = rngCopyFrom.Value
    
    For Each i In wksCopyTo.Range("F2:F" & CurLastRow)
        With Worksheets(1)
            .Hyperlinks.Add Anchor:=Range("F" & i.Row), Address:=Range("F" & i.Row), TextToDisplay:=Range("C" & i.Row).Value
        End With
    Next
      
    wbkNewFile.Save
    wbkNewFile.Close
    Set wbkNewFile = Nothing
    Set wksCopyTo = Nothing
    Set rngCopyTo = Nothing
    Set wksCopyFrom = Nothing
    Set rngCopyFrom = Nothing
     
    wbkM.Worksheets("QReportDates").Delete
    wbkM.Worksheets("QCurrentProjects").Delete
    wbkM.Save
    wbkM.Close
    
    Set CurCell = Nothing: Set CurRange = Nothing: Set wbkM = Nothing: Set wbkNewFile = Nothing: Set wksReportDates = Nothing:  Set wksCopyFrom = Nothing: Set wksCopyTo = Nothing
End Sub

Note that in this version I'm closing the .xlsm file from excel

wbkM.Close

But I also tried closing it from MS Access and removing the wbkM.Close from Excel. Same result.

Set ExcelApp = CreateObject("Excel.Application")
Set ExcelWbk = ExcelApp.Workbooks.Open(CurPath & "CurrentProjects.xlsm", True)
ExcelApp.Visible = False 
ExcelWbk.Close 
ExcelApp.Quit    
Set ExcelWbk = Nothing
Set ExcelApp = Nothing

解决方案

with @Parfait's help (or really, him fixing this for me in 3 threads), it all now works perfect. posting the code for reference

this is what opens the xlsm file, runs the macro and then closes the xlsm file

Set ExcelApp = CreateObject("Excel.Application")
Set ExcelWbk = ExcelApp.Workbooks.Open(CurPath & MainProjectName & ".xlsm", True)
ExcelApp.Visible = False
ExcelApp.Run "MainProcedure"
ExcelWbk.Close
ExcelApp.Quit
Set ExcelWbk = Nothing
Set ExcelApp = Nothing

using this code, i no longer have ghost xlsm files

这篇关于从MS Access打开时，VBA Excel实例无法关闭-后期绑定仍然存在相同错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！