Linking a query to excel that changes in size

[Dougalsworld]

Hi All,

Have searched the forums for an answer to this (hopefully simple) question.

I have a query out of access that displays the completed actions for a previous month. I take this and paste it into an excel file that contains the monthly scorecards.

Now in an ideal world I would link this query as I do for the static queries straight to excel so it automatically updates on openning the file...

* Copy the query
* Paste Special
* Paste Link (as text)

No for my regular queries (always consist of 10 records / lines) this works a treat...however the query in question can change in size...the problem with this is, if the query had 10 records when i linked it, but now it has 12....only the 1st 10 will update.

Is there any way round this?

Thanks

 

[Alansidman]

Why not export the query directly into the file you have in Excel. It will then bring all the data. Office automation allows this to happen seamlessly.

Alan

 

[Dougalsworld]

How would I export the query?

I know how to do a basic export, but this creates a whole new Excel file, and is quite manual...I need to export the data to an existing excel file, and preferably automatically so I do not need to manually trigger the export?

Kind regards
Paul

 

[Alansidman]

You would have to do that with a little bit of VBA. An alternative before I go there is to import the data through an MS Query directly in Excel. Here is a short tutorial on that.

http://cropwiki.irri.org/everest/index.php/Linking_data_from_MS_Access

Here is the explanation on exporting to a named location.

http://accessblog.net/2006/07/export-to-excel-range.html

You have a couple of options. Post back with specific questions as you delve into this.

Alan