lresult yield to Type mismatch

[Former Member]

Hi All,

I have used the callback_AfterRedisplay() function which is already called when I am opening the workbook from BW server.

(Actually this should not be, as no refresh is called)

Anyway

I  am using the following line in my coding:

lresult = Application.run("SapGetCellInfo", "Crosstab8", "DATASOURCE")

I tried to read the return code ,wich always ends in a dump with  lResult is "Error 2013" which means type mismatch.

Of course by opening the workbook from the BW server will not provide any selected cell and thus no DS is provided,

but how can I catch this exception, means how to read lResult.

If lResult <> false then --> dumps

if isErr(lResult)   --> dumps

Any idea.

PS. If I am refreshing the query in crosstab8, lResult is DS_8, thats ok.

[Former Member]

Hi Werner, Ricardo,

actually the coding is working and returns the "DS_7", but only if you are in one crosstab.

Still, as callback is getting called not only if you refresh I have added now the following coding to bypass the type mismatch.

Its not nice but at least its providing me the correct behavior. If have used now crosstab first to run the case statement

Public Sub Callback_AfterRedisplay()

.... some dim etc.. here.

lResult = Application.Run("SAPGetCellInfo", ActiveCell, "CROSSTAB")

sourceCrossTab = lResult

'Very important to have this line below as callback starts for several actions in Excel

'and if a cell is not selected the If statement would fail with type mismatch

'you also have to add the goto points.

On Error GoTo ignoreCallback

Select Case sourceCrossTab

     Case "Crosstab7"

     Set resultArea = Range("SAP" & sourceCrossTab)

  ...... some body here.

GoTo goodEnd

ignoreCallback:

     'MsgBox "Error.."

goodEnd:

' in case you want to place a message raise it here.

End Sub

Ralf

[former_member245941]

Hello,

your input parameters for the function call are wrong. SapGetCellInfo expects a Cell or Range of Cells as first input parameter. In your example the input parameter is  a plain string. Please refer to the "working with macros" chapter of the Analysis for Office help. (Press F1 after switching to the Analysis for Office Addin Ribbon in Excel)

Following your example:

lresult = Application.run("SapGetCellInfo", Names("SapCrosstab8").RefersToRange, "DATASOURCE")

should give you the result you seem to be looking for.

If you are working with this, take care not to forget to add "Sap" in front of the Crosstabs Formula Alias. Analysis creates a named element in your workbook for every Crosstab that way, which refers to the range used by that crosstab.

Please be aware that doing this with a Crosstab Formula Alias / Excel Named Range, that does not exist in your workbook, will result in a dump too. To prevent that you would have to implement some kind of error handling.

Kind regards,

[Former Member]

Hello Ralf-Peter,

the API method SAPGetCellInfo expects a single cell to be used for the Cell parameter. I have also observed that the use of a Range here always results in an error.

I would therefore recommend you to adapt your code to use a single cell here:

Dim oCell as Excel.Range
Dim vntResult as Variant


Set oCell = Range("Crosstab8").Cells(1,1)
vntResult = Application.run("SapGetCellInfo", oCell, "DATASOURCE")

Hope this helps!

Best regards,

Ricardo Vieira