Access into Excel template

[skydivetom]

Hello Experts:

I have been working on a re-engineering project allowing the customer to more efficiently apply data management procedures. More specifically, the re-engineering process included, but was not limited to, the design of a relational database. Much of the architectural design has now been completed and I am transitioning into the development of reports.

For many reports, I have been utilizing Access’ report objects IOT to output information into the typical 8.5 x 11 report format.

However, additional legacy reports (i.e., in Excel where some files have more than 50 columns) probably need to be exported (from a query) into Excel vs. utilizing Access’ reports.



Attached sample Excel only illustrates how the current Excel file summarizes information.
- As mentioned previously, the actual Excel has 50+ columns (for demo purposes, there is no need to include them all in this sample).
- Also, the Excel file has the column headers rotated.
- And, finally, the Excel uses grouping to sum up columns C:E for each branch.

I have my doubts that mimicking this Excel in Access is the best choice (see example Report1 in Access). It looks pretty poor – instead, I believe I should simply export the query into Excel.

My question: If I export the query into Excel (see “Query.xlsx”), the format however is completely different than the format of the legacy Excel file (see “Legacy.xlsx”). How can I export the data from Access into Excel and maintain the format of the Excel legacy file? Naturally, the number of records will grow in the Access database.

Ultimately, I’d like to utilize the format of the legacy Excel file as a template (with column headers rotated + the group sums) and simply export data w/o losing the integrity of the grouping as the number of records will grow. Is that doable? If so, how?

Thank you in advance for your assistance to determine an export routine. If the export routine (from Access query into Excel file) is the best option, I need to be able to easily modify such routine since I need to integrate the additional fields/columns, etc.
EEH

[alansidman]

You could Export the Query to Excel and apply a Pivot Table to the results. This could easily be accomplished with a little bit of VBA programming.

This may help-->http://accessblog.net/2006/07/export...cel-range.html

[skydivetom]

Alansidman:

Thank you... I'll give it a try. Appreciate the feedback.

I'm also considering creating a connection (from Excel to Access) IOT to keep the format. Again, thanks for the recommendation.

Cheers,
EEH