Sending a40 workbook to non a40 user-loses prompt values ( SAPGetvariable)

[former_member182373]

Guys,

Sending the A40 Workbook output to none A40 user.

user opens workbook and can see the data results ok, but the prompts-

defined using the SAPGetvariable function, display as #name.

Any idea?

thanks

Tony

[Former Member]

I researched a bit. Another reason that this happens, seems to be the automatic update of the workbook. Especially due to different Excel versions.

If you set the Calculation Option to "Manual" and open the workbook afterwards or if you save the workbook when this option is set, Excel doesn't try to update the content of the formula and you can work with it.

But it is an Application setting on Client side and again - just a workaround...

[former_member182373]

Thanks Nina for your suggestion -its a bit frustrating !

Tony

[Former Member]

Hello Tony,

i have a similar problem. I use SAPGetData and get a whole Table full of #NAME?.

I have not found a solution for myself, so I'm looking forward for any replies from the experts.. but I might have a solution for you.

If it's just a few fields, which use the SAPGetVariable (in my case SAPGetData), you could replace the formula with the content of the field.

I tried to use the Workbook_BeforeSave() Event and copied the content of the fields into the field (which overwrites the SAPGetData-Formula)

val = Mid(lv_cell.FormulaR1C1, 2)
If InStr(1, val, "SAPGetData", vbTextCompare) <> 0 Then
 lv_cell = lv_cell.value
End If

I loop over a whole range and check if I need to replace the content. If it's only a few certain fields in your case, you simply need the cell = cell.value command.

Unfortunately for several sheets full of SAPGetData-Formulas the runtime is to long, but it might be a useful workaround for you.

Good luck!

NiNa