run Excel Macro automatically

[sunnywink]

Hi all,

I would like an Excel macro run automatically
after user has finished query to get external data from Access database.

Is there a way where I can set an event procedure to provoke the macro automatically after user finished querying? Can Excel 97 capture and recognise the event?

Thanks in advance!

 

[ETID]

Record the "refresh data" or record the "edit query / return data to excel event as a macro then splice that code into your on workbook open event along with your other code.

 

[sunnywink]

Thanks but there is another problem. In the program, upon opening the Excel file, the user will be first prompted to key in a key word to query from access database.

I added the the Refresh data command into the Auto_open() macro before other command lines, so that the user is able to do query first before the other lines are executed.

However, upon opening the file, instead of being prompted to key in key word to do query from Access, an error message appears as follow:
" Run-time error'1004':
Application-defined or object-defined error"

The Refresh data command that I added is as follow:
Selection.QueryTable.Refresh BackgroundQuery:=False

 

[ETID]

Then record the Edit process start the macro recorder then(right click > edit query) then when msquery opens simply select file > return data to excel...then stop macro

that way, when you view the code, you will see the sql statement where you can splice / concatenate a variable obtained from an inputbox.

Make sense?