Getting Excel function results to Access

[Sajit]

I have functions in an Excel workbook which are either functions made up from
worksheet functions such as the vlookup and match or made up from few lines
of VBA code. These functions evaluate results from an input to it and arrive
at the result by using the above functions. I have read about DDE. How can I
get this done.

Thanks,
--
Sajit
Abu Dhabi

[Klatuu]

DDE is a long dead protocol. It is very slow and unstable.
If you need to work with an Excel xls file from within Access, you should
use Automation.
--
Dave Hargis, Microsoft Access MVP

[Sajit]

I have tried to understand what Automation is about from the different posts
in here. What I could see is that it is a means of opening the Excel
application in windows and executing things as if a user would be using
Excel. What I trying to do is, I am trying to get a value which is the result
of an Excel function. The Excel function will lookup using vlookup and match
into a table of values. This will be needed to be done on a record by record
basis in Access. Do you think this will be possible with Automation. Can you
give me a further lead to it either in here or else where.

[Klatuu]

Is the table of values in an Excel worksheet that you could import into Access?
If you can do a simple TransferSpreadsheet to import the data, you can do
your analysis in Access rather than in Excel.
If you can describe in some detail what you are trying to do, maybe I can
offer some suggestions.

--
Dave Hargis, Microsoft Access MVP

[Sajit]

I have a matrix in Excel, of rows and columns of values. Each of the row and
coumn headers have a key value. The function will be to find the value in the
matrix corresponding to the column and row key values.

For this, the Excel worksheet functions, 'Vlookup' and 'Match' is used.

Match will determine which is the column which has a column header key value
which is next lower to the lookup key value.

The vlookup function will find the row which is the next lower value to the
column look up value, and then read of the value corresponding to the vlookup
row and the column found out by match.

This is what I thought is best handled in Excel. If it were happening each
time there is a call from Access, the Excel file should be able to provide
the value without having to invoke the Excel application and opening the
file. I am not sure whether this is possible.

The Excel file will have many such matrices supplying various values either
as text or numbers.

On the contrary, if the same can be done through Access, that will be even
better. Can you please suggest how?
--
Sajit
Abu Dhabi

[Klatuu]

It could be done in Access, but it more native to Excel. Relational database
data, if correctly normalized, is very different from spreadsheet data.

What is it you want Access to do with this data?

--
Dave Hargis, Microsoft Access MVP

[Sajit]

I am doing a pipe support material summary application. For each code
(support type) there are various sub component assemblies. These sub
components are in Access tables. The material take off input will be main
code. A 1 to many query between the input table and the sub component tables
will get the list of components. In some case the sub components are further
grouped under sub sub components that are parametric (row and columns in the
Excel matrix) depended.

I wish there was a method by which the Access application could have
communicated with the Excel file without apparent intervention of the Excel
application.
--
Sajit
Abu Dhabi