AggieJustin

Where are our Microsoft Office gurus?? Got a question for you...

I'm working on a Microsoft Excel 2002 workbook that contains a number of external data ranges that are pulled in using SQL queries from an Oracle database. The workbook is basically a summary view of data related to a project, and I'll be using this workbook against many different databases.

I've got the queries all working and I can get the data pulled in, but I'm looking for an easier method to update all of the ranges automatically. Right now, I have all of the ranges set to not remember the password so that I can update the target database for the range each time I refresh. If I use the Refresh All function from the External Data toolbar, it will prompt for the user info for each range (and there will probably be about 50-60 ranges in the book). What I'd want is for it to prompt once, then use that login info for each range in the workbook. This would make it easy to point the workbook at a new database, and update the entire thing with data from the new database.

Any ideas? I'm guessing I'll have to put together a macro to grab the login info and pass it to the refresh command, but I don't have much experience with VB. So any help you guys could provide would be much appreciated.

Justin

grantx5

Oracle is going to see each data fetch as a separate session so not much you can do about the userid/password entry.

VBA is easy to use and if you do a little research I think you'll find that writing some code to accomplish your work is pretty easy.

Take a look at:
http://www.dicks-clicks.com/excel/Ex....htm#CreateVBA

also see:
http://support.microsoft.com/default...b;en-us;306125

Good luck.

AggieJustin

Thanks for the links Grant. So I've switched up my method so that the VB code is creating the connections and contains the queries as shown in the examples. Works much better as I can use prompts to get the user/password/database info from the user at the beginning, then substitute it into the connect line for each query. This way I just enter it once and let it run.

Now I just have to figure out how to get my form and my code to play together. I was satisfied having 3 prompts pop up that grab one piece of info at a time, so I created a form to capture everything in one window. The form looks nice, I just have to do some learning to figure out how to get it to pass the data back to the code to use in execution.

Back to the internet...sure wish I was at home where I could pick up a VB book.