adding fully fuctional spreadsheet to a form

[Guest]

Is there a way of adding say excel to a form - which would have the same
functions availabilty as the normal excel application.

I wanted to avoid the user changing from one application to another i.e.
access to excel and back to access.

I have searched all the web sites, but cannot find anything apart from this
, but do not know hoe implement it.
http://www.vbdotnetheaven.com/Code/Jul2003/2124.asp

Thanks
Danny

 

[Guest]

Danny,

The site you posted shows how to create an Excel Spreadsheet in VB. That is
different from what you are trying to do. It would be helpful if you would
describe the functionality you need so we can help with a good approach.

 

[Guest]

I use excel to facilitate preparing financial statements ,the data comes from
Access 2002. Excel is well suited for the need to add/or change minor
formatting and to add sub totals in the right place. The reporting function
of access is too restrictive. I have searched for other interactive drill
down reporting programs that could be used to place on top of forms within
access but have not found anything to date.

Any way I wanted the spreadsheet function to be able to pull in data from
Access and prepare the financial statements by using other worksheets on the
same file or by pulling the amounts on a predefined template on the worksheet
.. I would also like to have any work done the the spreadsheet to be saved on
the closure of the active form.

I hope the above helps.
Thanks for your help.
Regards
Danny.

 

[Guest]

It can be done, but it is tricky and takes a lot of work. I suggest you
build an Excel template to use and format it the way you need it. Then, you
will have to open it from Access. Not with the Shell function, but by
establishing an instance of Excel. Then you will have to move the data from
your table or query to the spreadsheet. Check out the CopyRecordsetFrom
command.

Then, before you close the instance of Excel, you will have to read the data
out of the spreadsheet. There are at least a couple of ways to do this.

Without knowing the details, and because of the complexity of the issue, I
can't really give you a detailed answer here. You might try "VBA for
Dummies". It has some good basics on coding for Excel.

 

[Guest]

Sounds too complicated for me.-as an alternative would this sort of thing be
any good

http://www.mathworks.com/matlabcentral/fileexchange/loadFile.do?objectId=6889

Danny

 

[Guest]

I have not seen this before, but I do know that products like this exist. It
would be worthwhile to download it and try it out.

 

[PC Datasheet]

Sounds too complicated for me.-as an alternative would this sort of thing be
any good

http://www.mathworks.com/matlabcentral/fileexchange/loadFile.do?objectId=688
9

Danny

 

[PC Datasheet]

Danny,

Send me an email and I will tell you about an alternative.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com

 

[Guest]

I have a question! I want to link a fully functional spreadsheet with
formulas with my access database. I linked them however when in access: one
column that does not contain formulas is blank...also another column with the
following formula =IF(AA4<0,"$0.00",IF(AA4>0,AA4)) is showing up with the
following: #Num!. Also is there a way to use access only and have the
formulas updated? The reason I linked the 2 is all the information was in
excel and I need the formulas and my company wants the info in access to run
reports and to store the info. Am I going about this in the right way?
Help!!!

 

[Guest]

I have a question! I want to link a fully functional spreadsheet with
formulas with my access database. I linked them however when in access: one
column that does not contain formulas is blank...also another column with the
following formula =IF(AA4<0,"$0.00",IF(AA4>0,AA4)) is showing up with the
following: #Num!. Also is there a way to use access only and have the
formulas updated? The reason I linked the 2 is all the information was in
excel and I need the formulas and my company wants the info in access to run
reports and to store the info. Am I going about this in the right way?

 

[PC Datasheet]

The answer to your question comes from the difference between Excel and
Access. Excel is designed to be highly computational and to give the user a
"snapshot" of given data at a specific point in time. Excel has limited data
storage capability and reports for the most part must be manually created.
Access is a database and is designed to store data from multiple points in
time. Access is designed to easily pull out selected data and display it on
screen or create a hardcopy report; all automatically. To emphasize, Access
has extremely capable reporting facilities. Access has strong computational
power but lacks some functions found in Excel. However these functions in
Excel are only moderately used by the average user.

So are you going about this in the right way? The answer is probably not.
Most likely the computations you need can be done in Access and therefore
there is no need to link to Excel. I can make both ways work for you. If you
would like my help, contact me at my email address below.

 

[Guest]

It's hard to beleive that with all the MV and programmers that a solution is
not out there, there must be others out there that need this solution or is
it that it's not in MS's interest to add this to the functionality of access !

 

[Guest]

Can someone help us on this?

It's hard to beleive that with all the MV and programmers that a solution is
not out there, there must be others out there that need this solution or is
it that it's not in MS's interest to add this to the functionality of access !

 

[Rick Brandt]

Can someone help us on this?

As far as I know it is not possible. That is why you are not getting any
suggestions on how to do it.

 

[Guest]

Thanks for the feedback, Rick

regards
Danny

As far as I know it is not possible. That is why you are not getting any
suggestions on how to do it.

 

[Tim Ferguson]

It's hard to beleive that with all the MV and programmers that a
solution is not out there, there must be others out there that need
this solution or is it that it's not in MS's interest to add this to
the functionality of access !

My hammer is not particularly good at making toast either, but I don't
plan to complain to Black and Decker about it. The jobs that spreadsheets
and databases do are just about as different...

First of all, Access is simply just not going to make any sense of
something that depends on row numbers. Records in a database are just
records -- there is no implicit sorting or recordnumbers. Roughly the
same applies to columns: although it is possible to call a field "AA4" it
would be a really stupid thing to do. Furthermore, there is no sense in
which it would be helpful to define one field purely in terms of the
values in (an)other field(s). Sorry: this is primary school R theory and
if you don't understand it, then you need to do some basic reading.

See the other reply upthread from the anonymous at earthlink.

There are plenty of things to moan at Microsoft about -- to many minds
there is already to much Excel-like crossover going on in Access already
-- but this has nothing to do with MS's interests, being more about not
making a dog's dinner out of a pretty good desktop R database system.

Best wishes


Tim F

 

[Guest]

Hi Tim

I oviously did not make my point clear enough.

Which is that , if you can add a spreedsheet version 10.0 (as at activx
control) on a form. Why do you loose any work that you do on that worksheet
when the form (in my case a form on a tabcontrol) is closed.
Yes I know you loose any work you do on that control - what I wanted to know
was can this work be saved, or was there a way of adding the same activx
control provided by another vendor.

And yes I do know the diff between spreadsheet and a databse - what I am
looking for is the flexability of working with in one application - so that
the end user does not have to switch between access and MS Excell.

Thanks your your comments

 

[Tim Ferguson]

Which is that , if you can add a spreedsheet version 10.0 (as at
activx control) on a form. Why do you loose any work that you do on
that worksheet when the form (in my case a form on a tabcontrol) is
closed.

I doubt that its being embedded on a form makes any difference: if you
close an application (and therefore all its documents) without saving first
then all changes will be lost.

I am not clear from any of the above whether you are opening a disk file or
an OLE object contained in a database table. OLE objects seem to be better
at saving themselves unasked (if this is better -- it seems to me that it
is just as good a way of destroying information as the opposite).

Perhaps your users just need to get a bit better and the ctrl-S..?

Hope it helps


Tim F

 

[Margaret Bartley]

You can read and write to Excel from Access, and vice versa. This is called
Office
Automation, and it is a rich development environment, and there are many
things you can do.

A good place to start would be
http://msdn.microsoft.com/library/d...v_wrcore/html/wrgrfexcelapplicationobject.asp
which talks about the different objects, and how to manipulate them. If
this is a new
area for you, it will take some getting used to. But once you get some
vocabulary,
you can search the web for the specific code fragments you need.

If you notice, the above URL takes you to one chapter in a book called
"Programming with
Office". I encourage you to look at the whole picture.

Conversely, another approach I used before I was as familiar with the Excel
object model
is that I built a worktable in Access, and used code to generate the
subtotal rows, and then
just used that report table as the basis of my report.

In other words, I'd have an indicator field in my Access report table with
values like D1 for details, d2 for first level subtotals, P1 for
percentages, etc, and my report would look at each row of the table,
determine from the indicator what kind of row it was, and make the
appropriate formatting changes for the displayed value. It was tedious, but
it saved me from having to learn the difference between late binding and
early binding, ranges, etc.