valquiriaa
12-05-2022, 07:55 PM
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
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