Embedded Spreadsheet in an Access form. How to save data.

[Colleyville Alan]

I have been working on an Access app that takes info from a file and writes
it to a spreadsheet on a form, simultaneously saving the spreadsheet to
Excel. I got the idea that the same concept could work in reverse, i.e. we
have a cost model written in Excel that calculates the profitability of
customer accounts based on several inputs and they need to be updated at
least once per year. These cost models sit on lots of people's hard drives,
but there is no central repository of information from it or even a log to
see if the cost models have been run recently.

I would like the user to be able to open the model from within a form,
update it, and when they save it, have the app write key information to a
table (maybe customer name and id, total cost, total revenue, margin and
last date the model was run). That information would be linked to other
customer info as part of and MIS system.

Is there an easy way to do this? Is it simply a matter of storing specific
cell references in memory and then writing these to a table or is there a
better way of handling this?

[DCM Fan]

Re: Embedded Spreadsheet in an Access form. How to save data.

<<and when they save it, have the app write key information to a
table (maybe customer name and id, total cost, total revenue, margin and
last date the model was run)>>

Then you'll have to write DAO or ADO code in the Excel file that runs
appropriate SQL statements against the Access database. You'll have to pray
that the Access database is always located i nthe same folder, too, or at least
provide a mechanism for the user to "search" for the Access database if it's
not found in the last location (and save the location on a hidden sheet)

This is all doable, but you're heading down a rough road. Instead, if I were
you, I'd do my darnedest to replace the Excel application completely with
Access.

What exactly is Excel giving you that Access can't?

[Colleyville Alan]

Re: Embedded Spreadsheet in an Access form. How to save data.

"DCM Fan" <dcmfan@aol.com SPNOAM> wrote in message
news:2004050102 3807.03927.0000 0643@mb-m01.aol.com...[color=blue]
> <<and when they save it, have the app write key information to a
> table (maybe customer name and id, total cost, total revenue, margin and
> last date the model was run)>>
>
> Then you'll have to write DAO or ADO code in the Excel file that runs
> appropriate SQL statements against the Access database. You'll have to[/color]
pray[color=blue]
> that the Access database is always located i nthe same folder, too, or at[/color]
least[color=blue]
> provide a mechanism for the user to "search" for the Access database if[/color]
it's[color=blue]
> not found in the last location (and save the location on a hidden sheet)[/color]

Well I do not see that as a problem since I planned to use an embedded
spreadsheet (FormulaOne ActiveX) on a form *within* Access.
[color=blue]
> This is all doable, but you're heading down a rough road. Instead, if I[/color]
were[color=blue]
> you, I'd do my darnedest to replace the Excel application completely with
> Access.
>
> What exactly is Excel giving you that Access can't?[/color]
The model is already in existence and is fully debugged plus users are
familiar with the interface. I simply want to be able to allow them to do
pretty much what they have always done, but post the information to a
database upon completion so the managers are able to query a database to see
if the models are up-to-date. If an account was $5mm one year but $12mm the
next, it might need to be re-priced. With this approach, I could compare
current asset levels with those of a prior time frame and determine whether
the cost model needs to be updated.

[DCM Fan]

Re: Embedded Spreadsheet in an Access form. How to save data.

<<Well I do not see that as a problem since I planned to use an embedded
spreadsheet (FormulaOne ActiveX) on a form *within* Access.>>

Well, I've never used that 3rd party ActiveX control, so maybe it inherently
"knows" the database path of an embedded object...good for you if it does.

<< I simply want to be able to allow them to do
pretty much what they have always done, but post the information to a
database upon completion so the managers are able to query a database to see
if the models are up-to-date. If an account was $5mm one year but $12mm the
next, it might need to be re-priced. With this approach, I could compare
current asset levels with those of a prior time frame and determine whether
the cost model needs to be updated.>>

Then why do you need to embed the Excel files? Can't the Excel files live their
own lives, doing their own things, with VBA code that populates external
databases as needed?

I'm not seeing the value-add of having it embedded yert, other than
gee-whiz-bang reasons, and the fact that you may have already paid for a
control you want to use. All you're doing is bloating the Access database, and
increasing its risk of failure. (Although Access has functionality that you
might think is cool, it's not really what Access is for...)

I read the 2-page datasheet at xlsoft.com, and they do not even mention if the
Formula One compnents allows embedded Excel objects that contain VBA! You'd
think that would be front-and-center.

Than again, you say these model already exist (as normally-created Excel file,
I assume), so I don't even understand why you need Formula One ActiveX at
all...

At any rate, no embedded object that I know of "knows" that it's embedded, and
"knows" what its parent object is, when it's activated. So when I imagine your
embedded Excel object on an Access form, and that Excel object has a macro
button in it, and you "open" the object (you'll have to open the Excel object
to change anything in it), the code behind the Excel button has no idea that it
was launched from an Access form....so it won't know WHERE the database is!

Now, if you're using a BOUND object frame, and LINKING to an Excel file, at
least maybe you could add code behind the Excel button that looks for the
database of a certain name in the same folder as the Excel file...that would
work.

But I'm still not sold on this whole idea. If you work it out, I'd like to see
your defintion of "success."

[Colleyville Alan]

Re: Embedded Spreadsheet in an Access form. How to save data.

"DCM Fan" <dcmfan@aol.com SPNOAM> wrote in message
news:2004050119 0223.25458.0000 0700@mb-m29.aol.com...
[color=blue]
> Then why do you need to embed the Excel files? Can't the Excel files live[/color]
their[color=blue]
> own lives, doing their own things, with VBA code that populates external
> databases as needed?[/color]

Well, this nextpart of your reply changes my thinking:[color=blue]
> At any rate, no embedded object that I know of "knows" that it's embedded,[/color]
and[color=blue]
> "knows" what its parent object is, when it's activated.[/color]

I was assuming that with the object embedded, it would be aware of the
underlying database (I'm obviously not an ActiveX expert). I have done some
work with automation, but had so many problems with "ghost' Excel objects in
memory, that I wanted to avoid using Excel to the extent possible.

snip
[color=blue]
> Than again, you say these model already exist (as normally-created Excel[/color]
file,[color=blue]
> I assume), so I don't even understand why you need Formula One ActiveX at
> all...[/color]

Basically, I wanted my application to be run from within Access, but using a
spreadsheet since that is what is familiar to the users. Running the Excel
models as stand-alone apps is what is done now. I want to launch it from
Access and capture data back whenever the spreadsheet is saved. Obviously,
I do not have a good understanding of how this might occur. But the idea is
that if they want to run the Excel cost models, they must initiate the
request from within Access (maybe VBA and automation from a command button).
When they finish and save, Access gets the current date and key data. Then
I have something useful in Access that management can use.
[color=blue]
> But I'm still not sold on this whole idea. If you work it out, I'd like to[/color]
see[color=blue]
> your defintion of "success."[/color]

Developing the concept is one thing, but how I will handle the
implementation is still a mystery to me.

[DCM Fan]

Re: Embedded Spreadsheet in an Access form. How to save data.

<<But the idea is that if they want to run the Excel cost models, they must
initiate the request from within Access (maybe VBA and automation from a
command button). When they finish and save, Access gets the current date and
key data. Then I have something useful in Access that management can use.>>

Then I recommend just storing the paths to the Excel docs in the Access
database, with a "Launch" button that will open them via Automation. Each of
the Excel docs will have to be re-engineered with a command button of their
own. (Although if you were really into it, you could use Automation to create a
command button on the fly, and attached code to it too!)

During the Automation whilst opening the workbook, you could also populate a
hidden worksheet with the path to the database that just launched the
automation...no w you know where it is, and it can change at will.

I would also set a global variable at the time of Open, too, so that you know
that the book was opened by Automation....t hat way, you can prevent the user
from exiting Excel without clicking your button to get back to Access....and
when they click the button, run some DAO SQL statements to update the target
database (which you'll know b/c you stored the path on the hidden worksheet
when you opened the book)

There ya go...just using Automation...NO EMBEDDED OBJECTS!!!

[Colleyville Alan]

Re: Embedded Spreadsheet in an Access form. How to save data.

"DCM Fan" <dcmfan@aol.com SPNOAM> wrote in message
news:2004050121 3431.17672.0000 0339@mb-m23.aol.com...[color=blue]
> <<But the idea is that if they want to run the Excel cost models, they[/color]
must[color=blue]
> initiate the request from within Access (maybe VBA and automation from a
> command button). When they finish and save, Access gets the current date[/color]
and[color=blue]
> key data. Then I have something useful in Access that management can[/color]
use.>>[color=blue]
>
> Then I recommend just storing the paths to the Excel docs in the Access
> database, with a "Launch" button that will open them via Automation. Each[/color]
of[color=blue]
> the Excel docs will have to be re-engineered with a command button of[/color]
their[color=blue]
> own. (Although if you were really into it, you could use Automation to[/color]
create a[color=blue]
> command button on the fly, and attached code to it too!)
>
> During the Automation whilst opening the workbook, you could also populate[/color]
a[color=blue]
> hidden worksheet with the path to the database that just launched the
> automation...no w you know where it is, and it can change at will.
>
> I would also set a global variable at the time of Open, too, so that you[/color]
know[color=blue]
> that the book was opened by Automation....t hat way, you can prevent the[/color]
user[color=blue]
> from exiting Excel without clicking your button to get back to[/color]
Access....and[color=blue]
> when they click the button, run some DAO SQL statements to update the[/color]
target[color=blue]
> database (which you'll know b/c you stored the path on the hidden[/color]
worksheet[color=blue]
> when you opened the book)
>
> There ya go...just using Automation...NO EMBEDDED OBJECTS!!![/color]


Thanks. That sounds like a fairly clear approach.

[Stephen Lebans]

Re: Embedded Spreadsheet in an Access form. How to save data.

You can use WIthEvents on the Excel object and sink any of the events
exposed by the Excel Automation interface. I'd guess that the File Save
and File Close methods are exposed. If you search on GoogleGroups I'm
sure you could find some canned code.

--

HTH
Stephen Lebans

http://www.lebans.com

Access Code, Tips and Tricks
Please respond only to the newsgroups so everyone can benefit.


"Colleyvill e Alan" <aecharbonneau@ nospam.comcast. net> wrote in message
news:qvXkc.1066 9$kh4.673935@at tbi_s52...[color=blue]
> "DCM Fan" <dcmfan@aol.com SPNOAM> wrote in message
> news:2004050119 0223.25458.0000 0700@mb-m29.aol.com...
>[color=green]
> > Then why do you need to embed the Excel files? Can't the Excel files[/color][/color]
live[color=blue]
> their[color=green]
> > own lives, doing their own things, with VBA code that populates[/color][/color]
external[color=blue][color=green]
> > databases as needed?[/color]
>
> Well, this nextpart of your reply changes my thinking:[color=green]
> > At any rate, no embedded object that I know of "knows" that it's[/color][/color]
embedded,[color=blue]
> and[color=green]
> > "knows" what its parent object is, when it's activated.[/color]
>
> I was assuming that with the object embedded, it would be aware of the
> underlying database (I'm obviously not an ActiveX expert). I have[/color]
done some[color=blue]
> work with automation, but had so many problems with "ghost' Excel[/color]
objects in[color=blue]
> memory, that I wanted to avoid using Excel to the extent possible.
>
> snip
>[color=green]
> > Than again, you say these model already exist (as normally-created[/color][/color]
Excel[color=blue]
> file,[color=green]
> > I assume), so I don't even understand why you need Formula One[/color][/color]
ActiveX at[color=blue][color=green]
> > all...[/color]
>
> Basically, I wanted my application to be run from within Access, but[/color]
using a[color=blue]
> spreadsheet since that is what is familiar to the users. Running the[/color]
Excel[color=blue]
> models as stand-alone apps is what is done now. I want to launch it[/color]
from[color=blue]
> Access and capture data back whenever the spreadsheet is saved.[/color]
Obviously,[color=blue]
> I do not have a good understanding of how this might occur. But the[/color]
idea is[color=blue]
> that if they want to run the Excel cost models, they must initiate the
> request from within Access (maybe VBA and automation from a command[/color]
button).[color=blue]
> When they finish and save, Access gets the current date and key data.[/color]
Then[color=blue]
> I have something useful in Access that management can use.
>[color=green]
> > But I'm still not sold on this whole idea. If you work it out, I'd[/color][/color]
like to[color=blue]
> see[color=green]
> > your defintion of "success."[/color]
>
> Developing the concept is one thing, but how I will handle the
> implementation is still a mystery to me.
>
>[/color]

[Colleyville Alan]

Re: Embedded Spreadsheet in an Access form. How to save data.

"Stephen Lebans" <ForEmailGotoMy .WebSite.-WWWdotlebansdot com@linvalid.co m>
wrote in message news:3u_kc.3338 1$Np3.1235324@u rsa-nb00s0.nbnet.nb .ca...
[color=blue]
> You can use WIthEvents on the Excel object and sink any of the events
> exposed by the Excel Automation interface. I'd guess that the File Save
> and File Close methods are exposed. If you search on GoogleGroups I'm
> sure you could find some canned code.[/color]

Yep, I've used close and write, though I'm guessing that write is a VB
function not really an Excel method, but it works.