Open Excel file from Access, refresh data (multiple query results), save and close Excel file

[puella]

I would greatly appreciate it if someone could help me.

I need to create charts based on Access queries (I use MS Office 2010.) I was hoping to use Access native charts and to insert them in the form, but they turn out to be outdated, difficult to use, and the options are limited. This may be important (it caused problems when I tried connecting Excel to Access data): the database that contains queries is linked to the tables, which are located in a separate Access database.

The task: I would like to import a few queries from Access into an Excel file, and then manually add the charts based on the query data to this file. The Excel file will have a number of tabs, one for each department. Then I want to open Access every night around midnight (using a Windows task; I know how to do this part, but need help with everything that follows...), open the existing Excel file from inside Access, refresh the tables in Excel based on the updated query results (the data in the linked tables in Access get updated every evening - they are a backup of my Salesforce app), close and save the Excel file. My users should be able to open this Excel file and see the charts based on the freshly updated query data. (I want the tables in Excel to be refreshed only at night, so that the users can quickly get their charts, not waiting for Excel file to refresh.)

Again, your help would be greatly appreciated!

 

[TerryHogarth21]

I would greatly appreciate it if someone could help me.

I need to create charts based on Access queries (I use MS Office 2010.) I was hoping to use Access native charts and to insert them in the form, but they turn out to be outdated, difficult to use, and the options are limited. This may be important (it caused problems when I tried connecting Excel to Access data): the database that contains queries is linked to the tables, which are located in a separate Access database.

The task: I would like to import a few queries from Access into an Excel file, and then manually add the charts based on the query data to this file. The Excel file will have a number of tabs, one for each department. Then I want to open Access every night around midnight (using a Windows task; I know how to do this part, but need help with everything that follows...), open the existing Excel file from inside Access, refresh the tables in Excel based on the updated query results (the data in the linked tables in Access get updated every evening - they are a backup of my Salesforce app), close and save the Excel file. My users should be able to open this Excel file and see the charts based on the freshly updated query data. (I want the tables in Excel to be refreshed only at night, so that the users can quickly get their charts, not waiting for Excel file to refresh.)

Again, your help would be greatly appreciated!

If your access data is updated every evening, I'm going to suggest using Excel as the front end to query the access data. Excel's charts, pivot tables, and pivot charts are a better presentation of information than what Access can give you out of the box.

In the Excel ribbon Data you will find multiple ways to connect to data source. Have a read below.
Connect an Access database to your workbook - Excel

Once you establish the connection you can change the SQL string to something like the below then add a parameter in there that links to a cell for that department on the specific worksheet.
Customize a parameter query - Excel

 SELECT * FROM Table1 Where Department = ?

You can even set the Excel file to refresh the connection on open, thus users would always get the most up to date information once it's open.

 

[puella]

Thank you TerryHogarth21! Unfortunately, been there, done that... This is how I initially wanted to proceed, but since I have Access tables linked to my Access reports database, Excel did not want to comply. I try many different ways and finally decided that the way I described would be the best for my situation...

 

[TerryHogarth21]

Thank you TerryHogarth21! Unfortunately, been there, done that... This is how I initially wanted to proceed, but since I have Access tables linked to my Access reports database, Excel did not want to comply. I try many different ways and finally decided that the way I described would be the best for my situation...

When you say Excel did not want to comply - what exactly do you mean by that? If you can give specific examples or the error you are running into that would help out a bit.

If Excel queries access you would be able to use that data in charts, graphs, etc - unless you mean you want to keep the reports in Access itself?

 

[puella]

Yes, I want to keep the queries in access itself...

 

[TerryHogarth21]

Yes, I want to keep the queries in access itself...

So just to clarify, what is the end application that you intend your users to use to see this information?

 

[puella]

Excel...

 

[TerryHogarth21]

Excel...

So then what error or issues are you having then when you follow my original post to your reply?