Embed Excel into an Access tab control page

Posted on 2004-08-22
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,
Question by:ljcor
  • 5
  • 5
LVL 26

Expert Comment

ID: 11863259
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).


Author Comment

ID: 11863938
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.
LVL 12

Expert Comment

ID: 11863960
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!
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

LVL 26

Expert Comment

ID: 11863995
OK, if you prefer to put Excel straight in...
Save you excel worksheet to a drive/location.
Open a form.
Click (on toolbar)
Select your file
Check the LINK box
Click OK

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.

Author Comment

ID: 11920772
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,
LVL 26

Expert Comment

ID: 11920785
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).
LVL 26

Expert Comment

ID: 11920793
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...

Author Comment

ID: 11920916

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.


Author Comment

ID: 11920971

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.

LVL 26

Accepted Solution

dannywareham earned 420 total points
ID: 11921184
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...


Author Comment

ID: 11921231
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.


Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

860 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