After 365 upgrade, VBA statement Sheet2.ListObjects(1).QueryTable.Refresh (False) crashes

Question

This report has been working normally until upgrading to MS365. I use MSQuery as I prefer using SQL with my reporting.

I suspected autosave is trashing the file and removed autosave and autorecovery with no change in results.

I have ran in safe mode with the same problems. There are six queries in the file, they all behave similarly.

I can run normal as long as I open the file without making a single change in any query, or change, save, exit Excel,restart.

After receiving the automation error, I can open MSquery and return results in MSQuery, but If I return to Exel, Excel stops working:

Amy ideas would be most helpful, these are automated reports, so all changes must be in VBA

Answer 1

Hi CrazyVan,

Greetings! Thank you for posting to Microsoft Community.

An automation error could occur within Excel if you load a form while another object (like the worksheet or range object) is still referenced.  Remember to set your object references to NOTHING after you have finished writing the code to control them with.

Reference: How Do You Fix an Automation Error in VBA?

Could you share your macros code via private message? We can find the object which will cause this error together.

Best Regards,

Snow Lu

Answer 2

Thank You for your consideration.

Here is the function, There is only one query on each visible worksheet, I am using Sheet objects, so I have not set to nothing. The Sheetx querytable refreshes don't crash on the same worksheet, the crashes are not always on the same worksheet.

I have the same problem with other reports that have more worksheets and queries since upgrading to Office 365.

I am going to convert to using querytable objects to attempt to control the connection.

I do use tables for each query, and as you will notice I don't like to use formulas in table, without controlling the formulas due to excel table formula problems.

Thanks again

Sub GetProbationers()

Dim Month As Variant, Year As Variant, vCases As Variant

Dim dStart As Date, dEnd As Date, dToday As Date

Dim bSuccess As Boolean, bFound As Boolean

Dim lvar As Long, lReportRow As Long, lCases As Long, lCustody As Long, lReport As Long

Dim iCurrentAge As Integer

Dim vReleased As Variant, vStatus As Variant, vStartDate As Variant, vToday As Variant, vMonth As Variant

On Error GoTo Error_Exit

Application.ScreenUpdating = False

If NewReport.cbMonth.ListIndex <= 0 And NewReport.cbYear.ListIndex <= 0 Then

MsgBox "Please Select Report Month and Report Year before Running Report Command" 

Exit Sub 

End If

If NewReport.cbMonth.ListIndex <= 0 Then

MsgBox "Please Select Report Month before Running Report Command" 

Exit Sub 

End If

If NewReport.cbYear.ListIndex <= 0 Then

MsgBox "Please Select Report Year before Running Report Command" 

Exit Sub 

End If

NewReport.Hide

Month = NewReport.cbMonth.ListIndex

Year = NewReport.cbYear.Value

dStart = DateSerial(Year, Month, "01")

Sheet99.Cells(2, 2) = dStart

If Month = 12 Then

Sheet99.Cells(2, 3) = DateSerial(Year, Month, "31") 

Else

Sheet99.Cells(2, 3) = DateSerial(Year, Month + 1, "01") - 1    'Get Last Day of Report Month using 1st day of next month - 1 

End If

dEnd = Sheet99.Cells(2, 3)

dToday = Now

vMonth = DatePart("m", dToday)

'Update all queries

Application.StatusBar = "Refreshing " & Sheet2.Name & " Worksheet"

Sheet2.ListObjects(1).QueryTable.Refresh (False)

Sheet2.ListObjects(1).Range.AutoFilter ' Reset Autofilter if on/off

Sheet2.ListObjects(1).Range.AutoFilter ' total reset

Sheet2.PageSetup.CenterHeader = "&B&14Del Norte County Probation&B&12" & Chr$(13) & Sheet2.Name & Chr$(13) & Sheet99.Cells(2, 2) & " - " & Sheet99.Cells(2, 3)

Sheet2.PageSetup.CenterFooter = "Page &P/&N"

Sheet2.PageSetup.RightFooter = "Printed: " & Now

Application.StatusBar = "Refreshing " & Sheet3.Name & " Worksheet"

Sheet3.ListObjects(1).QueryTable.Refresh (False)

Sheet3.ListObjects(1).Range.AutoFilter ' Reset Autofilter if on/off

Sheet3.ListObjects(1).Range.AutoFilter ' total reset

Sheet3.PageSetup.CenterHeader = "&B&14Del Norte County Probation&B&12" & Chr$(13) & Sheet3.Name & Chr$(13) & Sheet99.Cells(2, 2) & " - " & Sheet99.Cells(2, 3)

Sheet3.PageSetup.CenterFooter = "Page &P/&N"

Sheet3.PageSetup.RightFooter = "Printed: " & Now

Sheet4.PageSetup.CenterHeader = "&B&14Del Norte County Probation&B&12" & Chr$(13) & Sheet4.Name & Chr$(13) & Sheet99.Cells(2, 2) & " - " & Sheet99.Cells(2, 3)

Sheet4.PageSetup.CenterFooter = "Page &P/&N"

Sheet4.PageSetup.RightFooter = "Printed: " & Now

Application.StatusBar = "Refreshing " & Sheet5.Name & " Worksheet"

Sheet5.ListObjects(1).QueryTable.Refresh (False)

Sheet5.ListObjects(1).Range.AutoFilter ' Reset Autofilter if on/off

Sheet5.ListObjects(1).Range.AutoFilter ' total reset

Sheet5.PageSetup.CenterHeader = "&B&14Del Norte County Probation&B&12" & Chr$(13) & Sheet5.Name & Chr$(13) & Sheet99.Cells(2, 2) & " - " & Sheet99.Cells(2, 3)

Sheet5.PageSetup.CenterFooter = "Page &P/&N"

Sheet5.PageSetup.RightFooter = "Printed: " & Now

Application.StatusBar = "Refreshing " & Sheet6.Name & " Worksheet"

Sheet6.ListObjects(1).QueryTable.Refresh (False)

Sheet6.ListObjects(1).Range.AutoFilter ' Reset Autofilter if on/off

Sheet6.ListObjects(1).Range.AutoFilter ' total reset

Sheet6.PageSetup.CenterHeader = "&B&14Del Norte County Probation&B&12" & Chr$(13) & Sheet6.Name & Chr$(13) & Sheet99.Cells(2, 2) & " - " & Sheet99.Cells(2, 3)

Sheet6.PageSetup.CenterFooter = "Page &P/&N"

Sheet6.PageSetup.RightFooter = "Printed: " & Now

Application.StatusBar = "Refreshing " & Sheet7.Name & " Worksheet"

Sheet7.ListObjects(1).QueryTable.Refresh (False)

Sheet7.ListObjects(1).Range.AutoFilter ' Reset Autofilter if on/off

Sheet7.ListObjects(1).Range.AutoFilter ' total reset

Sheet7.PageSetup.CenterHeader = "&B&14Del Norte County Probation&B&12" & Chr$(13) & Sheet7.Name & Chr$(13) & Sheet99.Cells(2, 2) & " - " & Sheet99.Cells(2, 3)

Sheet7.PageSetup.CenterFooter = "Page &P/&N"

Sheet7.PageSetup.RightFooter = "Printed: " & Now

Application.StatusBar = "Updating Summary Report Worksheet"

If Sheet5.ListObjects(1).ListRows.Count > 0 Then 'Caseload Unions data exists(PRCS, 1170(h), Standard)

vCases = Sheet5.ListObjects("Petitions").DataBodyRange 

End If

For lCases = 1 To UBound(vCases) ' Get Current Probationers

iCurrentAge = 0 

If Not IsEmpty(vCases(lCases, 9)) Then 'DOB Exists 

    iCurrentAge = DatePart("yyyy", dToday) - DatePart("yyyy", vCases(lCases, 9)) 'iCurrentAge if birthday occured in current year 

     If DatePart("m", vCases(lCases, 9)) < DatePart("m", dToday) Then 

        Sheet5.Cells(lCases + 1, 12) = iCurrentAge 

        ElseIf DatePart("m", vCases(lCases, 9)) > DatePart("m", dToday) Then 

            Sheet5.Cells(lCases + 1, 12) = iCurrentAge - 1 

            ElseIf DatePart("d", vCases(lCases, 9)) < DatePart("d", dToday) Then 

                Sheet5.Cells(lCases + 1, 12) = iCurrentAge 

                Else 

                    Sheet5.Cells(lCases + 1, 12) = iCurrentAge - 1 

     End If 

End If 

Next lCases

If Sheet7.ListObjects(1).ListRows.Count > 0 Then 'Caseload Unions data exists(PRCS, 1170(h), Standard)

vCases = Sheet7.ListObjects("PetitionCharges").DataBodyRange 

End If

For lCases = 1 To UBound(vCases) ' Get Current Probationers Offenses

iCurrentAge = 0 

If Not IsEmpty(vCases(lCases, 28)) Then 'DOB Exists, should have built DOB function

    iCurrentAge = DatePart("yyyy", dToday) - DatePart("yyyy", vCases(lCases, 28)) 'iCurrentAge if birthday occured in current year 

     If DatePart("m", vCases(lCases, 28)) < DatePart("m", dToday) Then 

        Sheet7.Cells(lCases + 1, 31) = iCurrentAge 

        ElseIf DatePart("m", vCases(lCases, 28)) > DatePart("m", dToday) Then 

            Sheet7.Cells(lCases + 1, 31) = iCurrentAge - 1 

            ElseIf DatePart("d", vCases(lCases, 28)) < DatePart("d", dToday) Then 

                Sheet7.Cells(lCases + 1, 31) = iCurrentAge 

                Else 

                    Sheet7.Cells(lCases + 1, 31) = iCurrentAge - 1 

     End If 

End If 

Next lCases

Sheet4.Activate

Sheet4.Cells(1, 1).Select

Application.StatusBar = ""

Application.ScreenUpdating = True

Exit Sub

Error_Exit:

Application.ScreenUpdating = True

Application.StatusBar = ""

MsgBox "An Error Was encountered!" & vbCrLf & "Error: " & Err.Number & vbCrLf & Err.Description, vbOKOnly, "Get New Probationers Report Error Detected"

End Sub

Answer 3

Good Day,

Here are the VBAProject objects before crash and after recovery:

Original VBAProject:

After Recovery:

Answer 4

Try

Change==================

Sheet2.ListObjects(1).QueryTable.Refresh (False)

Sheet2.ListObjects(1).Range.AutoFilter ' Reset Autofilter if on/off

Sheet2.ListObjects(1).Range.AutoFilter ' total reset

to==========================

Dim Listobj As ListObject

Set Listobj = Sheet2.ListObjects(1)

Listobj.QueryTable.Refresh (False)

Listobj.Range.AutoFilter

Listobj.Range.AutoFilter

Answer 5

Thank You Snow Lu

I changed each querytable refresh to Listobj.Querytable.refresh and the same error occurred.

I also tried switching Sheet2 to a worksheet reference and the querytable also fails.

Another thing I tested was two open workbooks at the same time with a MSQuery dataset. After I receive the Automation error, I can still operate in the workbook until I refresh a datasource, then Excel crashes. If I refresh a MSQuery datasource in another workbook, Excel crashes both windows. MSQuery in either open workbook will operate and I can change queries and return data in MSQuery, it is only when returning data to Excel does the crash occurs.

Excel Recovery processes destroy the workbook.