Using Excel GoalSeek on Access Form

  • Thread starter Thread starter Clif McIrvin
  • Start date Start date
C

Clif McIrvin

XP Pro SP2, Access 2003 SP3.

I'm looking to convert an Excel worksheet into an Access form.

I have a strategy in mind for everything the worksheet does, except for
the GoalSeek code behind one of the cells.

Does anyone have experience with doing this sort of thing?

I can supply additional details, including my VBA macro in the worksheet
if that will help.
 
Clif McIrvin said:
XP Pro SP2, Access 2003 SP3.

I'm looking to convert an Excel worksheet into an Access form.

I have a strategy in mind for everything the worksheet does, except for
the GoalSeek code behind one of the cells.

Does anyone have experience with doing this sort of thing?

I can supply additional details, including my VBA macro in the worksheet
if that will help.
Click to expand...
 
I know there is no feature in the Office suite for converting an Excel
worksheet into an Access form; for an Excel worksheet which has data in
columns and rows, you can import or link the data from Access. (That will
not, of course, work if you have code or formulas behind many cells on the
worksheet.)

Because Excel is COM-compatible, if you know the object model and can
accomplish what you want to do with Excel itself, you can automate (most or
all) Excel functionality from Access, so you may be able to accomplish your
purpose by performing the function via code from Access.

Access itself, however, does not have, nor allow you to directly use,
Excel's GoalSeek.

Larry Linson
Microsoft Office Access MVP
 
Hi Cliff,
Access itself, however, does not have, nor allow you to directly use,
Excel's GoalSeek.
Click to expand...

This article may be useful to you:

How to call Excel functions from within Access
http://support.microsoft.com/kb/198571

I imagine that the Goalseek function would likely need an array of data
passed. That can be a little tricky when the array is not hard-coded values,
versus the examples shown in the above KB article, but still do-able.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________
 
Tom, Larry - Thank you!

I am familiar with passing paramater arrays to Excel functions from
Access .. for example, to the MIN, MAX and AVERAGE worksheet functions,
and the use of wrapper functions so they can be called from queries.

On more careful examination, I mis-spoke ... GoalSeek is a method, not a
function, that depends on worksheet functions, and unlike worksheet
functions, does not appear to have provision for accepting a parameter
array.

It appears that I have two (visible) options:
a) automate an excel worksheet populated with the necessary functions
for the goal seek to do its thing, or
b) write situation specific code for my form to emulate the goal seek.

I've got time to wait and see if anyone else has been down this road and
responds here before I need to craft my solution.

Thanks again for your suggestions.

...
Clif
 
Back
Top