473,696 Members | 1,675 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Excel and Access

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
12 3801
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!

Aug 10 '07 #2

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

Aug 12 '07 #6
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


Aug 12 '07 #7
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

Aug 12 '07 #8
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
Aug 12 '07 #9
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

Aug 12 '07 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

8
3366
by: mytfein | last post by:
Hi Everyone, Background: Another department intends to ftp a .txt file from the mainframe, for me to process. The objective is to write a vb script that would be scheduled to run daily to process this .txt file. Goal: I am working on a vba script to:
3
25052
by: Mads Petersen | last post by:
I'm stuck in this code. Hope you can and will help me. I launch it from excel. I have made the following code work, but not as i whant. I need the ranges to be working with something like xlDown. eg. this only transferes the first record in the area. ..Fields("Uge").Value = ws.Range("A98").Value Sub SelectMaster()
3
3329
by: hari krishna | last post by:
hi, I am generating excel reports through vb.Net. After creating excel.application and the report is generated, each report leaves Excel in memory. I can see them in task manager in Process tab (as EXCEL). So the memory has been taken by excel objects and memory is being full. i want to delete or kill this objects which are in memory. i wrote the code as 'myexcel.quit()' , myexcel=nothing. but still it is in memory. pls tell me how to...
9
4429
by: hari krishna | last post by:
hi, I want to send the data from dataset information to Excel through ASP.Net. there will be no XL installed on web server. web server is win 2000 server machine. I am using visual basic code in asp.net. The Xl sheet should not be opened in the browser. All the information from dataset(datatable,datarows) should be sent to XL and the file name should be given dynamically through program and the xl file should be saved dynamically through...
12
2419
by: D. Shane Fowlkes | last post by:
This most likely belongs in another forum but I thought I'd start here. I have a COM Object written in VB6. The DLL will access MS Excel and use it's Object Library to write a customized report and saves it to a folder. The DLL even writes to a log for each step it takes so we can troubleshoot the problems (if any). This works fine on one machine but not another. The folder the DLL is trying to write to has full permissions assigned...
1
5733
by: garry.oxnard | last post by:
Can anyone help me to solve a problem which involves switching from Access to Excel (then back to Access) programatically please? I have an Excel template which, on open, also opens an Access database - containing a list of addresses. Sequence of events is = (1) Excel template opens in its default XXX.xls filename. (2) Code runs to save the spreadsheet as XXX.xls. (3) User clicks a button to open an Access database containing an
0
14418
by: Grip | last post by:
Hi, I have gone throught the group and Microsoft's online help and have seen many suggestions but I am still seeking clarity: 1. I have an excel spreadsheet. Column A contains text that may be greater than 255 characters. 2. I have an access database. I link (not import) to the contents of the excel spreadsheet. In the design view in access, Column A has the data type "memo".
3
7173
by: rlntemp-gng | last post by:
RE: Access 2003/Excel 2003 Problem: After I close the Access application completely, I go out to the Task Manager and there is an Excel.exe object still sitting out there. My Access application creates an Excel file (MyTestFile.XLS) just fine per the code submitted here. This code closes the Excel file created and close the Excel application, leaving the Access app open. Then I close Access. After Access is closed, I go to the task...
0
18783
NeoPa
by: NeoPa | last post by:
Many of us have noticed that there are some very useful functions available to you when using Excel, but these same functions are not available, as standard, in Access. A particular issue I had recently (and I know others have come across this recently too) is rounding up and down. I know the Excel ROUNDUP() function rounds away from zero rather than upwards specifically, but is still useful in most circumstances. To allow an Access...
16
5179
by: Phil Stanton | last post by:
I have a form with a button which is supposed to open an Excel file (With lots of Macros /VBA) in it. The Excel file gets it's data from the Access program Here is the code Private Sub Storage_Click() On Error GoTo Err_Storage_Click
0
8658
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9142
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
8847
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7697
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6512
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
4352
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4606
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3027
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2310
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.