Using Access to open, populate and save Excel template

[Widge]

I have an Excel template that has to be populated with data each
month. It has to be a this particular template because it has several
functions embedded into it which are for a bespoke accounting system.
This I'm not concerned about, all I'm worried about is getting data
from Access, month on month, into a template, and having the template
save to a drive.

Lots of the fiddly stuff like specifying drive locations, flexible
filenames etc I'm ok with. Its the talking to Excel that I'm concerned
with.

Basically, I will run an Access query each month. The result of this
query will be a few records per 'supplier' and some financial data
relevant for that supplier. I need a template opened for each
supplier, and the financial data input into certain places on the
template. This will then autosave.

How do I make Access talk to this template and how can I tell it where
to put in the data?

Any help gratefully received!

 

[Norman Yuan]

You can automate Excel to do this.

Example:

Dim xls As Excel.Application
Dim wk As Excel.Workbook

Set xls=New Excel.Application
xls.Visible=True
Set wk=xls.Workbooks.Add("[ExcelTemplateFileName]")
''Retrieve data from the query
''Populate cells on sheet(s) in the template
wk.SaveAs "filename"
wk.Close
xls.Quit

In this case, you need Excel installed, of course

However, I feel it might be better run this from Excel side, so you do not
need ActiveX automation, which is slow due to communications cross the two
applications, especially if you have lots of cell in the sheet to be
populated.

You can write some VBA code in Excel to execute the needed query in *.mdb
file, to get data, and then populate cells. This way, the cell populating a
a lot faster, and you do not need Access installed. It is also more natural
from user's point of view of doing things: when he need a data symmary
sheet, he starts Excel with the template, query data source (*.mdb), the
data shows on the sheet.

 

[Widge]

So basically. Have the template and set up an extra bit of VBA on
there to run the Access queries and pull the results into the relevant
cells?

Sounds great. I'm not fussy where the VBA lies.

Although will the VBA be intelligent enough to have an instance of the
worksheet per record that the query will return?

You don't happen to know of any good guides where I can read up about
this at all?

Thanks for the help so far!

 

[Widge]

So basically. Have the template and set up an extra bit of VBA on
there to run the Access queries and pull the results into the relevant
cells?

Sounds great. I'm not fussy where the VBA lies.

Although will the VBA be intelligent enough to have an instance of the
worksheet per record that the query will return?

You don't happen to know of any good guides where I can read up about
this at all?

Thanks for the help so far!

 

[Norman Yuan]

Since you want to populate data from a query or queries into a specific
workbook/worksheet template, you need to do some VBA programming either
inside MS Access or Excel, as I mentioned in previous reply. Simple macros
iin either Access or Excel will not give that much control you want if the
template or data from queries is a bit "fancy".

With VBA (in either Access or Excel), you can preciesely control which data
you want to retrieve, or make up your query dynamically according to user's
input, and control which piece data goes to which sheet/cell,...

There is a lot of books on Access/Excel VBA programming, and of course
learning curve to climb, if you aren't good at it yet.