Link Access 2002 - Excel

  • Thread starter Thread starter Stanislav
  • Start date Start date
S

Stanislav

Hi to all,
I am new in Access and I need to know, how to create link
between text box in Access Form and Excel cell, so that
this cell is overwritten after each update of text box.

Thanks for helping me.
 
Hi Stanislav,

There's no really simple and reliable way of doing this. One approach is
to use Automation. It would require VBA code in the AfterUpdate event
procedure of the textbox to
-launch Excel (or get hold of it if it is already running)
-open the workbook
-update the cell
-save and close the workbook.

That is straightforward using the techniques demonstrated in the links
below. But it will not work 100% of the time: for instance, if the
workbook is already open it may not be possible to update the cell or to
guarantee that the change will be saved.

An alternative approach (probably a better one, in my opinion) would be
to store the value in the textbox in an Access table, and then use a
macro in Excel to fetch the value from the table (again using
Automation) when it is needed.

This is a useful article giving the basics of controlling Excel from
Access:
http://www.mvps.org/access/modules/mdl0006.htm

Q123859 ACC: Sample OLE Automation for MS Word and MS Excel
http://support.microsoft.com/?id=123859

ACC2000: Using Automation to Create and Manipulate an Excel Workbook
(Q210148) http://support.microsoft.com/?id=210148

ACC: Using Automation to Create and Manipulate an Excel Workbook
(Q142476)
http://support.microsoft.com/?id=142476

Using Microsoft Access as an Automation Server
http://support.microsoft.com/?id=147816
http://support.microsoft.com/?id=210111 (Access 2000 and later)

Also, search www.mvps.org/access
 
Back
Top