CODEWITHSUNDEEP
windowsms-accessvbams-access-2013
Phibins
Phibins

Reputation: 121

Storing Excel templates in my Access database

What I want:

I am working with an MS Access database to produce a report for the Ontario Professional Engineering University Accreditation board. The PEO have their templates in MS Excel and I MUST use their exact format.

I want to output the data from the Access DB to the Excel sheet (easy enough), but I want to copy their formatting when producing a new file. (I do NOT want to keep an empty template file and copy it each time).

So essentially I'm looking to somehow store the template in code. (Other suggestions are welcome!)

What I've tried:

Some of you will read this and think i'm an idiot. But what i tried was to get the data from excel on the MS Clipboard through the API Code and store the DataObject as (i was hoping) some sort of string.

But i could not preserve the formatting or cell shading from the original MS Excel template.

Any suggestions?...

Upvotes: 2

Views: 1738

Answers (2)

Gord Thompson
Gord Thompson

Reputation: 123409

You could store the Excel template(s) within the database by creating a table that includes an Attachment field

DesignView.png

creating a record and saving the Excel template as an attachment to that record

AttachmentsDialog.png

and then using VBA code like this to save a new copy of the Excel document to disk whenever you need one:

Option Compare Database
Option Explicit

Public Sub SaveReportTemplateToFile()
    Dim cdb As DAO.Database, rowRst As DAO.Recordset, attachRst As DAO.Recordset2, attachField As DAO.Field2
    Set cdb = CurrentDb
    Set rowRst = cdb.OpenRecordset("SELECT TemplateFile FROM ReportTemplates WHERE ID=1")
    Set attachRst = rowRst.Fields("TemplateFile").Value
    Set attachField = attachRst.Fields("FileData")
    attachField.SaveToFile "C:\Users\Gord\Desktop\" & attachRst.Fields("FileName").Value
    Set attachField = Nothing
    attachRst.Close
    Set attachRst = Nothing
    rowRst.Close
    Set rowRst = Nothing
    Set cdb = Nothing
End Sub

Upvotes: 5

Jens
Jens

Reputation: 879

What you are going to have to do is first export all the data into a new sheet of an empty template, then link all the data to the correct place on the template and then remove the sheet with all the data, keeping the data on the template.

As far as I know there is no quicker way to do this, but here is an example: http://www.rogersaccesslibrary.com/forum/topic350.html

It will be quite a job, but doable, if the template is well setup.

Upvotes: 1

Related Questions