Linking Access table to Excel

[Daniel Lee]

I have a table in Access that I run many update queries on to get the data
in the correct format. Once this is completed, I then need to export this
data into an Excel workbook onto a specific worksheet, where I then have
Pivot Tables setup to use this data. I have been copying the data from
Access to Excel, how do I set this to do by a macro or VBA?

Thanks...Daniel

 

[Rod Plastow]

Hi Daniel,

Are you using a stand-alone MBD? Does Excel run on the same machine as the
stand-alone MDB? Does your Excel spreadsheet already exist ready to receive
your data or do you have to create it anew each time?

How do you rate your VBA experience with Access and/or Excel? How familiar
are you with the Excel Component Object Model?

To reassure you, what you want to do is quite feasible but in order to
better help you I need to gauge your level of experience.

Regards,

Rod

 

[Daniel Lee]

=?Utf-8?B?Um9kIFBsYXN0b3c=?= <[email protected]>
wrote in
Thanks Rod, see my comments below.

Hi Daniel,

Are you using a stand-alone MBD?

[Daniel...YES]

Does Excel run on the same machine as
the stand-alone MDB?

[Daniel...YES, from the same network folder]

Does your Excel spreadsheet already exist ready
to receive your data or do you have to create it anew each time?

[Daniel...The Excel workbook and spreadsheet does exist and ready

to receive the data.]

How do you rate your VBA experience with Access and/or Excel?

[Daniel...minimal]

How familiar are you with the Excel Component Object Model?

[Daniel...Not at all. Can you give me some examples or a web site

to look into?]

To reassure you, what you want to do is quite feasible but in order to
better help you I need to gauge your level of experience.

Regards,

Rod

[Daniel...I get the data from a delimited text file where I first

import to another Excel worksheet in the same workbook, I have Linked to
the Access mdb. I then append this linked table into an intermediant
Access table where I run many queries to update some of the data. At
this point is where I need the data back into Excel to use in several
Pivot Tables.

Would it be better to Update the data in Excel without doing this step
in Access? If so, how would I perform the update in Excel? Macro's!

Thanks again for the help, Rod.

Daniel

 

[Rod Plastow]

Hi Daniel,

It should be quite straightforward. I will prepare something for you.
Meanwhile do the following. (It applies to v2003 and I can't guarantee the UI
in earlier versions is the same.)

Start Excel
Press [Alt] + [F11] The VBA coding window displays.
Click on Help on the Main Menu
Select Microsoft Visual Basic Help
From the Visual Basic Help pane select Microsoft Excel Visual Basic Reference
Finally select Microsoft Excel Object Model

What you now see is a schematic of the Excel COM. Each and every object is
context sensitive so click on any of them for further information. I suggest
you start with Application, Workbook, Worksheet and Range.

It's all a little overwhelming at first but the important thing to grasp at
this time is that this COM structure exists and is the 'key' to manipulating
Excel properties and methods. For your immediate project you need only a
fraction of this structure.

You notice that the schematic differentiates between collections and
objects. A collection is just that, a number of object gathered together.
Thus the Application contains a Workbooks collection and each Workbook
contains a Worksheets collection. Well yes but have another look at the
schematic; the Excel designers have changed the 'logical' hierarchical
relationships. Both the Workbooks collection and the Workbook object are
children of the Application. The Worksheet object is a child of the Range
object. The Worksheets collection does exist but has no parent.

While in the Visual Basic Help pane also browse the Programming Concepts
topics. Don't try and learn everything you read; just remember that it exists
and can be consulted in the future.

Incidentally you can do the same thing in Access and you will discover more
than one COM. There are COMs for Access, ADO, DAO and Visual Basic.

Enough for now.

Rod

 

[Rod Plastow]

Hi again,

I realise I never answered your final question: Excel or Access? Well, 'You
pays your money and you takes your choice.' Consider the strengths of each
application.

Access contains some superb visual programming tools and wizards, notably
form design and the Query Grid. It also encompasses a good small scale
database manager (Jet) and has the ability to link to ODBC compliant
databases (e.g. Oracle). So for structured data, queries based on that data,
repetitive tasks performed on record sets and a flexible UI (i.e. forms)
Access is your choice.

Excel is superb for displaying immediate results and handling numeric data.
Cross analysis is particularly easy in Excel. So are charts, pivot tales,
etc. Excel is used and understood by a wider user community, particularly the
Accounting fraternity. Data is stored and persisted in the spreadsheets; a
spreadsheet is analagous to an Access table.

If you are performing repetitive tasks on a set of records and/or need to
query that data and/or need to provide a comprehensive user interface then it
is probably worthwhile importing the data into Access. However it is
perfectly feasible to do everything you have described solely in Excel. If
you choose to use only Excel then I do suggest you repost your requirement in
the Excel section of these boards as you will solicit more qualified help
than generally available in this section.

If you choose Access then let me know and we'll take it a step further.

Rod

=?Utf-8?B?Um9kIFBsYXN0b3c=?= <[email protected]>
wrote in
Thanks Rod, see my comments below.

Hi Daniel,

Are you using a stand-alone MBD?

[Daniel...YES]

Does Excel run on the same machine as
the stand-alone MDB?

[Daniel...YES, from the same network folder]

Does your Excel spreadsheet already exist ready
to receive your data or do you have to create it anew each time?

[Daniel...The Excel workbook and spreadsheet does exist and ready

to receive the data.]

How do you rate your VBA experience with Access and/or Excel?

[Daniel...minimal]

How familiar are you with the Excel Component Object Model?

[Daniel...Not at all. Can you give me some examples or a web site

to look into?]

To reassure you, what you want to do is quite feasible but in order to
better help you I need to gauge your level of experience.

Regards,

Rod

[Daniel...I get the data from a delimited text file where I first

import to another Excel worksheet in the same workbook, I have Linked to
the Access mdb. I then append this linked table into an intermediant
Access table where I run many queries to update some of the data. At
this point is where I need the data back into Excel to use in several
Pivot Tables.

Would it be better to Update the data in Excel without doing this step
in Access? If so, how would I perform the update in Excel? Macro's!

Thanks again for the help, Rod.

Daniel

 

[Rod Plastow]

Daniel,

It's gone very quiet! I hope I haven't put you off in any way. Just to
demonstarte how straightforward it is 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 to your template
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

 

[Daniel Lee]

Daniel,

It's gone very quiet! I hope I haven't put you off in any way. Just to
demonstarte how straightforward it is 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 to your
template 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

Thanks again Rod,

I will try this on Tuesday and let you know how it went.


Daniel

 

[Daniel Lee]

Rod,

The code works, but when the Excel workbook opens it appends the filname with
a "1", in the Excel Title bar it comes up "filename1" without the .xls
extension.

I replaced your code "C:\My Documents\MyTemplate.xls" with my path and file
"\\server...\folder...\folder...\filename.xls".

What am I doing wrong?

Thanks again, Daniel