Essbase Options Excel VBA woes...

[teethofgold]

So... I use the following code...

ActiveWorkbook.ActiveSheet.Names("EssOptions").RefersTo = "A6100000000021000000001100020_01000"

... to set the options on Essbase retrieve pages... thus preventing the dreaded effect of retrieving and eliminating missing and zero rows... thus f&%king up your retrieve...

The problem I'm having... is that on some worksheets... the options just plain don't stick!

I use this exact code in other workbooks and it always works great... but in one particular workbook it doesn't work at all...

Anyone have any insight into this?

 

[teethofgold]

Here's a better code sample...

Private Sub retrieve_range(ByVal range_indx)
'the retrieve ranges are stored in records like this

'Type essbase_range_record
' sheet_name As String
' essbase_range As String
' password As String
'End Type
'the essbase options are stored in this public const
'Const SAFE_RETRIEVE_OPTIONS = "A1100000000011000000001100020_01000"

Dim a As Long

On Error GoTo invalid

With download_ranges(range_indx)
Sheets(.sheet_name).Activate
Range(.essbase_range).Select
ActiveSheet.Unprotect .password

ActiveWorkbook.ActiveSheet.Names("EssOptions").RefersTo = SAFE_RETRIEVE_OPTIONS

a = EssMenuVRetrieve

If .password <> "" Then
ActiveSheet.Protect .password
End If
End With
Exit Sub

invalid:
With download_ranges(range_indx)
MsgBox "There was a problem retrieving range " & .essbase_range & " from sheet " & .sheet_name, vbOKOnly, "Bad news..."
End With
End Sub