Go Premium for a chance to win a PS4. Enter to Win


Embed Excel into an Access tab control page

Posted on 2004-08-22
Medium Priority
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!
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

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 1680 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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

876 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