How to retrieve workbook's "Last Refreshed" value using VBA?

[Former Member]

Does anyone know how to retrieve a BEx workbook's "Last Refreshed" value using VBA?

What I've done is expand upon a colleague's existing Excel VBA solution to automatically log into SAP BEx and batch process (and also schedule) the running of multiple BEx reports. As each BEx report in the queue is processed, the results of the run are written to a "Results" worksheet -- indicating whether that BEx report was processed successfully or not. I'm pretty much done, and everything works like a charm.

Except I have one little problem remaining: during the processing of each BEx report, the SAP BEx status dialog appears, giving the user the ability to cancel the processing of that particular report, if they so desire. If the user cancels, I want my "Results" worksheet to indicate that for that report.

At first, I thought, okay, I'll just test the return value when calling the SAPBEX.XLA's SAPBEXrefresh function. That function's return value is supposed to return the number of errors that occurred after each time SAPBEXrefresh is fired -- normally it's 0 if everything runs okay. So surely, if the user cancels, there's got to be some sort of error and the return value of SAPBEXrefresh would be > 0, right? Nope, no such luck!

Which brings me back to my question in this post -- I found out through my company's SAP consultant that, if the user hits cancel in the SAP BEx dialog, the "Last Refreshed" value will not change. Therefore, he told me, simply test the value of the "Last Refreshed" value before and after each BEx reports' run. If the "Last Refreshed" value doesn't change, then presto, you know the user canceled.

This is where I'm stuck. How do you programmatically get the "Last Refreshed" value? Obviously, you could write VBA code to find the first cell in the BEx report with the text "Last Refreshed" and then get the value in the adjoining cell. The problem with that is, what if, for some stupid reason, there's another cell somewhere in the BEx report with the text, "Last Refreshed". There's no way I can be sure that I've really found the "Last Refreshed" value plugged in by BEx.

I've been looking extensively in this forum for an answer, but haven't found any. It seems like there are a lot of SAP BEx experts here, and if anyone can help me out here, I would greatly appreciate it.

Thank you.

[Former Member]

Well, it looks like I can't assign the points to myself

[Former Member]

Well, it was a little circuitous, but I figured out the solution to my own question.

I recalled I had read about the sapbexDebugPrint macro in sapbex.xla in one of Peter Knoer's posts in this forum. So I thought, maybe I can use that to get the before and after refresh values of "Last Refreshed" in the workbook. Well, I was half-right: I could only use sapbexDebugPrint to get the workbook's after-refresh values of "Last Refreshed".

But it didn't matter!

As long as the after-refresh value of the workbook's "Last Refreshed" value was later than the after-refresh value of the previous workbook in the processing queue, I knew the refresh was successful and the user didn't cancel. There were some other logic permutations I had to factor in, but basically that was the answer.

Here are snippets of my code from the main procedure, for anyone's who interested:

'..........

' **** Refresh query ************************************

' Get the previous "Last Refreshed" value

' We're going to need to compare this to the "Last Refreshed" value

' after running SAPBEXrefresh function to trap the possibility of

' the user canceling via the SAPBEx status dialog box

PrevLastRefr = GetLastRefreshed()

' Reactivate the source workbook, just in case

SourceWorkbook.Activate

RefreshRetVal% = Application.Run("SAPBEX.XLA!SAPBEXrefresh", True, , False)

If RefreshRetVal% <> 0 Then

blnProcessingErr = True

End If

' Get the current "Last Refreshed" value and compare it to the previous value

CurrLastRefr = GetLastRefreshed()

If CurrLastRefr = "NOT FOUND" Then

' Refresh canceled

blnProcessingCanceled = True

Else ' We found a valid current "Last Refreshed" value

If PrevLastRefr = "NOT FOUND" Then

' Refresh okay

blnProcessingCanceled = False

Else

If CDate(CurrLastRefr) > CDate(PrevLastRefr) Then

' Current "Last Refreshed" value is later than previous value,

' so refresh okay

blnProcessingCanceled = False

Else

' Refresh canceled

blnProcessingCanceled = True

End If

End If

End If

' Reactivate the source workbook, just in case

SourceWorkbook.Activate

'..........

And here's my function which retrieves the "Last Refreshed" value by calling sapbexDebugPrint macro in sapbex.xla:

Function GetLastRefreshed() As Variant

' Get the SAP BEx "Last Refreshed" value by calling

' SAPBEx.xla's sapbexDebugPrint procedure and creating

' the special diagnostic workbook.

On Error GoTo GetLastRefreshed_Error

Dim TextCell As Range

Dim TextCellAddr$

Dim TextCellRow%, TextCellCol%

Dim LastRefreshedVal As Variant

Dim NumWorkbooks%

' Initialize

GetLastRefreshed = "NOT FOUND"

LastRefreshedVal = "NOT FOUND"

' Turn off screen updating until the end

Application.ScreenUpdating = False

' Get the number of currently open workbooks

NumWorkbooks% = Workbooks.Count

' Call the SAPBEx.xla's sapbexDebugPrint procedure

' This'll create a diagnostic workbook with all the information

' about the BEx query that was previously refreshed

Application.Run "SAPBEX.XLA!sapbexDebugPrint"

' Let's double-check that the diagnostic workbook actually

' got created

' If there's any error at this point or if the number of workbooks

' isn't more than it was a moment ago, raise custom error

If (Err.Number <> 0) Or (Not (Workbooks.Count > NumWorkbooks%)) Then

Err.Raise vbObjectError + 513, , "sapbexDebugPrint failed to create the diagnostic workbook"

End If

' We'll need to look at a worksheet named "E_T_TXT_SYMBOLS"

' in the diagnostic workbook

' If this worksheet doesn't exist, then we know that there

' was no previously refreshed query during this session

' (We could loop through the collection of worksheets in the workbook

' to see if that worksheet actually exists, but we'll use

' error handling to deal with this instead)

' Find the first cell in the "E_T_TXT_SYMBOLS" worksheet

' with the text "Last Refreshed"

' (If the worksheet doesn't exist, an error will be thrown...)

Set TextCell = Sheets("E_T_TXT_SYMBOLS").Cells.Find(What:="Last Refreshed", _

LookIn:=xlValues)

If TextCell Is Nothing Then

' Can't find the cell, so we know the user had canceled during previous refresh

LastRefreshedVal = "NOT FOUND"

Else

' Found the cell, now we're in business

TextCellAddr$ = TextCell.Address ' $F$11

TextCellRow% = CInt(Mid(TextCellAddr$, InStr(2, TextCellAddr$, "$") + 1))

TextCellCol% = ColRef2ColNo(Mid(TextCellAddr$, 2, InStr(2, TextCellAddr$, "$") - 2))

' The cell with the "Last Refreshed" value is going to be 2 columns to the right

LastRefreshedVal = Sheets("E_T_TXT_SYMBOLS").Cells(TextCellRow%, TextCellCol%).Offset(0, 2).Value

' Ensure the "Last Refreshed" value is a valid date/time

If Not IsDate(LastRefreshedVal) Then LastRefreshedVal = "NOT FOUND"

End If

GetLastRefreshed_Exit:

' Err.Number -2147220991 is my custom raised error:

' "sapbexDebugPrint failed to create the diagnostic workbook"

If Err.Number <> -2147220991 Then

' Close the diagnostic workbook and return Last Refreshed value

Workbooks(ActiveWorkbook.Name).Close SaveChanges:=False

GetLastRefreshed = LastRefreshedVal

End If

Application.ScreenUpdating = True ' Turn on screen updating

Exit Function

GetLastRefreshed_Error:

Select Case Err.Number

Case 9 ' Subscript out of range (which means "E_T_TXT_SYMBOLS" worksheet doesn't exist)

LastRefreshedVal = "NOT FOUND"

Case Else

MsgBox "Error encountered during getting Last Refreshed value." & vbCrLf & vbCrLf & _

"Error: " & Err.Number & " - " & Err.Description, vbExclamation, gstrErrBoxTitle

End Select

Resume GetLastRefreshed_Exit

End Function

Like I said, the solution was a little circuitous, but it works!