Hi,
I have a situation where, there is ad-hoc report, in which the user will enter the prompt values.
But, he has to enter 75-100 values every day to get the report and it is very time consuming. So what we thought is, the user will get his prompt values in a excel file. Through VBA code, we capture all the values from the excel file and pass them as the filter variables.
Is this possible ?? Is there any sample code to do this ??
Regards
Rajiv
rajivbobj 
(BOB member since 2005-02-11)
If you have the results in an excel file, then why not create a dataprovider on it, and use the results from this dataprovider as input in the normal dataprovider ? (select ‘in list’, then ‘select query results’ in the condition)
It would be far easier. And since you can get them in an excel file, can’t you get them to your original database so you can use them in your original query ?
Regards,
Gerard
highandstoned 
(BOB member since 2005-08-01)
A more controlled way is to store the values in a database. You can use ms access for the front end and connect to a sql server or oracle back end.
Then all you need to do is to create an ado connection and a function (eg in sql) and return the value to the code. This is what I do and it works great. You can even have a default prompt so that more than one report can share the prompt value and create an overrider prompt for certain documents.
jonathanstokes (BOB member since 2004-09-17)
You’ve been given some excellent suggestions. You may also find this utility a useful alternative. I have a set of users that are similar to yours … periodic lists provided in Excel (like serial numbers), and they absolutely love that utility.
Dwayne Hoffpauir 
(BOB member since 2002-09-19)
WOW Dwayne,
Your Utility works great. Thanks for the help.
I thank others also for giving valuable suggestions.
Regards
rajiv
rajivbobj (BOB member since 2005-02-11)
You are quite welcome! As I’ve said before, I write those utilities for purely selfish reasons 
, but they are usually useful to others as well, so I don’t mind sharing.
This particular one has a story behind it, that will probably have several of you nodding your heads. In 2003 BZ (before ZABO), users of one particular database had individual Oracle IDs. The supported tool was Q+E (anyone remember that), but any ODBC connection would work. With ZABO, they lost the ability to connect directly to the database. This one department had a fit, because they often needed to extract data based on a list from a spreadsheet. The conversation went something like this:[list]Them: We MUST keep our Oracle IDs!
Us: Sorry, that’s not possible. Plus, ZABO is very easy to use and we’re quite certain you will like it quite a bit.
Them: But how will we join our spreadsheet data to the database data?
Us: Join? You’re loading the spreadsheet into the database to do the join?
Them: We have no idea, it just works.
Us: Well, we’re pretty sure you’re not allowed to load data into the database. Tell us exactly what you’re trying to accomplish.
Them: We get a list of ID numbers in a spreadsheet each week, and we need to pull information from the database for that list of ID numbers.
Us: Got it … and how are you doing that today?
Them: Suzie (who left 3 years ago) built this Access database. It’s on this PC over here. We just run this query and it works.
Us: {light bulbs beginning to come on} Show us … Ahh, we see now … You’ve linked the Oracle table and the spreadsheet into the Access database, and built the join that way.
Them: Yes, that’s what Suzie said!
Us: I’m guessing it takes a long time to run, doesn’t it?
Them: It sure does! That’s why it’s on this other PC. It takes two hours to run.
Us: {trying not to be too technical} Well, the reason it takes so long is that the ENTIRE 3.5 million records are being downloaded to Access behind the scenes each time. Access makes it look quite seamless, but there is a LOT of work going on behind the scenes.
Them: But Suzie said it’s the only way.
Us: {a few days later} Give this utility a try.
Them: WOW! It only took 10 seconds to run! You guys are great! Thanks!
Us: No problem … and no need to thank us. Do us a favor and tell your management though! Plus, you now have an extra PC.[/list]See, I knew you’d be nodding your head !!!
Dwayne Hoffpauir (BOB member since 2002-09-19)