SAPExecuteCommand Refresh – ambiguous return values – Part 2

The SAPExecuteCommand’s Refresh command has some ambiguous return values, as I’ve previously documented.

I’ve since discovered a further, related ambiguity regarding the QueryLastRefreshedAt property of the SAPGetSourceInfo.Analysis function.

As a reminder:

The following code will return 1 (Success), regardless of whether the DataSource (DS_1) is already refreshed:

Application.Run(“SAPExecuteCommand”, “Refresh”, “DS_1”)

The following code will return the date/time that a DataSource was last refreshed. If the Datasource has not already been refreshed, it returns Error 2042.

Application.Run(“SAPGetSourceInfo”, “DS_1”, “QueryLastRefreshedAt”)

So, what happens to the date/time after each subsequent Refresh? It remains the same as the initial refresh date/time….

Dim vLastRefreshed as Variant

Dim vResult as Variant

‘DS_1 hasn’t been refreshed, vTest will be assigned Error 2042

vLastRefreshed = Application.Run(“SAPGetSourceInfo”, “DS_1”, “QueryLastRefreshedAt”)

‘First Refresh of DS_1, vResult will be assigned 1

vResult = Application.Run(“SAPExecuteCommand”, “Refresh”, “DS_1”)

vLastRefreshed will be assigned the ACTUAL Refresh Date/Time

vLastRefreshed = Application.Run(“SAPGetSourceInfo”, “DS_1”, “QueryLastRefreshedAt”)

‘Time elapses…..

‘Second Refresh of DS_1, vResult will AGAIN, be assigned 1, suggesting that a Refresh actually occurred

vResult = Application.Run(“SAPExecuteCommand”, “Refresh”, “DS_1”)

‘vLastRefreshed will be assigned the ORIGINAL Refresh Date/Time, suggesting the most recent refresh didn’t actually occur

vLastRefreshed = Application.Run(“SAPGetSourceInfo”, “DS_1”, “QueryLastRefreshedAt”)

I’ve experienced this behaviour under SBO 1.4 and Excel 2007, please add your experiences with other versions in the comments…

I’m not sure that this is the desired/expected behaviour of the function, but it’s something to watch out for in your code…..