Excel VBA - MS Query

[JCH]

Excel 2000, Access 2000, MS Query 6.3

I bring data from Access into an Excel worksheet for statisitcal analysis
using MS Query. Using Excel VBA, I create a new SQL statement that depends on
the results of the analysis. Then I copy/paste the new SQL into MS Query SQL
view and repeat the process. All this works fine manually. This repeats 126
times and needs to be run each week. Eventually, I get down to the desired
recordset. To be useful, the procedure will have to be automated because it
is too much work by hand.

I don't know how to open MS Query in SQL view using Excel VBA to perform the
copy/paste.

I am sure I am missing something very simple. My reference book ("Excel 2000
Power Programming with VBA" by John Walkenbach) doesn't seem to address this
particular procedure.

Can someone here put me on the right track?

 

[Perry]

Why not use ADO to transmit data from Access to Excel.
With ADO you can use either the ODBC driver type connection from Access to
Excel
or use OLE DB provider under ADO to do more or less the same.

Here's a link to buildup ADO both connection strings
http://www.connectionstrings.com/?carrier=excel

A couple of advantages:
- your mechanism doesn't need to open MS Query for transmittance
- other than Automation, you don't need an Excel instance to transmit data
- You can use your native Access SQL to build the datasets

Krgrds,
Perry

 

[JCH]

Thank you for putting me on the right track. I am more of a statistician than
a programmer. I just muddle through VBA and SQL on a "must do" basis.

I appreciate the discussion group very much because I know I can come here
for direction when my muddleing gets muddled. Back to the book and online
help now.
--
Cordially,
JCH

Why not use ADO to transmit data from Access to Excel.
With ADO you can use either the ODBC driver type connection from Access to
Excel
or use OLE DB provider under ADO to do more or less the same.

Here's a link to buildup ADO both connection strings
http://www.connectionstrings.com/?carrier=excel

A couple of advantages:
- your mechanism doesn't need to open MS Query for transmittance
- other than Automation, you don't need an Excel instance to transmit data
- You can use your native Access SQL to build the datasets

Krgrds,
Perry

 

[Perry]

Appreciate the feedback!
Good Luck and Chrs,
Perry

Thank you for putting me on the right track. I am more of a statistician
than
a programmer. I just muddle through VBA and SQL on a "must do" basis.

I appreciate the discussion group very much because I know I can come here
for direction when my muddleing gets muddled. Back to the book and online
help now.