none
vba refresh.all function does not throw error if the data source connection has problem RRS feed

  • Question

  • environment :
    office 2016 64bit

    1. In Power Pivot add a excel data source (e.g. actual excel file is c:\test.xls)

    2. build a pivot table with the excel data  source just add in Power Pivot

    3. write a macro in vba e.g. call refresh()

    Sub Refresh()

    ActiveWorkbook.RefreshAll

    End Sub

    4. add a Refresh button in excel sheet and assign marco "Refresh" to this button

    5. move the excel file c:\test.xls  to c:\testback.xls

    6.  If click "Refresh all" button in under Data Tab , error throw to indicate the datasource has connection problem

    7.  But if click Refresh button to refresh , no error is indicate , but actually the data cannot refreshed.

    Expectation:  refreshAll() in vba should throw error in this case , any one has idea how to solve it ?  or any workaround ?

    Monday, January 9, 2017 7:15 AM

All replies

  • This question was never answered but still persists. Refresh All from the ribbon produces appropriate error message, but ActiveWorkbook.RefreshAll in a macro does not produce an error, i.e. the macro appears to have completed successfully although no data was updated.  PLEASE HELP!
    Monday, March 25, 2019 7:46 PM