How can I automatically update my spreadsheet with report generated from Access

[Shilabrow]

How can I update my spreadsheet with report generated from Access monthly. I have a report that I run in access monthly. Every month, I go to the database and copy ALL data including the old and current one and paste in my excel worksheet to work on. I however want to use the parameter criteria by month to just extract monthly update and only add that portion to my existing old EXCEL spreadsheet instead of copying ALL data to date over and over.

MY QUESTIONS IS - Is there is a way to just extract the updated monthly data from access and automatically add to to my current Excel Spreadsheet to get an updated version. This is because I have a set up Model Excel spreadsheet where new data will dynamically update and the report reflecting the updated data.



Any help will be appreciated. Thanks!!

[Shilabrow]

Here is a sample worksheet (please click on the link below to see the sample worksheet) - Any and all help including VBA code will be greatly appreciated. I need to automatically update my excel file with current updated data from access database as often or monthly once I run the code.

http://www.msofficeforums.com/excel/20923-how-can-i-automatically-update-my-spreadsheet.html#post63373

[ranman256]

FROM ACCESS:
If you are in Access you can export the data to the worksheet in the excel file with
docmd.TransferSpreadsheet ...

if you need to put the data in a precise range:
Set XL = CreateObject("excel.application")
xl.Workbooks.Open pvFile
xl.activesheet.Cells.CopyFromRecordset rstData

or do you HAVE to do it from the EXCEL side?

[Shilabrow]

Thanks - is there a way for me to do this in Access. Please help me clarify and walk me through the easier route. Much appreciated!!

FROM ACCESS:
If you are in Access you can export the data to the worksheet in the excel file with
docmd.TransferSpreadsheet ...

if you need to put the data in a precise range:
Set XL = CreateObject("excel.application")
xl.Workbooks.Open pvFile
xl.activesheet.Cells.CopyFromRecordset rstData

or do you HAVE to do it from the EXCEL side?

[June7]

Ranman's code IS from the Access side.

Why do you need to involve Excel at all?

Could have Excel pull the data from Access instead of Access pushing.

[Shilabrow]

Thanks June7, That's why I need help. If you can help me straighten out the best route to get this done and how. I will really appreciate it. The sample sheet can be found from the link above. Will appreciate all the assistance you can render.

Thanks again.

[June7]

Apparently I would have to create an account on that forum to download file.

Why does Excel need to be involved?

Sounds like you might want to explore Excel capability to link to Access.

[Shilabrow]

How do I attach file to Access? being trying to no success. Whatever help or if you want I can send to you private email. June7, I sure will appreciate this.



Thanks much!!!

Apparently I would have to create an account on that forum to download file.

Why does Excel need to be involved?

Sounds like you might want to explore Excel capability to link to Access.

[June7]

What do you mean by 'attach file to Access'?

Have you looked at Excel for setting link to Access?

To attach file to post, follow instructions at bottom of my post.