I have a macro in Excel that opens an OLE program called BI Query which
interegates one of our databses and return the results to an Excel worksheet.
Everything works fine but their is one niggle, when BI Query connects to the
database a password box appears and the whole process stop until the password
is entered. At the moment we enter the password manually. We want to make
this a totally independent process. Is there any why to get the password into
the box either through Excel or another form filling program?
For example if we schedule the macro to run at 6pm and it needs the password
at 6:05pm is there a program that anyone knows of that we could have run at
6:06pm and enter a text string into the password box which has the focus.
I've tried to do this from Excel but have had no luck, and theres nothing in
BI Query's library to allow me to do this.

Any help is greatly appreciated.
--

_______________________
Naz,
London

I don't know BI Query, but you can try this. Assuming your password is
"password" (no quotes), you can run this statement at the beginning of your
excel macro (yes, BEFORE the dialog for the password):
Sendkeys "password~"
where the "~" is the equivalent of the enter key.

"Naz" wrote:

I have a macro in Excel that opens an OLE program called BI Query which
interegates one of our databses and return the results to an Excel worksheet.
Everything works fine but their is one niggle, when BI Query connects to the
database a password box appears and the whole process stop until the password
is entered. At the moment we enter the password manually. We want to make
this a totally independent process. Is there any why to get the password into
the box either through Excel or another form filling program?
For example if we schedule the macro to run at 6pm and it needs the password
at 6:05pm is there a program that anyone knows of that we could have run at
6:06pm and enter a text string into the password box which has the focus.
I've tried to do this from Excel but have had no luck, and theres nothing in
BI Query's library to allow me to do this.

Any help is greatly appreciated.
--

_______________________
Naz,
London

Unless there's a compelling reason to use the actual .BQY document, you
should be able to emulate the query and get everything you need from the
database using ADO. Otherwise, I'm pretty sure the Brio authentication will
continue to be an issue. I've had no trouble accessing my company's Brio
data mart using Excel/VBA/ADO.

Another option would be to put your BI Query document on the Ondemand Server
and schedule it to be event driven. The event could be change in a database
field that you have permissions to write to and the output could be a
fileserver location.

Does that help any?

--
Regards,
Ron