Access Report (Letter) With Excel Charts - Best Practice?

[lameid]

I use Access to send letters.

The gist of the process is a report is run for each recipient and exported to PDF.

I have a new project coming down that will involve using charts for each recipient. The charts will need data varied by recipient.

I am thinking use an Excel file to template out the charts and update the data within Excel.

Somehow I have to deal with Excel consuming the right data. Since this is iterative execution, I am wondering what the best way to write the data is in this scenario? Use a recordset with dynamic SQL and write directly to a range so I do not need to update a querydef? Some other way?

Then there is the issue of putting the charts into the "letter", keeping in mind the final product is a PDF.

[ol 1]
[li]Automate Excel to print the chart to PDF as well as the Access report and then automate Adobe to merge the two PDF's (fortuantely I have a class written for adobe).[/li]
[li]A little searching shows that charts can be exported to Gif's using the export method of the Chart Object which can be used in turn to set the picture property of an image control (or use a fixed name).[/li]
[li]Use an unbound opject frame. Set the Source Doc property to the workbook path and the Source Item property to the name of the worksheet + reference to the chart. [/li]
[/ol]

Amongst all that and any other alternatives I have not turned up, which way should I go?

 

[SkipVought]

Hi,

I’m a big Excel guy, but why export to Excel when you ought to be able to produce charts in Access, in fact the same chart object that you’d use in Excel?

That being said, if it were me, I’d use Excel for the entire project.
Set up (one time) a query (MS Query very similar to Access) to loop through your data. The chart data resultset in Excel would be a ListObject table that would refresh the linked chart automatically for each recipient.

Don’t know what other data you would need to generate, but you can SaveAs a .pdf.


Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[lameid]

Charts... My loose recollection of history... Different in different applications for a while, then common across office apps for a while and then Excel became special again... Did it flip back to common in some version? I want to say Excel became special again in 2010?

This is probably a good point to mention that I am on Office 2013.

But access charts... Maybe. To be honest I have not played much with graphs or charts in any office product. The consensus in forums has seemed to be use Excel for charts, it does the most, is the most flexible and does it well/quickly. FMS even says so...

https://www.fmsinc.com/microsoftaccess/dataanalysis/versus-excel.html

- check out section headed "Data Visualization is more Advanced in Excel". I could not find a date on that page/article so maybe it is old? I may have a false impression as to how well charts may work in Access and may be remembering a bad taste looking at it in an older version as I understand they are much better now in all apps and my brief tinkering reminded me of Excel 97 charts - functional but not all that great.

My catch 22 is the project definition is still in a fluid state so it is difficult to know if Access charting can meet the demands or what kind of variances may have to be accounted for.

That said, there will be multiple charts/graphs and I think multiple datasets to source those objects. My gut instinct is to plan on needing Excel and the knowledge gap on that for me as usual is big in Excel. I had to look up Listobject to know that is the object representative of an Excel table. And even so I have barely dabbled with one, years ago. I have programmatically done enough with sheets, ranges and formulas to generally have that down and I have even written code to create pivot tables and pivot cache although it has been a while. But that really is about the scope of my Excel knowledge - there is a lot I have never touched in Excel. Most certainly I have no idea which method is best for my circumstance in this case assuming Excel is necessary. My gut leaning is to push data into excel ranges by looping over recordsets while persisting an Excel object, workbook and worksheet; refreshing the chart objects and then printing to PDF. If I am right, I never have to save the Excel workbook and can produce each attachment to my letter pdf this way. But I know better than to trust my gut which is why I am here asking.

 

[SkipVought]

I wasn’t aware that all MS Office apps did not share the same chart object model.

Be as it may, Excel may be your best bet. I, too, have Excel 2013.

So where does all the data reside for your recipients?

Will all the reports be tabular? I would presume that data for charts would come from a database.

An advantage for Excel includes the ability to record code. You also have good resources at Forum68 for native Excel features and Forum707 for VBA.

I have developed many Excel applications accessing remote databases, mashing data and producing interactive reports including charts. So, in principle, your project is very doable in Excel. We are here to help.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[lameid]

Probably 80% of what I know in Excel's object model comes from recording code and then looking the things up.

The Access report is a letter with code behind it that takes care of all the odd stuff like changing headers and footers. There is a lot baked into it so while I recongnize it can happen in Excel it seems like reinventing the wheel for that portion and would also represent a fork that would need maintained.

Source data we receive is usually a CSV or Excel file. We import it to standard tables and have processes that run on those tables. We then run letters based on it by looping over a recordset of recipients and running a report to PDF for each recipient. This is of course leaving out a lot of in the weeds detail not relevant to incorporating Excel presentation objects. Graphs and charts is a novel insert / attachment for us. This explains why I am where I am. Technically I could push out what ever source information is required and have excel create the individual files but on the other hand this is creating two code sources to maintain for one project. I think this is easily avoided through automation and I believe best practice...

 

[SkipVought]

Two different approches. Maybe other members have other ideas as well.

1) Put the chart data into an Access table. In this scenario your worksheet will have a pre-configured MS Query that queries that Access table. The query resultset is returned to the sheet when the querytable is simply refreshed ([tt]QueryTable.Refresh False[/tt]). The linked chart will then reflect the new data.

2) Send the data to the Excel Sheet ListObject to totally replace the previous data (which is what happens when a Query is run), that has the chart linked to it. I’m not sure what Access command would accomplish that.

The details in either would include report specific chart annotations like the title.

The SaveAs a .pdf.

Wash, rinse repeat.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[lameid]

Listobjects... I found a stack overflow post about pulling data from listobjects in Excel.

https://stackoverflow.com/questions/50651148/importing-an-excel-listobject-into-an-access-table

The first response takes the listobject DataBodyRange.value and puts it in an array and subsequently puts that in a recordset.

I could easily go the other way and populate an array from a recordset. The not intuitive part to me is updating the listobject used range to match the correct dimensions I would put the array in (I did find something of interest notes below). It has also been a long time since I used an array to populate a range and is code I no longer have access to. A refresher there would be helpful. It seems clunky to me but is looking like the choicest solution.

The below seems a good read if you understand class modules (more for those reading for answers)...

http://www.excelandaccess.com/working-with-tables-in-vba/

Big take away for me for resizing is the pseudo code below...

<Listobject>.DataBodyRange.Delete 'Delete existing data
<Listobject>.ListColumns.Add 'add additional column, repeat / loop as needed; This likely is not needed in most scenarios as data structural changes are at best problematic
<Listobject>.ListRows.Add <num> 'add additional row, repeat / loop as needed, Typically loop over the rows, appending for each one

I obviously have not run water through the pipes on that one. The <num> above was an iterator for the loop used to append the correct number of rows. The parameter seems suspect to me from an intuitive perspective but for now I am rolling with the example and moving on to something else.

 

[SkipVought]

Your Structured Table/ListObject should be manually set up ONE TIME with the Fields required to populate your chart.

Subsequently, the DataBodyRange is deleted and simply replaced by an array that corresponds to the existing fields or a loop to load one row at a time. (In reality, deleting the DataBodyRange leaves one empty row in the Structured Table)

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[lameid]

So set the databodyrange to an array or add 1 row at a time possibly while looping over a recordset.

Setting to an array, I want to say that the set keyword is not needed in this context.

If adding rows one at a time, how are the data values/cells populated?

Am I correct in thinking that there is no reason to save the Excel Workbook while looping over this, only saving the PDF? I ask because writing to disk here seems to eventually cause weird race condition problems. I assume it is from the write cache not being flushed in some man in the middle security product but that is an entirely other can of worms.

 

[SkipVought]

Am I correct in thinking that there is no reason to save the Excel Workbook while looping over this, only saving the PDF?

The essential stuff that needs to be saved as an Excel Workbook is what’s there prior to any data loaded for the chart and that data is transient, simply being a means to produce a pdf.

So the Workbook application that you’re gonna build with some help, will be
[tt]
opened

repeatedly
[ul]
[li]loaded[/li]
[li]SaveAs .pdf[/li]
[/ul]

closed
[/tt]

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]