modifying database query with VBA

[bsumner]

I have a worksheet that retrieves a months worth of valid data out of an oracle database. This works great but i would like to be able to modify what month and year the query requests in the background so the user does not have to go through all those button clicks in the wizard.

After looking through the help and searching here it seems like its possible, but i havent worked with excel's odbc before so i'm a bit new, i do know sql though.

i already have a button that activates a macro to do other things in this worksheet, so i can fire a seperate macro from that as well.

all i am interested in doing is modifying the sql and then refreshing the data with the new query.

thanks,

bsumner

[bsumner]

anybody have a clue?

[bsumner]

still need help.

[bsumner]

still need help.

[MattShoreson]

post your sql.

Prob best not to reply to your own posts as people will prob assume its been solved.

[bsumner]

sql isnt the issue, i need to know the excel vba syntax to get the query, syntax to set the query (if it is different), and syntax to refresh the query.

just bumping my post as it goes a day without replies, which puts it way down on the 6 or 7th page.

thanks.

[Norie]

How are you getting the data?

Are you using Data>Get External Data... ?

If you are I suggest you goto Tools>Macro...Record a New Macro... then manually import the data as you normally would.

Hopefully this will generate code that includes the SQL.

You can then adapt that to take user imput or values from a worksheet or whatever.

Post the code for further help.

[bsumner]

yeah, thats how i did it. i had'nt tried to record the macro yet though, that project is at a different site and i wont be back there till next week. I was hoping that someone would know this off the top of their head since i expect it is something simple and shouldnt take more than a few lines of code to do it, and i have not tried to record a macro before.

As far as the rest, i already have the user input setup and i already get the values, just need the sql modifier part to finish it off.

thanks.

[Norie]

I do know how to do it but it's just that it is pretty much specific to what you are doing.

Generally when you record the macro part of it should be an SQL statement as a string.

You would then write code to alter/replace that string as needed.