Save informix query to an excel file

[wallyoe]

How can I simply save an informix query to an excel file AUTOMATICALLY rather than MANUALLY.

Created a query doing these steps:
Start Excel > Data > Get External Data > New Database Query > Databases & Select "CMS ODBC"(just a previously created data source) > Identity (sign on to a Sun Server with Informix on it) > from query wizards-choose columns, select a table > on query wizard-filter data, no action taken > on query wizard-sort order, selected "acd", "logid", "row_date" > on query wizard finish, selected "Return Data to Microsoft Excel", select "Save Query", then select "Finish" > on Return External Data to Microsoft Excel, select "Existing Worksheet" "=Sheet1!$A$1", and query will load data and display query results.
This can be saved to an Excel spreadsheet manually BUT I want to save it AUTOMATICALLY via the query or some other automated scripted means. The wagent.dqy query generated (abbreviated form) is:
"XLODBC
1
DSN=CMS ODBC;UID=test;PWD=test;Servertype=Informix 2000;
Host=172.16.17.18;Port=5000;Database=cms;Options=;
Driver=C:\Program Files\OpenLink\olod4032.dll;
FetchBufferSize=60;NoLoginBox=No;Protocol=TCP/IP;
ReadOnly=No;ServerOptions=;DeferLongFetch=Yes
SELECT wagent.row_date, wagent.acd, wagent.logid
(appx 100 items)
FROM root.wagent wagent ORDER BY wagent.acd,
wagent.logid, wagent.row_date

row_date acd logic (appx 100 items)
"

 

[SkipVought]

wallyoe,

Not sure what you want.

1) Once you have inserted a query, it's there until you delete it and can be refreshed via Date/Refresh data.

2) IF you want to have some code to gen a New query, turn on the macro recorder and go thru the steps of inserting the query.

3) If you want to change the query on the fly, turn on the macro recorder AFTER you have inserted the query and record Data/Get External Data/Edit Query - NEXT-NEXT-FINISH.

Then clean up the generated code. Post back if necessary.

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[wallyoe]

Skip,

Thank YOU very much for your interest in my problem!

This is going to be a batch process that we want to set up
to be run after hours. My info is misleading--I was providing info on what I went thru to generate the query.
However once there are appx 50 queries set up and running,
they will seldom or ever will be changed. So far all steps run for me by initiating the query. The only problem is that the result I get from the query is info on the screen that I MANUALLY have to save to disk. I want the query or whatever to do this for me.

Would you have any ideas how I can initiate the macro after the query has been completed? What I see is the screen is stopped (waiting for me to review) and I have to MANUALLY save the query results to a file. I am not sure
how the macro could be initiated with this screen hanging there waiting for me. Instead of a screen popping up, I want the results of the query to do directly to a file.

I hope this makes more sense. If it doesn't, please inform me.

Again, Thank YOU very much for responding

wally