MS ACCESS Query Results linked to an Excel Spreadsheet

[egrill]

I am looking for away to link the results of my query to an Excel spreadsheet that will automatically update and then emailed. Right now I export and send. I need a more efficient way. It is on-going report done 3 times a week. Can I build a marco?

[Stang02GT]

I am looking for away to link the results of my query to an Excel spreadsheet that will automatically update and then emailed. Right now I export and send. I need a more efficient way. It is on-going report done 3 times a week. Can I build a marco?

Is it the same data every week that is being changed?

[egrill]

Yes. The tables in the query are linked to a real time database. It has not parameters other than to pull the data from the tables based on the query parameters that are predetermined.

[Stang02GT]

what if you linked the Excel Spread sheets into your database as tables....then since you are updating the same information everytime, and changes you make in the access application will be made to the excel sheets. This eliminates the need for macros and VBA coding to export to excel....is this something you think would work or am i way off base ?


I have done something very similar to what you are doing. I had a real time database that i was pulling information from ( this data was only used to update my spread sheets)

[egrill]

How would that work? I run the real time query in Access; it gives me a new table result. The query is based on a vendor request that I need make so I get the correct information How do I link the results into an Excel so I can send it as an attachment in an email to the warehouse manager (remotely)?

I would love to stream the data from ACCESS right into Excel and skip the export function each time.

I think we are working in the same direction; I just can see the forest not the trees on this one.

[Stang02GT]

Alright i have a better understanding of what you are trying to do now.... i'm going to toy with the idea for a little and hopefully i can come up with something to help you !

[Emmi]

I have a "graphs" spreadsheet that I have tied to multiple queries in my live access db. This way I can refresh my queries in excel and my graphs are rebuilt in Excel based on the new data coming in from my database. These are graphs that I have to update quite a bit so I looked to automate it. Is this what you are looking for?

If so, in Excel under Data choose import external data - then follow the wizard to find the query you are importing the data from. Make sure on your toolbars setting you have the External Data toolbar shown. Then when you want to update the information on your spreadsheet click on the refresh button on the External Data toolbar. The query is run in access - and the results are brought into Excel with current information.

Not sure if this is what you meant.

Emmi

[egrill]

Emmi

Thank you. Thank you. I wish I could buy you lunch sometime. It worked as your said. This saves me a lot time.

Have a nice day

[Stang02GT]

Good to see you got your problem solved :) Good luck!

[Emmi]

So glad I could help, I've gotten quite of bit of help here myself so glad I could return the favor!

Emmi