1

I am ultimately trying to automate/schedule a data retrieval from Essbase.

I started with a macro that works perfectly. Sample code:

Sheets("Sheet1").Select
range("B1:b1").Select
ActiveCell = j
application.Run macro:="EssMenuVRetrieve"

This macro will run in about 0.5 seconds and retrieves the data perfectly.

Next I wrote a .bat and vbScript file to automate the process. but this will not work.

Everything runs fine up until the EssMenuVRetrieve line of code. I know this because I put in sample code before and after to write some text to two cells for testing purposes.

When I run the batch file, it calls the macro as I want, executes the code upto EssMenueVRetrieve and then just hangs infinitely. No data comes back. And no code after it gets executed.

I cannot understand the issue going from a working macro to a not working vbscript.

R3uK
  • 14,417
  • 7
  • 43
  • 77
Eddie O'Grady
  • 11
  • 1
  • 2

1 Answers1

0

I haven't had much luck using EssMenuVRetrieve method. Take a look at the "library" of functions that come with the Essbase addin. It should be under the ExcelAddin -> bin foler and is called "essxlvba.txt" (or something like that). I read through that and successfully came up with a solution similar to the one below. It works perfectly with a scheduled bat script that opens about 15 different files and performs 100+ retrieves.

Dim wsTar as Worksheet
    Set wsTar = thisworkbook.sheets("Sheet1")
Dim rngTar as Range
    Set rngTar = wsTar.Range("B1:B1")
'EssVConnect...
EssVRetrieve wsTar, rngTar, 1 'this is the line that performs the actual retrieve
'EssVDisconnect
David
  • 41
  • 5