Solved

Embed Excel into an Access tab control page

Posted on 2004-08-22
11
2,594 Views
Last Modified: 2012-05-05
I want to embed (if possible) an Excel worksheet into an Access tab control page.  Perhaps I should not be thinking of Excel in this instance.  You may have another suggestion.  

This grid is to be used for a scheduling item.  Considerations are: 1.) all columns are dated-days and each dated-day column is the same length as all others.  2.) Each entry will take up at least 7 dated-day columns.  3.) A description will be a part of each 7 (or more) column entry - and these descriptions vary in length, taking up 1 or more columns but no more columns than the assigned number of days.  These entries are saved and recalled each time the Access program is closed or run again.

I hope this is clear.  Is it?

If you know how to do this and are going to describe it to me, please remember that I have no clue at all so all the instructions you can give me will be very welcome.  (I have read the previous answers to relatively similar questions.)

Mahalo from Hawaii,
Jack
0
Comment
Question by:ljcor
  • 5
  • 5
11 Comments
 
LVL 26

Expert Comment

by:dannywareham
Comment Utility
Rather than embedding an excel worksheet into a form (which takes up load of space and needs references setting too), build your form to emulate a worksheet.
Create a table using your excel worksheet as a template.
Use text boxes to create each cell on the form and bind these to your table (control source in properties).
You can control what is entered and its format etc, even automate certain functions, by using some very simple code (which we will help you with).

:-)
0
 

Author Comment

by:ljcor
Comment Utility
Hi dannywareham,

I appreciate your suggestion however my opinion is that the design aspects that you suggest would be difficult.  There could easily be a few thousand text boxes in just the first year's time.  Also, I believe the formatting of the text descriptions as described above would result in uneven text of individual words that spanned more than 1 text box.  Excel handles this automatically - and evenly.

I am not really concerned with the hefty aspects of Excel.  I did consider using Excel as the base program rather than Access but I prefer not to do that.  However, that may be a consideration to be examined in more detail.

Thanks again.
Jack
0
 
LVL 12

Expert Comment

by:0tacon
Comment Utility
The easy way of doing what danny suggests is to use the form wizard to create a datasheet/continuous form.

Its the closest thing access has to excel and is pretty similar.

Alternatively, create your worksheet in Excel, then on the databse window in access right click and select 'LINK TABLES' and link to the spreadsheet. Any changes you make to the spreadsheet in excel will be reflected in access, so you could, when you want your spreadsheet view, simply use the code:

Application.followhyperlink..>code as appropriate<

good luck!
0
 
LVL 26

Expert Comment

by:dannywareham
Comment Utility
OK, if you prefer to put Excel straight in...
Save you excel worksheet to a drive/location.
Open a form.
Click (on toolbar)
INSERT > OBJECT > CREATE FROM FILE > BROWSE
Select your file
Check the LINK box
Click OK

Done.
This links directly to your excel spreadsheet (you can create your formulas directly in it).
You'll need a reference - ActiveX Data objects 2.6 or higher.
0
 

Author Comment

by:ljcor
Comment Utility
0tacon & dannywareham,

I offer a great apology for not getting back to this much sooner.  A family matter took precedence over my poor programming skills.

I feel that I need (if possible) to open an Excel worksheet, COMPLETE with the Excel menu, inside my Access program.  Again if possible, I would like to have this open on a Tab control page.  

The entire small system of programs is resident on the Tab control pages.  When it starts, Excel should load on the tab page.  When the system closes, Excel should close and save the changes that were made to the worksheet.

Again, I offer a warm Mahalo from Hawaii,
Jack
0
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 
LVL 26

Expert Comment

by:dannywareham
Comment Utility
Welcome back, Jack.

My previous post (08/22/2004) shows how to link to an Excel worksheet.
It does require the worksheet to be saved external to the database.
I suggest putting this on a shared server.
The OLE object will update the spreadsheet when Access automatically saves (on close).
0
 
LVL 26

Expert Comment

by:dannywareham
Comment Utility
I must warn you that OLE objects do bloat your database (in terms of size).
It may be better to link it as a table as Otacon suggested.
We can always make your continous form look exactly the same to the user.

Downside with this is that access cannot hold the fomulas in Excel in a table- only their values...
0
 

Author Comment

by:ljcor
Comment Utility
dannywareham

You are ALWAYS here, it seems - and that is really great.

When I commit the action you supplied it works to a degree.  Now I probably am forgetting some of the things that happened before because they do not seem to be happening at the moment.

1. Only the grid portion of the Excel table loads into the Access tabbed page.  I didn't get the entire Excel object; menu, etc.

2.  At some point I double-clicked (I think) on the visible grid portion of the Excel table and (as I recall) the entire spreadsheet appeared.  It took up the screen (again, as I recall) and was a little unwieldly to handle.  In fact, I didn't know exactly what I was doing - or at least it seemed that way in my present memory.  At any rate, I cannot duplicate the bringing up of the entire Excel at this time.

Maybe more will come to me but I will give you that much to chew on right now.


0
 

Author Comment

by:ljcor
Comment Utility
dannywareham,

I just tried again with the Excel worksheet.  That double-clicking on the portion open on the Tab page only works in Design mode.  Even so, only the grid portion stays within it's container AND only the rows and columns designations show, on the border of the container.  The Excel menu does not show at all.  Maybe it never did.

jack
0
 
LVL 26

Accepted Solution

by:
dannywareham earned 420 total points
Comment Utility
Howdo again

I've just double checked my method - works on Access97 and 2000.

What I did was:

1. Save an Excel worksheet to my desktop (although would work in a shared drive too)
2. In form design view, click INSERT>OBJECT. Select LINK and click browse to find your file.
3. Right click on the object and change the ENABLED state to YES and the LOCKED state to NO.

That should be all you need.

If you run the form, when you double click, the spreadsheet opens and any changes are reflected instantly...

0
 

Author Comment

by:ljcor
Comment Utility
Ido fine, thanks.  But I am desperately tired.  4:15AM Honolulu time.  I am going to thank you one more time - and I owe you a lot of thanks.  Then I will accept this method which you have provided.  And then I am going to bed.  I will test it again tomorrow but I have a full anticipation it will work.

Thank you dannywareham.   I genuinely appreciate your assistance.

Jack
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now