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

[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!

[jonh]

This opens a file, refreshes the data connections, deletes the connections and saves a new file with today's date.
Needs a reference to excel. Open a module > tools > references.


Private Sub Command1_Click()
On Error GoTo err_Command1_Click
Dim xl As Excel.Application, wb As Excel.Workbook, cn As Excel.WorkbookConnection
Set xl = New Excel.Application
Set wb = xl.Workbooks.Open("C:\MasterCopy\Report.xlsx") '<--- CHANGE PATH
wb.RefreshAll
For Each cn In wb.Connections
cn.Delete
Next
wb.Close True, "C:\Report_" & Format(Date, "yyyymmdd") & ".xlsx" '<--- CHANGE PATH
xl.Quit
err_Command1_Click:
Select Case Err.Number
Case 0
Case Else: MsgBox Err.Description, vbExclamation
End Select
End Sub

[puella]

Thank you Jonh, I will try this.