By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,742 Members | 1,327 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,742 IT Pros & Developers. It's quick & easy.

Excel and Access

P: n/a
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!

Aug 10 '07 #1
Share this Question
Share on Google+
12 Replies


P: n/a
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******@pcdatasheet.com

"slinky" <ca***************@yahoo.comwrote in message
news:11**********************@q4g2000prc.googlegro ups.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!

Aug 10 '07 #2

P: n/a

"Steve" <so***@private.emailaddressschreef in bericht news:13*************@corp.supernews.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******@pcdatasheet.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...(although 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 'StopThisAdvertising'.
Newbees will still see this warning-message.

ArnoR
Aug 10 '07 #3

P: n/a
On Fri, 10 Aug 2007 12:24:20 -0700, slinky
<ca***************@yahoo.comwrote:

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 programmatically.

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!
Aug 11 '07 #4

P: n/a
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
Aug 11 '07 #5

P: n/a
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******@pcdatasheet.com


"Marshall Barton" <ma*********@wowway.comwrote in message
news:c1********************************@4ax.com...
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

Aug 12 '07 #6

P: n/a
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.emailaddresswrote in message
news:13*************@corp.supernews.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******@pcdatasheet.com


"Marshall Barton" <ma*********@wowway.comwrote in message
news:c1********************************@4ax.com...
>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


Aug 12 '07 #7

P: n/a
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.comwrote in message
news:46***********************@news.corp.moreusene t.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

Aug 12 '07 #8

P: n/a
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
automatically 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
Aug 12 '07 #9

P: n/a
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******@pcdatasheet.com

"Marshall Barton" <ma*********@wowway.comwrote in message
news:k2********************************@4ax.com...
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
automatically 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

Aug 12 '07 #10

P: n/a
Steve wrote:
>I have SP-2 for OfficeXP and at least two service packs for Office2003. What
are the most current updates?

See Len's posts with links.

--
Marsh
Aug 12 '07 #11

P: n/a
"Steve" <so***@private.emailaddresswrote in message
news:13*************@corp.supernews.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.
That goes to show that you don't have Office 2k3 *SP2* installed.

Aug 13 '07 #12

P: n/a
"Steve" <so***@private.emailaddresswrote in message
news:13*************@corp.supernews.com...
>I have SP-2 for OfficeXP and at least two service packs for Office2003.
What are the most current updates?
Some resource for Access and Excel you are. This issue has been common
knowledge for years.

Aug 13 '07 #13

This discussion thread is closed

Replies have been disabled for this discussion.