Essbase / Smartview VBA - Connect Code?

Mr Retirement

New Member
Joined
Nov 12, 2016
Messages
42
Hello,

I'm hoping someone has some experience with VBA code with regards to Essbase/Smartview. I'm looking to connect to my cube without entering my login information manually (i.e. have username and password in the code and it'll login by that).

I've tried reading the Developer's Guide below (not getting anywhere):
https://docs.oracle.com/cd/E57185_01/SMVDG/ch05s06.html

I believe I need to use - which are from the developer guide above:
HypConnect (vtSheetName, vtUserName, vtPassword, vtFriendlyName)

+

HypCreateConnection(vtSheetName, vtUserName, vtPassword, vtProvider, vtProviderURL, vtServerName, vtApplicationName, vtDatabaseName, vtFriendlyName, vtDescription)

Thanks for any advice,
Mr R
 

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

=ODIN=

Active Member
Joined
Dec 3, 2009
Messages
288
Below is some code I successfully used to connect to essbase cubes in the past.

Make sure to have all the declaration pasted in at the top of your code module before any subs() -- I have more than declarations than needed, but in case you want to use the other functions...

Also, it requires you to have already setup a Private Connection to the cube you want to access through your smart view browser.

Then just change the spots i marked as enter... in this line of code below: coNN = HypConnect(Empty, enterUserNameString, enterPassWordString, enterPrivateSmartviewConnectionNameString)

Code:
Declare PtrSafe Function HypConnect Lib "HsAddin.dll" (ByVal sheetName As Variant, ByVal vtUser As Variant, ByVal vtPassword As Variant, ByVal vtFriendlyName As Variant) As Long
Declare PtrSafe Function HypDisconnect Lib "HsAddin.dll" (ByVal sheetName As Variant, ByVal bLogout As Boolean) As Long
Declare PtrSafe Function HypSetAliasTable Lib "HsAddin.dll" (ByVal vtSheetName As Variant, ByVal vtAliasTableName As Variant) As Long
Declare PtrSafe Function HypExecuteQuery Lib "HsAddin.dll" (ByVal vtSheetName As Variant, ByVal vtMDXQuery As Variant) As Long
Declare PtrSafe Function HypDeleteMetaData Lib "HsAddin" (ByVal vtDispObject As Variant, ByVal vtbWorkbook As Variant, ByVal vtbClearMetadataOnAllSheetsWithinWorkbook As Variant) As Long
Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Declare PtrSafe Function HypSetGlobalOption Lib "HsAddin.dll" (ByVal item As Long, ByVal globalOption As Variant) As Long
Declare PtrSafe Function HypDisconnectAll Lib "HsAddin" () As Long




Sub essCon()
    


             
             coNN = HypConnect(Empty, enterUserNameString, enterPassWordString, enterPrivateSmartviewConnectionNameString)
             Debug.Print coNN


end sub
 
Last edited:

Mr Retirement

New Member
Joined
Nov 12, 2016
Messages
42
Hi Odin,

Thank you for your response. Is it possible for me to somehow lookup what the technical Connection Name is for the cube that i'm connecting to? I believe that is the only information point that i'm missing.

Thanks,
Mr R
 

=ODIN=

Active Member
Joined
Dec 3, 2009
Messages
288
Sorry, no idea. I only ever managed to connect through vba if I had already manually setup that private connection first.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,501
Messages
5,523,290
Members
409,508
Latest member
Afc

This Week's Hot Topics

Top