------ Register to get rid of these "In Post" ads! ------


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

------ Register to get rid of these "In Post" ads! ------



"Clif McIrvin" <clare.moe@nevergmail.com.invalid> schreef in bericht
news:evgoepFmKHA.1536@TK2MSFTNGP06.phx.gbl...
> 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
>

------ Register to get rid of these "In Post" ads! ------


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

"Clif McIrvin" <clare.moe@nevergmail.com.invalid> wrote in message
news:evgoepFmKHA.1536@TK2MSFTNGP06.phx.gbl...
> 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
>

------ Register to get rid of these "In Post" ads! ------


Hi Cliff,

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

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/
__________________________________________

"Larry Linson" wrote:

> 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
>
> "Clif McIrvin" <clare.moe@nevergmail.com.invalid> wrote in message
> news:evgoepFmKHA.1536@TK2MSFTNGP06.phx.gbl...
> > 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
> >
>
>
>
> .
>

------ Register to get rid of these "In Post" ads! ------


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

"Tom Wickerath" <AOS168b AT comcast DOT net> wrote in message
news:70F19198-8944-4243-B44A-3F1CD8BA38D0@microsoft.com...
> Hi Cliff,
>
> Larry wrote:
>> Access itself, however, does not have, nor allow you to directly use,
>> Excel's GoalSeek.
>
> 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/
> __________________________________________
>
> "Larry Linson" wrote:
>
>> 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
>>
>> "Clif McIrvin" <clare.moe@nevergmail.com.invalid> wrote in message
>> news:evgoepFmKHA.1536@TK2MSFTNGP06.phx.gbl...
>> > 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
>> >
>>
>>
>>
>> .
>>



--
Clif