Wanting to use Business Objects references and queries in Excel VBA but struggling

[VbtoMe]

Hello,

I want to be able to connect Excel VBA to a database via a Business Objects universe that is already attached to the database. I then want to send SQL through the BO universe to query the database. Specifically, I want to be able to copy/paste the SQL generated from a BO report I use into the VB code and then paste the query results directly into the open spreadsheet. Is this possible?

I have managed to connect to the DB via an ADODB connection with my login details. I can then send it basic SQL but I can't send the SQL that is generated by BO. I can't use this method as as soon as put in a parameter such as data from a specific date, it crashes (no error message, just locks screen and is unlockable). I have read that it shouldn't be ADODB conn as BO is Oracle and should be an ODBC but I'm not sure of how to deal with this.

In references, I have selected:

- Visual Basic for Applications
- MS Excel 14.0 Object Library
- OLE automation
- Business Objects 5.1 Object Library
- Business Objects Designer 5.1 Object Library

I ask because we have an old version of BO and it is really slow and falls over a lot. I have to hope that a BO report runs overnight, exports its results to a .txt file and then I upload it into an Excel spreadsheet. I'd like to shorten that process by querying the DB from Excel and returning the results.

I can also use PowerQuery but it makes my file size huge so I want to do it via VBA.

Sorry for the long post.

[westconn1]

did you check your ADO connection with http://www.connectionstrings.com/oracle/

some database can take so long to return query it can be quicker to return all and loop though, or filter the recordset, though i would not recommend that as good practice
i am not sure of the corrct date format for oracle queries, but it must be correct format

as you have not shown any code for your query or connection strings hard to know