Auto clear Essbase connections

O

Oli.Harwood

New Member
Joined
May 12, 2006
Messages
20
Hi,

I'm trying to get a macro which will run through a folder of files and do an Essbase retrieve on a certain tab, then save and close.

I have the code working to update each and every file, doing the retrieve ok.

However I am experiencing a problem due to the number of files that I am attempting to update. I believe Essbase only allows a user to have 250 active connections, however I'm trying to update over 300 files so hitting an error message near the end of the cycle.

I'm trying to use the EssVDisconnect function but it seems to keep returning a value of -4 which apparently means that the spreadsheet is not yet connected to the server.

Do any of you guys know how I can fix this?
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
Sort by date Sort by votes
Hello,

Can you post your code? I want to see where you connect and disconnect and how you implement the API.

Haven't used Essbase API in a few years, and do not currently have access, but I might spot something... :)
 
Upvote 0
Apologies for the long post but I'm at work so can't download the HTML maker utilities.

I haven't actually implemented the logon yet, preferring to make the user log onto Essbase before they run the macro.

The only bit that I cant get to work is for the macro to regularly disconnect so as not to run over the 250 connected sessions.

Any thing you can spot to help out is appreciated.


' Copyright 1992-1999 Hyperion Solutions Corporation. All Rights Reserved.

' RESTRICTED RIGHTS LEGEND:

' Use, duplication, or disclosure by the Government is subject to
' restrictions as set forth in subparagraph (c)(1)(ii) of the Rights
' in Technical Data and Computer Software clause at DFARS 252.227-7013,
' or in the Commercial Computer Software Restricted Rights clause at
' FAR 52.227-19, as applicable.

' Hyperion Solutions Corporation
' 1344 Crossman Avenue, Sunnyvale, CA 94089 USA
'
'
' /*********************************************************\
' * *
' * ESSXLVBA.TXT - Essbase Excel VBA Include File. *
' * *
' * For Essbase Release 6 *
' * *
' \*********************************************************/
'


' The following prototypes declare the Visual Basic menu-equivalent functions

Declare Function EssMenuVRetrieve Lib "ESSEXCLN.XLL" () As Long
Declare Function EssMenuVKeepOnly Lib "ESSEXCLN.XLL" () As Long
Declare Function EssMenuVZoomIn Lib "ESSEXCLN.XLL" () As Long
Declare Function EssMenuVZoomOut Lib "ESSEXCLN.XLL" () As Long
Declare Function EssMenuVPivot Lib "ESSEXCLN.XLL" () As Long
Declare Function EssMenuVWizard Lib "ESSEXCLN.XLL" () As Long
Declare Function EssMenuVQueryDesigner Lib "ESSEXCLN.XLL" () As Long
Declare Function EssMenuVFlashBack Lib "ESSEXCLN.XLL" () As Long
Declare Function EssMenuVOptions Lib "ESSEXCLN.XLL" () As Long
Declare Function EssMenuVMemberSelection Lib "ESSEXCLN.XLL" () As Long
Declare Function EssMenuVCurrencyReport Lib "ESSEXCLN.XLL" () As Long
Declare Function EssMenuVCascade Lib "ESSEXCLN.XLL" () As Long
Declare Function EssMenuVRetrieveLock Lib "ESSEXCLN.XLL" () As Long
Declare Function EssMenuVLock Lib "ESSEXCLN.XLL" () As Long
Declare Function EssMenuVUnlock Lib "ESSEXCLN.XLL" () As Long
Declare Function EssMenuVSend Lib "ESSEXCLN.XLL" () As Long
Declare Function EssMenuVCalculation Lib "ESSEXCLN.XLL" () As Long
Declare Function EssMenuVConnect Lib "ESSEXCLN.XLL" () As Long
Declare Function EssMenuVDisconnect Lib "ESSEXCLN.XLL" () As Long
Declare Function EssMenuVDatalessNav Lib "ESSEXCLN.XLL" () As Long
Declare Function EssMenuVLinkedObjects Lib "ESSEXCLN.XLL" () As Long
Declare Function EssMenuVRemoveOnly Lib "ESSEXCLN.XLL" () As Long

' The following prototype declares the VBA-specific functions

Declare Function EssVGetHctxFromSheet Lib "ESSEXCLN.XLL" (ByVal sheetName As Variant) As Long
Declare Function EssVGetMemberInfo Lib "ESSEXCLN.XLL" (ByVal sheetName As Variant, ByVal mbrName As Variant, ByVal action As Variant, ByVal aliases As Variant) As Variant
Declare Function EssVFreeMemberInfo Lib "ESSEXCLN.XLL" (ByRef memInfo As Variant) As Long
Declare Function EssVFreeDataPoint Lib "ESSEXCLN.XLL" (ByRef Info As Variant) As Long

' The following constants are suitable as actions
' for the EssVGetMemberInfo function

Const EssChildLevel = 1
Const EssDescendentLevel = 2
Const EssBottomLevel = 3
Const EssSiblingLevel = 4
Const EssSameLevel = 5
Const EssSameGenerationLevel = 6
Const EssCalculationLevel = 7
Const EssParentLevel = 8
Const EssDimensionLevel = 9

' The following prototypes declare the Visual Basic
' Extended Spreadsheet macro-equivalent functions

Declare Function EssVCalculate Lib "ESSEXCLN.XLL" (ByVal sheetName As Variant, ByVal calcScript As Variant, ByVal synchronous As Variant) As Long
Declare Function EssVCancelCalc Lib "ESSEXCLN.XLL" (ByVal sheetName As Variant) As Long
Declare Function EssVCascade Lib "ESSEXCLN.XLL" (ByVal sheetName As Variant, ByVal range As Variant, ByVal selection As Variant, ByVal path As Variant, ByVal prefix As Variant, ByVal suffix As Variant, ByVal level As Variant, ByVal openFile As Variant, ByVal copyFormats As Variant, ByVal overwrite As Variant, ByVal listFile As Variant) As Long
Declare Function EssVCell Lib "ESSEXCLN.XLL" (ByVal sheetName As Variant, ParamArray memberList() As Variant) As Variant
Declare Function EssVConnect Lib "ESSEXCLN.XLL" (ByVal sheetName As Variant, ByVal userName As Variant, ByVal password As Variant, ByVal server As Variant, ByVal application As Variant, ByVal database As Variant) As Long
Declare Function EssVDisconnect Lib "ESSEXCLN.XLL" (ByVal sheetName As Variant) As Long
Declare Function EssVFlashBack Lib "ESSEXCLN.XLL" (ByVal sheetName As Variant) As Long
Declare Function EssVGetCurrency Lib "ESSEXCLN.XLL" (ByVal sheetName As Variant) As Variant
Declare Function EssVGetDataPoint Lib "ESSEXCLN.XLL" (ByVal sheetName As Variant, ByVal cell As Variant, ByVal range As Variant, ByVal aliases As Variant) As Variant
Declare Function EssVGetGlobalOption Lib "ESSEXCLN.XLL" (ByVal item As Long) As Variant
Declare Function EssVGetSheetOption Lib "ESSEXCLN.XLL" (ByVal sheetName As Variant, ByVal item As Variant) As Variant
Declare Function EssVKeepOnly Lib "ESSEXCLN.XLL" (ByVal sheetName As Variant, ByVal range As Variant, ByVal selection As Variant) As Long
Declare Function EssVRemoveOnly Lib "ESSEXCLN.XLL" (ByVal sheetName As Variant, ByVal range As Variant, ByVal selection As Variant) As Long
Declare Function EssVPivot Lib "ESSEXCLN.XLL" (ByVal sheetName As Variant, ByVal range As Variant, ByVal startPoint As Variant, ByVal endPoint As Variant) As Long
Declare Function EssVRetrieve Lib "ESSEXCLN.XLL" (ByVal sheetName As Variant, ByVal range As Variant, ByVal lockFlag As Variant) As Long
Declare Function EssVSendData Lib "ESSEXCLN.XLL" (ByVal sheetName As Variant, ByVal range As Variant) As Long
Declare Function EssVSetCurrency Lib "ESSEXCLN.XLL" (ByVal sheetName As Variant, ByVal currencyIdentifier As Variant) As Long
Declare Function EssVSetGlobalOption Lib "ESSEXCLN.XLL" (ByVal item As Long, ByVal globalOption As Variant) As Long
Declare Function EssVSetSheetOption Lib "ESSEXCLN.XLL" (ByVal sheetName As Variant, ByVal item As Variant, ByVal sheetOption As Variant) As Long
Declare Function EssVUnlock Lib "ESSEXCLN.XLL" (ByVal sheetName As Variant) As Long
Declare Function EssVZoomIn Lib "ESSEXCLN.XLL" (ByVal sheetName As Variant, ByVal range As Variant, ByVal selection As Variant, ByVal level As Variant, ByVal across As Variant) As Long
Declare Function EssVZoomOut Lib "ESSEXCLN.XLL" (ByVal sheetName As Variant, ByVal range As Variant, ByVal selection As Variant) As Long
Declare Function EssVSetMenu Lib "ESSEXCLN.XLL" (ByVal setMenu As Boolean) As Long
Declare Function EssVGetStyle Lib "ESSEXCLN.XLL" (ByVal sheetName As Variant, ByVal styleType As Variant, ByVal dimName As Variant, ByVal item As Long) As Variant
Declare Function EssVSetStyle Lib "ESSEXCLN.XLL" (ByVal sheetName As Variant, ByVal styleType As Variant, ByVal dimName As Variant, ByVal item As Long, ByVal newValue As Variant) As Long
Declare Function EssVLoginSetPassword Lib "ESSEXCLN.XLL" (ByVal sheetName As Variant, ByVal newPassword As Variant, ByVal oldPassword As Variant, ByVal server As Variant, ByVal userName As Variant) As Long
Declare Function EssVSetSKUMouse Lib "ESSEXCLN.XLL" (ByVal fSKUWithMouse As Variant) As Long
Public pathname As String



Sub AVBUpdate()
'
' Update AVBs Macro
' Macro recorded 12/05/2006 by Oliver Harwood
'

' Folder to search
Dim pathname As String
pathname = BrowseFolder("Select a folder of AVBs to update") & "\"
If pathname = "\" Then
MsgBox "You didn't select a folder. This macro will now terminate."
Exit Sub
Else
MsgBox "You selected: " & pathname
End If

'
filemask = "*AVB.xls" ' Type of files to open

'If Dir(pathname, vbDirectory) = "" Then ' Test that the directory exists
' MsgBox "The folder " & pathname & " was not found."
' Exit Sub
'End If

fname = Dir(pathname & filemask, vbNormal) ' Reads the first filename in the directory
c = 0

Do While fname <> ""
' Repeats for as long as there is a filename read in by Dir()

'MsgBox "Opening " & fname
Workbooks.Open Filename:=pathname & fname, UpdateLinks:=3
c = c + 1



' Add in routine for updating AVBS

Sheets("Fin Use Only - Essbase Retrieve").Select
application.DisplayAlerts = False 'Turn off screen prompts
'Turn off "Display Unknown Members", retrieve data and then reinstate
Y = EssVSetGlobalOption(6, False)
X = EssMenuVRetrieve()
Y = EssVSetGlobalOption(6, True)
If c > 0 Then X = EssMenuVDisconnect() 'this is where i try to disconnect but it doesnt want to work
Sheets("MI Expense Report").Select



application.DisplayAlerts = True 'Turn screen prompts back on
Workbooks(fname).Close savechanges:=True



fname = Dir()
' Reads the next filename in the list, or returns "" if there are no more

Loop

'
End Sub
 
Upvote 0
Ok, I now get an error message when I run the macro saying that I dont have the licences to enable me to use the Extended Essbase macros.

Therefore I need to try and find some other way round this.

Only way I can think of at present is to use essMenuVDisconnect after the macro has run through 100 files and then this will bring up the Disconnect box and ask the user to select which ones to disconnect.

This would be a pain but may be the only way round this.

Any help greatly appreciated as always


===================================


Also I appreciate this is in the wrong section so if a mod is able to move it then please do. Sorry for posting it to the wrong place
 
Upvote 0
Hello again,

First of all, don't dump every possible API function in your module, only use the ones you want. Just confuses the issue at hand... ;)

Have a look at this, for example:

http://www.xtremevbtalk.com/showpost.php?p=961374

Heh, must be a glitch in the Matrix, I've seen this one before!! :lol:

Note, you don't have to select the Worksheet, just pass the name.

Try to not use the Menu varieties, as I did in my example. Try to explicitly connect and disconnect to a specific sheet, using the following calls:

Code: 
Declare Function EssVConnect Lib "ESSEXCLN.XLL" ( _
    ByVal sheetName As Variant, _
    ByVal userName As Variant, _
    ByVal password As Variant, _
    ByVal server As Variant, _
    ByVal application As Variant, _
    ByVal database As Variant) As Long

Declare Function EssVDisconnect Lib "ESSEXCLN.XLL" ( _
    ByVal sheetName As Variant) As Long
On the first one, connecting, try passing the Sheet's name, username and password, and try to pass Null to the last 3 arguments, for starters... ;)
 
Upvote 0
Hi,

Thanks for all your help Nate.

I've been trying to use some of the functions you suggested but all I get now is an error message saying
"Your software license does not include the use of extended spreadsheet macros. Contact your database administrator for more information."

THis happens whenever I use the EssVRetrieve or EssVDisconnect.

It appears to be that my system will only let me use the EssMenuV type function calls.

Most likely my work admins have cut some corners with the licenses and I don't see much chance of persuading them to change things.

I think I might have to give this particular bit of code up.

Easiest solution I can think of now is to split the files I want to update into 2 folders and run the macro twice so that I dont pass the 250 sessions each time the macro is run.

Unless someone else has a way round this....

Thanks for all the help
 
Upvote 0
Hmmm, odd... Always worked for me... :-?

You might be going about this the opposite way that I was, I generally started with one template and kicked out ~200 files from that template, which might only be one connection... :)
 
Upvote 0
You must log in or register to reply here.

Similar threads

xarzu
  • Question Question
How do I resolve this exception thrown while trying to do CRUD operations on CSOM?
Replies
2
Views
272
xarzu
xarzu
B
  • Question Question
Printing Excel Tabs to Adobe PDF
Replies
2
Views
304
boonwd
B
F
  • Question Question
Outlook - Searching Excel and returning offset value
Replies
2
Views
625
frateg8r
F
R
  • Question Question
Mail merge to insert the picture in the mail body and schedule the email
Replies
0
Views
1K
rafeek4u
R
T
  • Question Question
Power Query - Help!
Replies
3
Views
295
RoryA
RoryA

Forum statistics

Threads
1,224,905
Messages
6,181,662
Members
453,059
Latest member
jkevin

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top