Automating Change of String Value in Querytable SQL statement 1

[GoatieEddie]

Hi All,

I have set up a worbook that has 5 or 6 querytables in it. I created these using the wizard and not code (dumb in hindsight!)

My question is this: I now want to recreate the exact same workbook but changing one of the criteria in the SQL statement. I know how to do the replacing bit and how to access each querytable but I am stuck on how I pull to out the existing SQL and then send it back having changed the string.

Can someone point me in the right direction please?

Thanks,

GE

 

[DPlank]

If your query is stored in the workbook (and your code only has the refresh statement) then you need to:

right click on the area where the data is returned to Excel and select Edit Query.

This opens MS Query and you can change the SQL as you like. Use MSQuery to save the query under a different name and don't save changes to your workbook.

In the new workbook, Data->Get External Data->Open Query will let you open the amended query you saved.

Cheers,
Dave

Probably the only Test Analyst Manager on Tek-Tips...therefore whatever it was that went wrong, I'm to blame...

animadverto vos in Abyssus!

Take a look at Forum1393!

 

[GoatieEddie]

Yep I can do that and that is what I am doing at the moment but I want to run a macro to do it as I effectively want ot use this wokbook as template for 4 others and didn't fancy the idea of all those manual changes if a quick for each loop could do it for me!

 

[PHV]

Have a look at the QueryTables collection of the Worksheet object and at the SQL (or CommandText) property of the QueryTable object.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[GoatieEddie]

I had a sneaky peek at that beforehand and what I am wondering is do I have to passback all the other properties of the original QueryTable eg connection etc or can I just get away with changing the commandtext property and then doing a refresh?

 

[xlbo]

Something like this:

For each sht in thisworkbook.worksheets
 For each qt in sht.querytables
  [COLOR=green]'test for querytable name[/color]
  if qt.NAME = "The one you want to change" then
      theOLD_SQL = qt.COMMANDTEXT
      qt.COMMANDTEXT = "New SQL goes here"
  else
      [COLOR=green]'no changes needed[/color]
 Next
Next

Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question

 

[GoatieEddie]

marvellous. And I guess I need a qt.refresh after the qt.commandtext line. many thanks guys. helpful as always.

 

[xlbo]

qt.Refresh BackgroundQuery = FALSE

to make sure that the refresh is completed before the code moves on...

Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question