Excel VERY VERY strange behaviour!!!

[pobo1234]

I'm currently trying to migrate from Excel 2007 to 2013. I have a setup where one workbook (Scheduler.xlsm) calls a set of procedures in another (Core Workbook.xlsm) through automation which updates an MS Access database with dates from the Core Workbook using DAO. If I run the test in 2007 it closes all the workbooks as it should, however if I run the same test in 2013 it leaves a read only copy of the Core Workbook open.

I have not uploaded the project as I can't upload files but please PM/message me and I can send you the zipped folder. The test can be performed by clicking the "Run Test" button in Sheet1 of the Scheduler.xlsm workbook. All references are relative to the 2013 Migration folder and the test can be run using both 2007 and 2013.

Can anyone explain this difference in behaviour and suggest how to overcome it retaining the same or similar code please?

Many thanks

Pobo123

 

[Derek Brown]

Not sure why there should be a difference between 2007 and 2013 but the following explains how this could happen and how to overcome such problems.
Program won't quit
Although this refers to Excel, the same issue would apply to Access, Word etc.

 

[pobo1234]

Thanks Derek - I have already applied all the principles and guidelines in your link in my project but have still been getting this strange behavior. I think this is being caused by something not covered in the Program won't quit article.

 

[MrKowz]

Can you post the code, highlighting the line(s) that are not functioning as you believe they should?

 

[pobo1234]

Hi MrKowz

I've stripped out the relevant code. The line of code dbsCurrent.Execute strSQL highlighted in purple causes a second read only workbook (Workbook 2) to be opened and left open in 2013 but no copies of Workbook 2 are left open when this code is executed using 2007:

Workbook1
=========


Public Function RefreshTool() As Boolean
    
    Dim appExcel As Excel.Application
    Dim strToolPath As String
    Dim wkbTool As Workbook
    
    Set appExcel = New Excel.Application
    
    appExcel.Visible = True
    'appExcel.WindowState = xlMinimized
    
    strToolPath = ThisWorkbook.Path & "\Core Workbook.xlsm"
    
    Set wkbTool = appExcel.Workbooks.Open(strToolPath, UpdateLinks:=False, ReadOnly:=False)
    
    appExcel.Run ("AutoRunReport")
    
    appExcel.Workbooks.Close
    
    
    appExcel.Quit
    Set appExcel = Nothing
    
    
End Function






Workbook2
=========

Public Sub AutoRunReport()


    RefreshNAVCalculationBasketDB
    
End Sub




Public Function RefreshNAVCalculationBasketDB() As Boolean


    Dim dbsCurrent As DAO.Database
    Dim rstCurrent As DAO.Recordset
    Dim ws As DAO.Workspace
    Dim strSQL As String


    Const PROCEDURE_NAME = "RefreshNAVCalculationBasketDB"
   
    On Error GoTo ERROR_HANDLER1
 
   
    RefreshNAVCalculationBasketDB = False
   
    Set ws = DBEngine.Workspaces(0)
    Set dbsCurrent = ws.OpenDatabase(ThisWorkbook.Path & "\Employee.accdb")
   
 
    'empty the data tables
    dbsCurrent.Execute "DELETE * FROM [tblEmployees]"
    


    ThisWorkbook.Save
   
    strSQL = "INSERT INTO [tblEmployees] ('Employee Code', 'Employee Ac', 'Date T-1','Date T')" _
    & "SELECT * FROM [Excel 12.0 Macro;HDR=True;IMEX=1;READONLY=TRUE;database=" & _
    ThisWorkbook.FullName & "].[Import & Control$C26:F96]"
    
[COLOR=#800080]    dbsCurrent.Execute strSQL[/COLOR]
      
    varEnableEventsState = Application.EnableEvents
    varCalculationState = Application.Calculation
    Application.DisplayAlerts = True
    
    
    RefreshNAVCalculationBasketDB = True
   
    Exit Function
   


ERROR_HANDLER1:
   
    MsgBox Err.Number & vbCrLf & "Error Description: " & Err.Description, _
    vbCritical, "Excel Workbook"

End Function

 

[Derek Brown]

Where are you setting dbsCurrent to 'Nothing'?
Is that in the part of the code that you did not 'strip out' along with the other objects that do not appear to be released at the end of the code?

 

[pobo1234]

I am closing it afterwards but have omitted that part. Anyway Derek, I don't think this is causing the issue I have raised. As I said previously in 2007 the same code (dbsCurrent.Execute strSQL) does not open another read only version of the executing workbook but in 2013 it does. Closing that read only workbook is not the issue as it shouldn't be opening in the first place.

The issue is that automation through another workbook using 2013 is not producing the same result as running the code directly from the main executing workbook. Yet, automation through another workbook using 2007 does produce the same result as running the code directly from the main executing workbook. 2007 does not open a read only copy of the workbook when performing a DAO query on itself. Why is 2013 doing this when automation is used to execute code in a workbook that performs the dbsCurrent.Execute strSQL statement on itself.

Is this a 2013 bug and is it a known one or is this new expected behaviour in 2013?

 

[pobo1234]

So as not to confuse I should have stripped out the following lines from the code snippet above as well:

varEnableEventsState = Application.EnableEvents
varCalculationState = Application.Calculation
Application.DisplayAlerts = True

 

[pobo1234]

Any more ideas on this please - still haven't been able to resolve this one yet

 

[Derek Brown]

Sorry, but I am going to have to 'pass' on this one.
There are too many unanswered questions in my mind because I am unable to see all the relevant code - and I don't do PMs so would be unable to obtain copies of the workbooks and database etc. If the data belongs to your employer, I would not want it anyway.
You have taken out code from that which you have given and that could have been relevant (the 3 lines in Post 9 make no difference whatsoever) so I would only be guessing what code is running.
I would also have to assume that you are running on two separate Windows PCs - one for 2007 and the other for 2013 - and I don't know what operating systems you are using.
Could it be the reference to Excel 12 (Excel 2007) in the SQL string that is not helping?
And did you change the 'References' to the later version?