Home VBA Error Handling Not working
Reply: 0

VBA Error Handling Not working

user1625
1#
user1625 Published in July 18, 2018, 4:47 am

I am writing a vba program to refresh power queries for a number of reports based on a user saying which reports to refresh via a form with checkboxes.

This part of the code refreshes the power query and I want it to capture the error if the refresh isn't successful. It will update a control table "Not Updated" if it wasn't updated and then I want it to resume next.

For some reason the "On Error Goto Error" isn't triggering the error line. It is still throwing the error and stopping the code run.

Any help would be greatly appreciated!

For Each cell In 
wsConfig.ListObjects("tblReportstoRun").ListColumns(2).DataBodyRange
    If cell.Value = True Then
        cell.Offset(, 1).Value = Now()
        cell.Offset(, 2).Value = frmSetting.tbStartDate
        cell.Offset(, 3).Value = frmSetting.tbEnddate

        strCurrWS = cell.Offset(0, -1)
        ThisWorkbook.Sheets(strCurrWS).Activate
        Application.StatusBar = "Updating tab " & strCurrWS

            For Each qt In ThisWorkbook.Sheets(strCurrWS).QueryTables
                qt.Refresh BackgroundQuery:=False
            Next qt

            For Each lo In ThisWorkbook.Sheets(strCurrWS).ListObjects
                On Error GoTo Error
                lo.QueryTable.Refresh BackgroundQuery:=False
            Next lo
    Else
 Error:
    cell.Offset(, 4).Value = "Not Updated"
    If InStr(Err.Description, "Permission Error") Then
    cell.Offset(, 6).Value = "Permission Error. Check Credentials"
    Err.Clear
    End If


    End If
Next cell

Set qt = Nothing
Set wks = Nothing
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.321578 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO