473,327 Members | 2,055 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

Microsoft Office Spreadsheet 11.0 ActiveX Control

91
I want to use the Microsoft Office Spreadsheet 11.0 ActiveX Control on my form so that I can view and edit a spreadsheet inside the form as if there was a mini excel window in there.

I converted the .xsl to a XML spreadsheet as this control only lets you import CSV, XML, or HTML pages.

Everything shows up fine, but is there a way to save back to the file? Seems like it can only read the file and update the form with new data inputted from Excel itself. Any changes made inside Access will not update the actual file. Is this control supposed to be able to do this or is it made more for just displaying information rather than input?

If not, whats the best way to do this? I have a bunch of spreadsheets with data that I want to integrate into my database. I dont want to have forms with over 60 labels and text boxes and set up all new tables on my backend and design forms as that is very time consuming, and also looks kind of ridiculous.
Jun 4 '10 #1
19 26303
ADezii
8,834 Expert 8TB
@matt753
  1. Create an OLE Object Field in a Table.
  2. Insert each Spreadsheet as an OLE Object into this Field for each Record.
  3. In a Form Bound to the Table containing the OLE Object Field, add a Bound Object Frame.
  4. Set the Control Source of the Bound Frame to the OLE Object Field.
  5. As you navigate each Record, the appropriate Spreadsheet will be visible in the Bound Object Frame.
  6. Dbl-Click on the Spreadsheet to Activate it within Microsoft Excel where you can make any changes.
Jun 5 '10 #2
matt753
91
Could you go into more detail for the steps? I've never dealt with OLE objects before

Thanks for the help!
Jun 6 '10 #3
ADezii
8,834 Expert 8TB
@matt753
In this case, a Picture is worth a thousand words.
  1. Download the Attachment
  2. Create a Folder on the C: Drive named Test (C:\Test)
  3. Copy the 4 files contained within the *.Zip to this Folder
  4. Open the Database and follow the simple Instructions
Attached Files
File Type: zip Linked Spreadsheets.zip (206.5 KB, 3357 views)
Jun 6 '10 #4
matt753
91
oh ok that makes sense now, that helped me understand what you were saying.

Is there any way to be able to edit it in the actual form though? Without having to double click, open excel, and edit it inside there?
Jun 7 '10 #5
ADezii
8,834 Expert 8TB
@matt753
Embed, instead of Link the Excel Spreadsheet, now you can activate it in place, but you'll lose the Link to the actual File.
Jun 7 '10 #6
matt753
91
Do you mean embed it on the form? Or change how it is set up in that table? Not sure exactly what you mean.
Jun 7 '10 #7
matt753
91
ps if theres a way to save using that office spreadsheet activex control that would also solve all the problems
Jun 7 '10 #8
ADezii
8,834 Expert 8TB
@matt753
Not familiar with that Control.
Jun 7 '10 #9
matt753
91
Its located in the more controls menu, basically it allows you to select an XML spreadsheet (I converted my xsl to this), HTML file, or CSV file, and it displays it in a window looking very similar to Excel, right inside your form. You can click inside each cell and edit and everything, but theres no save button and once you leave thr page and go back no changes are there. I assume you should be able to save back to the file but maybe it is intended for only viewing, i'm not sure.

If you want to try it out, on your form goto More Controls -> Microsoft Office Spreadsheet 10.0 or 11.0, drag box to make control on your form, right click inside or in a cell -> Commands and Options -> Import -> then select your file
Jun 7 '10 #10
matt753
91
Embed, instead of Link the Excel Spreadsheet, now you can activate it in place, but you'll lose the Link to the actual File.
Will this method allow the spreadsheet to be viewed as well as edited and saved back to the file?
Jun 7 '10 #11
ADezii
8,834 Expert 8TB
@matt753
I'll look into it when I have some spare time, and see if I can come up with something. Thanks.
Jun 7 '10 #12
matt753
91
Awesome thanks for the help
Jun 7 '10 #13
matt753
91
Did you have a chance to take a look?
Jun 11 '10 #14
ADezii
8,834 Expert 8TB
@matt753
Sorry, been very busy, but I'll try to get to it this weekend.
Jun 11 '10 #15
matt753
91
No worries, sounds good
Jun 11 '10 #16
ADezii
8,834 Expert 8TB
@matt753
This is what I came up with matt753:
  1. The XML Spreadsheet can easily be Imported into the ActiveX Control embedded within a Form.
  2. In the above context, any changes that you make within the Spreadsheet Control are only available during the Current Session only and will not be in 'Sync' with the original XML Data.
  3. To Save any changes to the Data in the Control within your Access Form, click the Export to Microsoft Excel Button on the Spreadsheet Toolbar.
  4. The ActiveX Spreadsheet Control does expose an Updated() Event that is activated each time that a Cell is either Updated or Written to. I see no reason why you could not use Automation Code to write the Data back to the original Spreadsheet, but then it would have to be Re-Imported into the Control again once the DB is opened.
  5. This is probably not what you wanted to hear, but it is what I have come up during my brief analysis of this Control.
  6. You can probably expand on this information yourself should you so desire.
Jun 12 '10 #17
matt753
91
The ActiveX Spreadsheet Control does expose an Updated() Event that is activated each time that a Cell is either Updated or Written to. I see no reason why you could not use Automation Code to write the Data back to the original Spreadsheet, but then it would have to be Re-Imported into the Control again once the DB is opened.
I think the "auto refresh data from URL at run time" checkbox would take care of the updating from the file from the looks of things. What would the code be to write back to the XML file in the Updated Event?
Jun 16 '10 #18
ADezii
8,834 Expert 8TB
@matt753
It would essentially be recreating the Spreadsheet and copying the Data to the newly created one.
Jun 16 '10 #19
matt753
91
Hmm maybe this isnt the best way to do what i'm trying to do then if you cant save back you the same one every time.


Are there any other ways I can integrate spreadsheets into my database?
Jun 17 '10 #20

Sign in to post your reply or Sign up for a free account.

Similar topics

0
by: Dan Boehm | last post by:
I am using visual studio.net 2003 and am currently developing an application that may require Microsoft Office Spreadsheet 10. This is a COM object. I am curious about what copyright information...
1
by: Nothing | last post by:
I am trying to create an field on a form which is link to a table to store pictures. I have the field defined as follows: Name: Photofield1 Type: OLE Object I have created a form with this...
2
by: Smartin | last post by:
I tried to add the "Microsoft Common Dialog Control, version 6.0" (also known as COMDLG32.OCX) to an Access97 application I am developing and received error "You Don't Have a License to use this...
3
by: seaweed | last post by:
Hi, all. I am a beginner. If C# can not be used to ActiveX control, How can I do? Thank you in advance.
3
by: vegvegko | last post by:
Hi, Is there any way to build or distribute a C# application with a Powerpoint component, so that the application can display Powerpoint presentations on PCs that do not have Microsoft Office...
2
by: apartain | last post by:
Does anyone know how to use the MS Web Browser ActiveX Control? It is intended for allowing users to view web sites on a form.
6
by: =?Utf-8?B?U2llZ2ZyaWVkIEhlaW50emU=?= | last post by:
Previously I could write (supported) ActiveX controls for IE only. Has this changed with VS2008? Does office support ActiveX controls written in VS2008? What about older versions of office: will...
0
by: christian | last post by:
Hi, I am using Access 2003 and have a form with the Outlook View Control. I want user to be able to drag an item from the view control to a bound ole object field which stores the message as a...
0
by: mdamjan | last post by:
I am trying to read content of an email in Outlook from the VB program. I have inserted this Microsoft office outlook view control, and in the code below I can identify emails by subject line and...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.