Excel linked tables in Access 2000

[Bill LaVia]

Is it possible to print the formula in an excel linked
table (e.g. b$1 / c$1) from the linked table in access so
that I can show the formula used from an Access report.

 

[John Nurick]

Hi Bill,

I'm pretty sure it isn't possible to do this with a linked table: Access
just links the values, not the formulae (and in fact it sometimes fails
when cells contain formulas and not just plain values).

But you can use Automation to access the .Formula property of individual
Excel cells, so it's possible to write code that will collect all the
formulas and put them into a temporary table that can feed your report.
If you're not familiar with automating Excel, the following articles
give the basics.

Sample Excel automation
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

ACC2000: How to Use Automation to Fill a List Box (Q210145)
http://support.microsoft.com/?id=210145
This article shows you how to use Automation to populate a list box
with values from a Microsoft Excel worksheet



Also, search www.mvps.org/access