VBA Screen Scraper Required/Using Mulitple Threads

[deloitte666]

My problem in brief…

I am running a routine in VBA that requires user intervention, pressing enter three times, before it completes. I want to automate this routine but do not have access to the source code and there are no parameters I can pass to it in order for it to run silently. How do I open up another thread in VBA that can click enter for me in order for the previous routine to complete? Any other solutions???

e.g. code:
Application.Run "Analyst_CubeUpdate"
//prog. never goes beyond this pt awaiting user
Application.SendKeys "{RETURN}"
// my feeble attempt but never reached as the previous call never completes.

HELP,
Craig


The full scope…

I am attempting to integrate “Palisade Software’s @Risk” tool with Cognos Analyst Version 7.2 Excel Add-in in order to perform Monte Carlo sensitivity analysis on my Cognos model.

The @Risk software is an excel add-in. It works by altering cell inputs dependant on a user defined distribution (e.g. expected value, max, min in Triang distribution). The program re-calculates all cells in the workbook(s) and monitors the targeted cell output, this is repeated over a 1000 times in order to build up the figures for statistical analysis on the results and outline cost drivers etc…

I have integrated this tool with Cognos by placing the targeted inputs in an excel spreadsheet that feed into my model by use of the Cognos Excel Add-in. As there is an option in @Risk to run a VBA macro after changing each input, I have produced one that calls Cognos functions to update all D-Links & Cubes and then outputs the result back into my targeted output cell in my monitored spreadsheet.

This works fine, HOWEVER, when I run the Cognos functions they have popup windows that require user intervention in order to execute the commands. This is no good as I need the process to be autonomous. I have tried everything in order to make the functions silent but nothing seems to work, no parameter etc…

I have spoken to Cognos support and they cannot give me a patch for these functions and (as you would expect) I do not have access to the code as the add-in is password protected. Apart from getting in a woodpecker to sit with the machine over night I’m out of ideas!?!

One thing I did think of a potential solution would be to open up a duel thread in VBA that works in parallel with the call to the Cognos code and hits enter on the active window in order to automate the process. Does anyone out there know a possible solution?

The code for the excel macro is as follows:

Sub MonteCarloRefresh()
Application.ScreenUpdating = False
AnalystDisableMessages True
Sheets("Input").Select
Range("A5").Select
Application.Run "Analyst_CubeUpdate" //prog. never goes beyond this pt awaiting user
Application.SendKeys "{RETURN}" // my feable attempt but never reached
Application.Run "Analyst_CubeSave"
Application.Run "Analyst_MacroExecute", "LRUC Common (OBC 04)", "MUM Monte Carlo"
Sheets("Output").Select
Range("A5").Select
Application.Run "Analyst_CubeRefresh"
Application.ScreenUpdating = True
End Sub

 

[SkipVought]

Hi,

What's the code for "Analyst_CubeUpdate" ?

Skip,
[sub]
[red]Be advised:[/red] It's been reported that a wee psychic is roaming the countryside.
Small Medium @ Large! [/sub]

 

[DeeBeeGee]

Thinking a long way outside of the box and indeed good programming practice but hey where needs must you could maybe get your program to send you an email with an individual subject line and a delay if necessary just before calling Analyst_CubeUpdate and in Outlook you could set up an event where an email arrives with that subject which simply sends the keys you require. If you are running overnight then this will be safer than it initially seems as there will be no user interfering.
However I would still encourage a more proper solution if possible

 

[deloitte666]

Cheers for the response guys!

Ive actually fixed the problem now. If I run the "Application.SendKeys {RETURN}" before running Analyst_CubeUpdate the command is saved in cache and used at the next available point.

Application.SendKeys "{RETURN}
Application.SendKeys "{RETURN}
Application.SendKeys "{RETURN}
Application.Run "Analyst_CubeUpdate"

I'd actually tried this before but only with one keystroke so didnt notice if it was working (as 3 popups appear).

Cheers!

p.s. cubeupdate is the calculate the cognos cube relating the view on screen