Hi there, I am executing a loop to open excel files to refresh the data, save and close automatically. The first file from the loop gets modified and closes down, however I get stuck in the second file from the loop because I get the exception HRESULT:0x800401A8. Any idea how to get rid of that?
Many thanks,
Val
+7
Hi
I have a feeling that the bot closes the Excel file too soon before disposing the Excel Object, so could you try using a Save Action and a 3-5 second delay after it before closing it?
Kind Regards,
Ashwin A.K
+1
Hi
'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("14:46: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.
Call Application.Run("SAPExecuteCommand", "Refresh")
Application.Wait (Now + TimeValue("00:00:10"))
'Refresh all pivottables and graphs.
ThisWorkbook.RefreshAll
Application.Wait (Now + TimeValue("00:00:10"))
'Save and then close the workbook
ThisWorkbook.Save
Application.Wait (Now + TimeValue("00:00:10"))
ThisWorkbook.Close savechanges:=False
Application.Wait (Now + TimeValue("00:00:10"))
End If
End Sub
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.