MrExcel Publishing
Your One Stop for Excel Tips & Solutions

problem with macro running ms query - external data ranges


Posted by Patrick LIngle on July 24, 2001 3:15 PM

I have set up a macro that runs several queries using MS Query. MS Query is hitting the AS/400 using ODBC. When I recorded the macro to run the query, I selected the Properties box that pops up after you have selected the query to run. From the properties box, I deselected the box that gives you the option to save the query definition. If you don't do this, after the query is done the query definition is saved in the range of cells populated by the query. I don't want the query definition saved because if it's saved I have trouble saving the worksheet.
For some reason, even though I recorded the macro to not save the query definition, it is saved when the macro is run. I can go back and delete the definition manually by right-clicking on a cell in the range of cells populated by the macro and deleting the external data range. However, this is a pain and if I forget to do it and have done other work I can't save the spreadsheet.

Any help on modifying the macro to remove the query definition would be greatly appreciated.


Posted by Alex on July 25, 2001 7:22 AM


Patrick,

I know it's not strictly answering your question but I had a similar task to do recently where I tried using MS Query in an excel macro to do various things with a MySQL database. I came to the conclusion that the easiest way to proceed was to dump MS Query altogether and use the XLODBC.XLA add-in.
Have a look at the link below and see if it might be a better way for you. I found it easier because of the simple documentation in the Vbaxl8.hlp help file (in your Office directory) whereas I couldn't find anything about using MS Query in VBA.
I'm fairly new to this so if this is entirely leading you up the garden path then apologies!

Regards
Alex

Posted by Alex on July 25, 2001 7:24 AM

Doh! - link didn't show up!


http://support.microsoft.com/support/kb/articles/Q124/2/18.asp?LN=EN-US&SD=gn&FR=0&qry=VBA%20SQL&rnk=1&src=DHCS_MSPSS_gn_SRCH&SPR=XLW97