transferspreadsheet to excel template

[Tcurtis]

I am trying to streamline my charting requirements and need some advice. I want to export the queries I used for charts to excel and have that linked to power point. These reports are monthly and my manager would like to have these saved for historical review. So, I know I can export my queries into excel and that works great. I can link my power point charts to this data but how do I keep the charting requirements as a template between all three? If this was just a monthly run, there would be no problem. Keep these reports not just per monthly but also per facilities has me baffled. I know access cannot export the query information into named cells. How do I get around this?

 

[Micron]

Not sure I understand some of your comments:
how do I keep the charting requirements as a template between all three?
Keep these reports not just per monthly but also per facilities has me baffled.
access cannot export the query information into named cells (specific cells?? Ranges are usually given names, not single cells?)

I think you alluded to the fact that the TransferSpreadsheet function does not allow you to specify a template file, which is correct AFAIK. This means you'd need to use Automation to Copy>SaveAs your template as a new file, then either continue with Automation or use the transfer function. If you need to target specific cells even when pushing data from Access to Excel, either
- continue on with Automation and push the data to specific cells or
- your workbook template could have one sheet take the Access data in contiguous cells which are referenced in the sheet that actually does the charting or
- the chart might be based on a pivot table of those contiguous cells

 

[FlammConsulting]

Why not store the data sets in an Access table? Each month, append the chart data to a table, tagging each row with fields for year, month, facility code, etc. Then, either export the current month's data from the table to Excel/PowerPoint, or just hook up PowerPoint directly to the Access table with a query that takes the current month as a parameter. That way, you can access the historic data by querying the table rather than trying to dig the data out of Excel. You could also use PowerBI to connect Excel to the Access table and host the query in PowerBI (PowerQuery in older Excel versions).