Msg error: "Exception from HRESULT:0x800401A8" when refreshing reports automatically

[valquiriaa]

Hi there,

I created a task to open an excel file, refresh the data, save and close down the report, but once the report closes down, I get this error message "Exception from HRESULT:0x800401A8" and then I get stuck with the second file from the loop.

Any help would be much appreciated. Below is the code and attached is the error screenshot:

'This is like an "on workbook open" event but it makes sure SAP Analysis has fully loaded first"

Public Sub Workbook_SAP_Initialize()
'This function checks if when the workbook is it being opened by the right person and between the times specified. This way if it's outside of the times given you can open the workbook and not have it refresh everytime (which can lock you out since there is a "Close" event in the code).
'Change the time and name to suit - it needs to be the name of the logged in users whose PC it is running from.
If VBA.Time > VBA.TimeValue("05:15:00") And VBA.Time < VBA.TimeValue("05:30:00") And Application.UserName = "Val Kawaguchi" _
Then
'The SAP function to refresh every data source in the book. Should go through fine as long as the data sources have the correct settings applied when saved. I used to need to give each data source a username and password but since we now have auto login to SAP that step doesn't seem to be needed.
Call Application.Run("SAPExecuteCommand", "Refresh")
'Refresh all pivottables and graphs.
ThisWorkbook.RefreshAll
'Save and then close the workbook
ThisWorkbook.Save
ThisWorkbook.Close savechanges:=False
End If
End Sub

Many thanks,
Val

[Grade4.2]

It looks like the error is occurring when the workbook is trying to close. This could be due to the fact that the workbook is still refreshing the data sources or pivot tables when the code attempts to close it. To fix this issue, try adding a delay before closing the workbook to give it time to fully refresh the data. You can do this by using the "Application.Wait" function, which will pause the code for a specified amount of time before continuing.


For example, you can add the following line before closing the workbook:



Application.Wait (Now + TimeValue("00:00:05")) 'Wait 5 seconds before closing


This will give the workbook 5 seconds to finish refreshing before closing, which should prevent the error from occurring. You can adjust the amount of time to wait as needed.

[valquiriaa]

thanks! it worked :)