Embed Excel into an Access tab control page
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
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
ASKER
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
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
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.followhyperlin
good luck!
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.followhyperlin
good luck!
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.
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.
ASKER
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
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
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).
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).
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...
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...
ASKER
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.
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.
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
Thank you dannywareham. I genuinely appreciate your assistance.
Jack
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).
:-)