Essbase retrieve macro

[seanrmckenna]

Hi all,

The below code works fine when retrieving from the one database but does not when trying to switch databases (where I put "server = Sheets..." for the second time). I assume this is because I have used Declare Function at the top and can't define the server, application and db twice in the lower code. Is that the case? Do I need to use something more flexible, e.g. Dim =...? I haven't used "Dim =" much as I've been learning as I go the last few years and the below is how the macros have always been set up.

Also the EssVDisconnects seem to not be working. Any advice on those? Sorry I know there is another thread on this but I wanted to see if this code is salvageable and also get opinions on whether it is robust or not.

Thanks
Sean

Declare Function EssMenuVConnect Lib "ESSEXCLN.XLL" () As Long
Declare Function EssMenuVRetrieve Lib "ESSEXCLN.XLL" () As Long
Declare Function EssMenuVDisconnect Lib "ESSEXCLN.XLL" () As Long
Declare Function EssMenuVZoomIn Lib "ESSEXCLN.XLL" () As Long
Declare Function EssVDisconnect Lib "ESSEXCLN.XLL" (ByVal sheetName As Variant) As Long
Declare Function EssMenuVZoomOut Lib "ESSEXCLN.XLL" () As Long
Declare Function EssVConnect Lib "ESSEXCLN.XLL" (ByVal sheetName As Variant, ByVal username As Variant, ByVal password As Variant, ByVal server As Variant, ByVal app As Variant, ByVal database 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


Sub RetrieveAll()


Application.ScreenUpdating = False


' Use Clear Contents macro first to easily identify any errors, which will be blank


'Disconnect from all sheets so that the correct connection is used:


X = EssVDisconnect("PL Main")
X = EssVDisconnect("Net Rev")
X = EssVDisconnect("PAP")
X = EssVDisconnect("COP")
X = EssVDisconnect("Fixed Expenses")
X = EssVDisconnect("OI")
X = EssVDisconnect("OI by format")
X = EssVDisconnect("OI INT by month")
X = EssVDisconnect("INT by title")
X = EssVDisconnect("UK by title")
X = EssVDisconnect("France by title")
X = EssVDisconnect("Germany by title")
X = EssVDisconnect("Italy by title")
X = EssVDisconnect("Spain by title")
X = EssVDisconnect("Nordic by title")
X = EssVDisconnect("Benelux by title")
X = EssVDisconnect("Switzerland by title")
X = EssVDisconnect("EMEA Lic by title")
X = EssVDisconnect("EMEA Reg by title")
X = EssVDisconnect("EMEA HO by title")
X = EssVDisconnect("Australia by title")
X = EssVDisconnect("NZ by title")
X = EssVDisconnect("Japan by title")
X = EssVDisconnect("China by title")
X = EssVDisconnect("Korea by title")
X = EssVDisconnect("Hong Kong by title")
X = EssVDisconnect("APAC Lic by title")
X = EssVDisconnect("APAC Reg by title")
X = EssVDisconnect("APAC HO by title")
X = EssVDisconnect("Int HO by title")




Sheets("Control Sheet").Select



username = Sheets("Control Sheet").Range("UserDetailsName").Text
password = Sheets("Control Sheet").Range("UserDetailsPassword").Text
server = Sheets("Control Sheet").Range("TopScenarioServer").Text
app = Sheets("Control Sheet").Range("TopScenarioApplication").Text
database = Sheets("Control Sheet").Range("TopScenarioDatabase").Text




If Sheets("Control Sheet").Range("RetSelectPLMainTop") = 1 Then
Sheets("PL Main").Select
X = EssVConnect(Null, username, password, server, app, database)
X = EssVSetGlobalOption(5, 4)
X = EssVSetSheetOption(Null, 6, False)
X = EssVSetSheetOption(Null, 7, False)
X = EssVSetSheetOption(Null, 11, True)
Range("PL_Main_RetTop").Select
X = EssMenuVRetrieve()
End If


If Sheets("Control Sheet").Range("RetSelectNetRevTop") = 1 Then
Sheets("Net Rev").Select
X = EssVConnect(Null, username, password, server, app, database)
X = EssVSetGlobalOption(5, 4)
X = EssVSetSheetOption(Null, 6, False)
X = EssVSetSheetOption(Null, 7, False)
X = EssVSetSheetOption(Null, 11, True)
Range("NetRev_RetTop").Select
X = EssMenuVRetrieve()
End If


If Sheets("Control Sheet").Range("RetSelectPAPTop") = 1 Then
Sheets("PAP").Select
X = EssVConnect(Null, username, password, server, app, database)
X = EssVSetGlobalOption(5, 4)
X = EssVSetSheetOption(Null, 6, False)
X = EssVSetSheetOption(Null, 7, False)
X = EssVSetSheetOption(Null, 11, True)
Range("PAP_RetTop").Select
X = EssMenuVRetrieve()
End If


If Sheets("Control Sheet").Range("RetSelectCOPTop") = 1 Then
Sheets("COP").Select
X = EssVConnect(Null, username, password, server, app, database)
X = EssVSetGlobalOption(5, 4)
X = EssVSetSheetOption(Null, 6, False)
X = EssVSetSheetOption(Null, 7, False)
X = EssVSetSheetOption(Null, 11, True)
Range("COP_RetTop").Select
X = EssMenuVRetrieve()
End If


If Sheets("Control Sheet").Range("RetSelectFixExpTop") = 1 Then
Sheets("Fixed Expenses").Select
X = EssVConnect(Null, username, password, server, app, database)
X = EssVSetGlobalOption(5, 4)
X = EssVSetSheetOption(Null, 6, False)
X = EssVSetSheetOption(Null, 7, False)
X = EssVSetSheetOption(Null, 11, True)
Range("FixExp_RetTop").Select
X = EssMenuVRetrieve()
End If


If Sheets("Control Sheet").Range("RetSelectOITop") = 1 Then
Sheets("OI").Select
X = EssVConnect(Null, username, password, server, app, database)
X = EssVSetGlobalOption(5, 4)
X = EssVSetSheetOption(Null, 6, False)
X = EssVSetSheetOption(Null, 7, False)
X = EssVSetSheetOption(Null, 11, True)
Range("OI_Main_RetTop").Select
X = EssMenuVRetrieve()
End If


If Sheets("Control Sheet").Range("RetSelectOIFormatTop") = 1 Then
Sheets("OI by format").Select
X = EssVConnect(Null, username, password, server, app, database)
X = EssVSetGlobalOption(5, 4)
X = EssVSetSheetOption(Null, 6, False)
X = EssVSetSheetOption(Null, 7, False)
X = EssVSetSheetOption(Null, 11, True)
Range("OI_Format_RetTop").Select
X = EssMenuVRetrieve()
End If


If Sheets("Control Sheet").Range("RetSelectOIMonthTop") = 1 Then
Sheets("OI INT by month").Select
X = EssVConnect(Null, username, password, server, app, database)
X = EssVSetGlobalOption(5, 4)
X = EssVSetSheetOption(Null, 6, False)
X = EssVSetSheetOption(Null, 7, False)
X = EssVSetSheetOption(Null, 11, True)
Range("OI_Month_RetTop").Select
X = EssMenuVRetrieve()
End If


If Sheets("Control Sheet").Range("RetSelectINTTop") = 1 Then
Sheets("INT by title").Select
X = EssVConnect(Null, username, password, server, app, database)
X = EssVSetGlobalOption(5, 4)
X = EssVSetSheetOption(Null, 6, False)
X = EssVSetSheetOption(Null, 7, False)
X = EssVSetSheetOption(Null, 11, True)
Range("PL_INT_RetTop").Select
X = EssMenuVRetrieve()
End If












X = EssVDisconnect("Control Sheet")
X = EssVDisconnect("PL Main")
X = EssVDisconnect("Net Rev")
X = EssVDisconnect("PAP")
X = EssVDisconnect("COP")
X = EssVDisconnect("Fixed Expenses")
X = EssVDisconnect("OI")
X = EssVDisconnect("OI by format")
X = EssVDisconnect("OI INT by month")
X = EssVDisconnect("INT by title")






Sheets("Control Sheet").Select

username = Sheets("Control Sheet").Range("UserDetailsName").Text
password = Sheets("Control Sheet").Range("UserDetailsPassword").Text
server = Sheets("Control Sheet").Range("MidScenarioServer").Text
app = Sheets("Control Sheet").Range("MidScenarioApplication").Text
database = Sheets("Control Sheet").Range("MidScenarioDatabase").Text




If Sheets("Control Sheet").Range("RetSelectPLMainMid") = 1 Then
Sheets("PL Main").Select
X = EssVConnect(Null, username, password, server, app, database)
X = EssVSetGlobalOption(5, 4)
X = EssVSetSheetOption(Null, 6, False)
X = EssVSetSheetOption(Null, 7, False)
X = EssVSetSheetOption(Null, 11, True)
Range("PL_Main_RetMid").Select
X = EssMenuVRetrieve()
End If


If Sheets("Control Sheet").Range("RetSelectNetRevMid") = 1 Then
Sheets("Net Rev").Select
X = EssVConnect(Null, username, password, server, app, database)
X = EssVSetGlobalOption(5, 4)
X = EssVSetSheetOption(Null, 6, False)
X = EssVSetSheetOption(Null, 7, False)
X = EssVSetSheetOption(Null, 11, True)
Range("NetRev_RetMid").Select
X = EssMenuVRetrieve()
End If


If Sheets("Control Sheet").Range("RetSelectPAPMid") = 1 Then
Sheets("PAP").Select
X = EssVConnect(Null, username, password, server, app, database)
X = EssVSetGlobalOption(5, 4)
X = EssVSetSheetOption(Null, 6, False)
X = EssVSetSheetOption(Null, 7, False)
X = EssVSetSheetOption(Null, 11, True)
Range("PAP_RetMid").Select
X = EssMenuVRetrieve()
End If


If Sheets("Control Sheet").Range("RetSelectCOPMid") = 1 Then
Sheets("COP").Select
X = EssVConnect(Null, username, password, server, app, database)
X = EssVSetGlobalOption(5, 4)
X = EssVSetSheetOption(Null, 6, False)
X = EssVSetSheetOption(Null, 7, False)
X = EssVSetSheetOption(Null, 11, True)
Range("COP_RetMid").Select
X = EssMenuVRetrieve()
End If


If Sheets("Control Sheet").Range("RetSelectFixExpMid") = 1 Then
Sheets("Fixed Expenses").Select
X = EssVConnect(Null, username, password, server, app, database)
X = EssVSetGlobalOption(5, 4)
X = EssVSetSheetOption(Null, 6, False)
X = EssVSetSheetOption(Null, 7, False)
X = EssVSetSheetOption(Null, 11, True)
Range("FixExp_RetMid").Select
X = EssMenuVRetrieve()
End If


If Sheets("Control Sheet").Range("RetSelectOIMid") = 1 Then
Sheets("OI").Select
X = EssVConnect(Null, username, password, server, app, database)
X = EssVSetGlobalOption(5, 4)
X = EssVSetSheetOption(Null, 6, False)
X = EssVSetSheetOption(Null, 7, False)
X = EssVSetSheetOption(Null, 11, True)
Range("OI_Main_RetMid").Select
X = EssMenuVRetrieve()
End If


If Sheets("Control Sheet").Range("RetSelectOIFormatMid") = 1 Then
Sheets("OI by format").Select
X = EssVConnect(Null, username, password, server, app, database)
X = EssVSetGlobalOption(5, 4)
X = EssVSetSheetOption(Null, 6, False)
X = EssVSetSheetOption(Null, 7, False)
X = EssVSetSheetOption(Null, 11, True)
Range("OI_Format_RetMid").Select
X = EssMenuVRetrieve()
End If


If Sheets("Control Sheet").Range("RetSelectOIMonthMid") = 1 Then
Sheets("OI INT by month").Select
X = EssVConnect(Null, username, password, server, app, database)
X = EssVSetGlobalOption(5, 4)
X = EssVSetSheetOption(Null, 6, False)
X = EssVSetSheetOption(Null, 7, False)
X = EssVSetSheetOption(Null, 11, True)
Range("OI_Month_RetMid").Select
X = EssMenuVRetrieve()
End If


If Sheets("Control Sheet").Range("RetSelectINTMid") = 1 Then
Sheets("INT by title").Select
X = EssVConnect(Null, username, password, server, app, database)
X = EssVSetGlobalOption(5, 4)
X = EssVSetSheetOption(Null, 6, False)
X = EssVSetSheetOption(Null, 7, False)
X = EssVSetSheetOption(Null, 11, True)
Range("PL_INT_RetMid").Select
X = EssMenuVRetrieve()
End If


' Disconnect from all sheets
X = EssVDisconnect("PL Main")
X = EssVDisconnect("Net Rev")
X = EssVDisconnect("PAP")
X = EssVDisconnect("COP")
X = EssVDisconnect("Fixed Expenses")
X = EssVDisconnect("OI")
X = EssVDisconnect("OI by format")
X = EssVDisconnect("OI INT by month")
X = EssVDisconnect("INT by title")


' Clear the user's password from the Control Sheet. Then select cell A1.
Sheets("Control Sheet").Select
Range("UserDetailsPassword").ClearContents
Range("A1").Select


Application.ScreenUpdating = True


End Sub

 

[seanrmckenna]

Hi,

I solved this one myself. For the benefit of anyone in the future, I used X = EssVDisconnect(Null) on each sheet and this worked. Having the sheet name in the brackets did not previously work.
This has now allowed the macro to retrieve from two separate databases.

Each individual section now reads:

If Sheets("Control Sheet").Range("RetSelectINTMid") = 1 Then
Sheets("INT by title").Select
X = EssVDisconnect(Null)
X = EssVConnect(Null, username, password, server, app, database)
X = EssVSetGlobalOption(5, 4)
X = EssVSetSheetOption(Null, 6, False)
X = EssVSetSheetOption(Null, 7, False)
X = EssVSetSheetOption(Null, 11, True)
Range("PL_INT_RetMid").Select
X = EssMenuVRetrieve()
X = EssVDisconnect(Null)
End If