Dear All,
We have several SmartView Reports and I need to refresh that report for each environment using VBA.
The problem is that when I see the "Sheet Info", it shows the production environment details, whereas I am connection to Development or SIT environment.
I am using a form to accept the User Name, Password and the environment to be connected to. I am able to establish the connection and can also view the connection in "Active Connection".
It is basically the Named Range which needs to be renamed first and by selected the renamed Name Ranges, I need to refresh the them.
Somehow every time the "Sheet Info" shows the Production environment details.
At the beginning of my Connection script, I'm checking for any existing connections, and if they exist, then Disconnect them. Here's the piece of code to validate it:
For Each ws In ActiveWorkbook.Sheets
Dim vtRet As Variant
vtRet = HypIsConnectedToSharedConnections()
If vtRet = True Then
x = HypDisconnect(ws.Name, True)
sts = HypDisconnectAll()
End If
Next
Once validated, I am connecting to Essbase using the following piece of code:
For Each ws In ActiveWorkbook.Sheets
x = HypCreateConnection(Empty, _
UserName, _
UserPwd, _
HYP_ESSBASE, _
URL, _
ConnServer, _
AppName, _
DBName, _
ConnName, _
Description)
x = HypConnectionExists(ConnName)
If x = True Then
' Actual connection between SmartView and Essbase
y = HypConnect(ws.Name, UserName, UserPwd, ConnName)
MsgBox "Essbase Connection established successfully for Sheet Name: " & ws.Name, , "Home Box Office"
End If
Next
And finally I am renaming and then later refreshing the Named Ranges using the following code:
For Each nrName In ws.Names
TempName = "'" & ws.Name & "'!" & "B_HE_PLAN_HE_PLAN_" & nrCount
If nrName.Name = TempName Then
sts = HypRetrieveNameRange(ws.Name, TempName)
MsgBox sts
nrCount = nrCount + 1
End If
Next nrName
nrCount = 1
Next
Somehow this refresh is not happening and the "Sheet Info" still contains information regarding Production environment, whereas the information should reflect for Development or SIT. Though in "Active Connections" I am able to see the the Friendly Name for connection I created for Development, but the check (tick mark) is not there for Development connection.
Please advise what am I doing wrong that the "Sheet Info" is not getting updated with Development information.
Please help!!!
Best Regards,
Amit