Excel & Access integration

[Mary Parks]

Is it possible to set up a database switchboard so that I can generate
an Excel file from a template, using a form to populate certain fields
in the Excel document?

I'm setting up a contact manangement database for a group and need to
set up a form that the group leader can use to automatically generate
the Excel document (a trip invoice) from a template I already set up.
Also, I need to have functionality where the group leader fills in the
information for that particular trip on the Access form, and it sets
certain cells in the Excel document.

Any advice, help, etc. (even if it's just telling me this isn't
possible), would be greatly appreciated.

Thanks!

 

[Guest]

Hi Mary,

Not only is it possible it's a fairly common use of MS Office integration;
all MS Office applications have a VBA programming interface and each
application has one (or more) COMs (component object model). To effectively
combine two or more applications you have to be familiar with the models for
those applications and be aware of a few idiosyncrasies with each application.

But before leaping into an Access-Excel project sit back and think whether
this is the best solution. From your write-up the user (group leader) will
using Access. If the concept is to create an Excel spreadsheet and then read
back the data into Access is it not possible to set up an Access form to do
the same job and avoid the complexity of using Excel? On the other hand you
may be generating a preformatted Excel spreadsheet to be distributed to the
relevant people.

If you go ahead with the Excel solution then you can use an existing Excel
spreadsheet as a template. In fact using Windows APIs you can even go as far
as allowing your user to select the spreadsheet to use. (I don't know of any
controls available in Access to do this.) No guarantees however that your
Access code will have the desired effect on spurious spreadsheets.

A risk of hard-coding an Excel spreadsheet's details into your Access code
is that it may not be found - name or directory path is invalid. As an
alternate you can create your spreadsheet from scratch.

A final consideration - but not one that causes any problem - is that when
instantiating an Excel spreadsheet from Access you have to first determine
whether the Excel application is already running and if not then start it.

If you can be a little more specific then perhaps I can give you some
boilerplate code. As I inferred above you need to be somewhat experienced
with VBA and the COMs (or at least the class-collection concept of the COMs);
I wouldn't recommend starting this with no knowledge.

Regards,

Rod

 

[Guest]

Mary,

To get some experience, try the following.

Create a new Access project (mdb)

Make sure that the Excel dll is referenced. Press [Alt]+[F11]. Select Tools
from the main menu. Select references. If you don't see it at the top of the
list against a checkmark then scroll down the list and find 'Microsoft Excel
11.00 Object Library' and put a check mark against it. (The version on your
system could be different.)

Return to the Access window and create a new form in design mode. Put a
command button on this form.

Behind the button's Click event enter the following code. (I'm using the VBA
or code window here, not a macro.)

Dim strTemplate As String

Set objExcel = CreateObject("Excel.Application") 'Create an empty Excel
app.
strTemplate = "C:\My Documents\MyTemplate.xls" 'Change this any .xls
objExcel.Workbooks.Add (strTemplate)
objExcel.Visible = True 'The application is
initially invisible

One final thing to do. While in the VBA module for your form, go to the top
of the module - the declarations section - and enter the following line.

Private objExcel as Excel.Application

Return to the Access window and test your form's button. If you've done
everything right then an Excel session will be started using your .xls as a
template.

Regards,

Rod