Excel and Access

[slinky]

Can an Excel spreadsheet or a section of one be embedded into an
Access form and serve as a subform from which other parts of the
Access form can get data? Thanks!

[Steve]

If the spreadsheet is tabular, you can link to the spreadsheet using File -
Get External Data - Link. The spreadsheet will appear in your database as a
table. The link will be dynamic meaning changes to the linked table in
Access will automatically change the data in the spreadsheet and vice-versa.
Now that you have the linked table, you can create a subform where the
recordsource is the linked table.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
re******@pcdata sheet.com

"slinky" <ca************ ***@yahoo.comwr ote in message
news:11******** **************@ q4g2000prc.goog legroups.com...

Can an Excel spreadsheet or a section of one be embedded into an
Access form and serve as a subform from which other parts of the
Access form can get data? Thanks!

[StopThisAdvertising]

"Steve" <so***@private. emailaddresssch reef in bericht news:13******** *****@corp.supe rnews.com...

If the spreadsheet is tabular, you can link to the spreadsheet using File -
Get External Data - Link. The spreadsheet will appear in your database as a
table. The link will be dynamic meaning changes to the linked table in
Access will automatically change the data in the spreadsheet and vice-versa.
Now that you have the linked table, you can create a subform where the
recordsource is the linked table.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
re******@pcdata sheet.com

--
This is to inform 'newbees' here about PCD' Steve:
http://home.tiscali.nl/arracom/whoissteve.html
Until now 3275+ pageloads, 2200+ first-time visitors (these figures are rapidly increasing)

To PCD' Steve: (this is also to inform those who do not understand...)
This is *not* about the sigline...(alth ough you are far away from a 'resource' status).
But we will simply continue to hunt down *each and every* of your posts.

Why???
Because you are the ONLY person here who continues to advertise in the groups.

It is not relevant whether you advertised in *this* particular post or not...
==We want you to know that these groups are *not* your private hunting grounds!

For those who don't like too see all these messages:
==Simply killfile 'StopThisAdvert ising'.
Newbees will still see this warning-message.

ArnoR

[Tom van Stiphout]

On Fri, 10 Aug 2007 12:24:20 -0700, slinky
<ca************ ***@yahoo.comwr ote:

Yes, that is technically possible (it's called embedding an OLE
object; just drop an Excel object on a form in design view), but it is
practically rather complicated especially if you want to manipulate
this object programmaticall y.

Much better is to use the data from the Excel file as a Linked Table,
and use a standard Access subform to display the data.

-Tom.

>Can an Excel spreadsheet or a section of one be embedded into an
Access form and serve as a subform from which other parts of the
Access form can get data? Thanks!

[Marshall Barton]

Steve wrote:

>If the spreadsheet is tabular, you can link to the spreadsheet using File -
Get External Data - Link. The spreadsheet will appear in your database as a
table. The link will be dynamic meaning changes to the linked table in
Access will automatically change the data in the spreadsheet and vice-versa.
Now that you have the linked table, you can create a subform where the
recordsource is the linked table.

I think you are wrong about the link being two way dynamic.
A linked table to an Excel range is NOT updatable and it
has been this way for the last four years or so. The SP
that disabled this capability is so thorough that
Automation/OLE is the only(?) way to update Excel data from
Access.

--
Marsh

[Steve]

I created a dynamic link in an Access97 project for a customer some time ago
and it is still working. That's the only time I used that feature. For
future reference, I decide to test what you said. I have Office97, OfficeXP
and Office2003 installed. I created an Excel flle in Excel97, another file
in ExcelXP and another file in Excel2003. I then went to Access97 and linked
to the Excel97 file, AccessXP and linked to the ExcelXP file and Access2003
and linked to Excel2003. I found all three links to be two way dybanic
links. In all three I found changes to the linked table in Access will
automatically change the data in the spreadsheet and vice-versa.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
re******@pcdata sheet.com


"Marshall Barton" <ma*********@wo wway.comwrote in message
news:c1******** *************** *********@4ax.c om...

Steve wrote:

>>If the spreadsheet is tabular, you can link to the spreadsheet using
File -
Get External Data - Link. The spreadsheet will appear in your database as
a
table. The link will be dynamic meaning changes to the linked table in
Access will automatically change the data in the spreadsheet and
vice-versa.
Now that you have the linked table, you can create a subform where the
recordsourc e is the linked table.

I think you are wrong about the link being two way dynamic.
A linked table to an Excel range is NOT updatable and it
has been this way for the last four years or so. The SP
that disabled this capability is so thorough that
Automation/OLE is the only(?) way to update Excel data from
Access.

--
Marsh

[Len Robichaud]

See KB Article:
You cannot change, add, or delete data in tables that are linked to an Excel
workbook in Office Access 2007, Office Access 2003, or Access 2002
http://support.microsoft.com/kb/904953

Once you Update to SP2 the ability to directly update linked Excel tables
goes away unless you use Automation.

Len Robichaud

"Steve" <so***@private. emailaddresswro te in message
news:13******** *****@corp.supe rnews.com...

>I created a dynamic link in an Access97 project for a customer some time
ago and it is still working. That's the only time I used that feature. For
future reference, I decide to test what you said. I have Office97, OfficeXP
and Office2003 installed. I created an Excel flle in Excel97, another file
in ExcelXP and another file in Excel2003. I then went to Access97 and
linked to the Excel97 file, AccessXP and linked to the ExcelXP file and
Access2003 and linked to Excel2003. I found all three links to be two way
dybanic links. In all three I found changes to the linked table in Access
will automatically change the data in the spreadsheet and vice-versa.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
re******@pcdata sheet.com


"Marshall Barton" <ma*********@wo wway.comwrote in message
news:c1******** *************** *********@4ax.c om...

>Steve wrote:

>>>If the spreadsheet is tabular, you can link to the spreadsheet using
File -
Get External Data - Link. The spreadsheet will appear in your database as
a
table. The link will be dynamic meaning changes to the linked table in
Access will automatically change the data in the spreadsheet and
vice-versa.
Now that you have the linked table, you can create a subform where the
recordsour ce is the linked table.

I think you are wrong about the link being two way dynamic.
A linked table to an Excel range is NOT updatable and it
has been this way for the last four years or so. The SP
that disabled this capability is so thorough that
Automation/OLE is the only(?) way to update Excel data from
Access.

--
Marsh

[Len Robichaud]

MORE INFORMATION
Because of legal issues, Microsoft has disabled the functionality in Access
2003 and in Access 2002 that let users change the data in linked tables that
point to a range in an Excel workbook. However, when you make changes
directly in the Excel workbook, the changes appear in the linked table in
Access.

"Len Robichaud" <le***********@ rqwproserv.comw rote in message
news:46******** *************** @news.corp.more usenet.net...

See KB Article:
You cannot change, add, or delete data in tables that are linked to an Excel
workbook in Office Access 2007, Office Access 2003, or Access 2002
http://support.microsoft.com/kb/904953

Once you Update to SP2 the ability to directly update linked Excel tables
goes away unless you use Automation.

Len Robichaud

[Marshall Barton]

Steve wrote:

>I created a dynamic link in an Access97 project for a customer some time ago
and it is still working. That's the only time I used that feature. For
future reference, I decide to test what you said. I have Office97, OfficeXP
and Office2003 installed. I created an Excel flle in Excel97, another file
in ExcelXP and another file in Excel2003. I then went to Access97 and linked
to the Excel97 file, AccessXP and linked to the ExcelXP file and Access2003
and linked to Excel2003. I found all three links to be two way dybanic
links. In all three I found changes to the linked table in Access will
automaticall y change the data in the spreadsheet and vice-versa.

That experiment just demonstrates that your Office updates
for XP and 2003 are years out of date.

--
Marsh

[Steve]

I have SP-2 for OfficeXP and at least two service packs for Office2003. What
are the most current updates?

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
re******@pcdata sheet.com

"Marshall Barton" <ma*********@wo wway.comwrote in message
news:k2******** *************** *********@4ax.c om...

Steve wrote:

>>I created a dynamic link in an Access97 project for a customer some time
ago
and it is still working. That's the only time I used that feature. For
future reference, I decide to test what you said. I have Office97,
OfficeXP
and Office2003 installed. I created an Excel flle in Excel97, another file
in ExcelXP and another file in Excel2003. I then went to Access97 and
linked
to the Excel97 file, AccessXP and linked to the ExcelXP file and
Access2003
and linked to Excel2003. I found all three links to be two way dybanic
links. In all three I found changes to the linked table in Access will
automatical ly change the data in the spreadsheet and vice-versa.

That experiment just demonstrates that your Office updates
for XP and 2003 are years out of date.

--
Marsh