Microsoft Office Spreadsheet 11.0 ActiveX Control

[matt753]

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.

[ADezii]

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.

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.

[matt753]

Could you go into more detail for the steps? I've never dealt with OLE objects before

Thanks for the help!

[ADezii]

Could you go into more detail for the steps? I've never dealt with OLE objects before

Thanks for the help!

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

[matt753]

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?

[ADezii]

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?

Embed, instead of Link the Excel Spreadsheet, now you can activate it in place, but you'll lose the Link to the actual File.

[matt753]

Do you mean embed it on the form? Or change how it is set up in that table? Not sure exactly what you mean.

[matt753]

ps if theres a way to save using that office spreadsheet activex control that would also solve all the problems

[ADezii]

ps if theres a way to save using that office spreadsheet activex control that would also solve all the problems

Not familiar with that Control.

[matt753]

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

[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.

Will this method allow the spreadsheet to be viewed as well as edited and saved back to the file?

[ADezii]

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

I'll look into it when I have some spare time, and see if I can come up with something. Thanks.

[matt753]

Awesome thanks for the help

[matt753]

Did you have a chance to take a look?

[ADezii]

Did you have a chance to take a look?

Sorry, been very busy, but I'll try to get to it this weekend.