How to have Excel run my saved query automatically?

[MisterBob]

I have used the query wizard in Excel to setup a query to get the data I want from Access and have saved the query. The data comes into the worksheet nicely. I use ShellExecute to start Excel and load the spreadsheet from within an Access Form using a command button. I need help on a few items.

1. How can I clear just the data from the cells that have to be refreshed or can I just have the data refreshed? Users can save the data in the spreadsheet when done looking at it. I can get the range of cells that would need to be cleared.

2. Can I use a macro or something to run the query when the WorkBook_Open routine is fired off?

3. Is there a better way to get the data from Access into the worksheet than using the query?

4. How do enable macros so users don't get the message about enabling them?

 

[ide]

4. a) set manually the Tools/Macro/Security/Security level to low level.
b) create a vb script to set in the registry what above, and send it the users
c) digitally sign your project, then users can (with Medium security level) allow your macros like a "trusted source"

3. You can use Access VBA, too, to create or open an existing workbook and write data into it.

1. If the start cell of data is A2 then:
Range("A2&quot.Select 'select A2
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
'select the range between start cell and the last cell
Selection.ClearContents '...
Range("A2&quot.Select

ide