MS Office Spreadsheet 9.0

[John]

I've built a custom task with a spreadsheet on one of the
tabs using the MS Office Spreadsheet 9.0 tool but the data
does not seem to save when using the form. Everything else
I've built into it works fine but whatever data I put into
the spreadsheet is lost when I use "Save and Close."

Am I doing something wrong or does this spreadsheet just
not work?

Thanks in advance for any help!

 

[Sue Mosher [MVP-Outlook]]

Did you include code to save the spreadsheet information to an Outlook
property? See http://www.outlookcode.com/d/spreadsheetcontrol.htm

 

[John]

Hmm, I was hoping to not have to code anything with this
project because I'm just not very good at it. =)

I tried pasting this into the code on my form but it keeps
having a problem with the oPage.Controls("Spreadsheet1")
part. I read the section that said you couldn't have any
other controls in front of the spreadsheet on a form but I
even tried a "post" form with the spreadsheet on the first
tab and nothing else on the form but it still has a
problem with this section.

Any suggestions?

 

[Sue Mosher [MVP-Outlook]]

What is the specific problem? Do the names you gave the page and the control
match the name in your code?

 

[John]

Well, I've actually been able to get it to work without
any errors and it saves any data entered into the form
now. However, the original data put into the form is lost.
I filled it with several formulas and such and I can still
see that in the original form I'm working on but as soon
as I run it all the original spreadsheet data is lost.

Any suggestions for this?

Thanks alot for your advice!

 

[John]

Scratch the last post, the data I originally built into
the spreadsheet is lost now that I have the code saving to
the correct tab. If I rebuild it all will it continue to
be deleted or will it stay on the spreadsheet after being
published?

 

[Sue Mosher [MVP-Outlook]]

Nothing stays on the spreadsheet. It's an unbound control. That's why it's
critical to include code that saves the spreadsheet information when the
item closes and then restores it when the item opens. You did publish the
form, didn't you? Code doesn't run on unpublished forms.

--
Sue Mosher, Outlook MVP
Author of
Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers

 

[Guest]

So, I can't create a spreadsheet filled with formulas and
locked cells before I publish the form?

Do I have to publish the form, create the data on the
spreadsheet and then make the form public?

We have a clunky old spreadsheet that I'd like to move
most of the data into this form and then publish it for
users to input the final data and send off the results. Is
this possible in the way I've begun this project?

 

[Sue Mosher [MVP-Outlook]]

Think of the spreadsheet control as a blank grid. It's always blank when the
item opens -- always, regardless of the state when the item closed. (This is
true, BTW, of all unbound Outlook form controls, not just the spreadsheet
control.) Anything that you want the user to see in that grid must be (a)
placed there by the user or (b) placed there for code, usually code running
in the Item_Open event handler of the custom form.

The alternative solution may be more what you had in mind. In Outlook 2000
(not later versions), choose File | New | Office Document | Excel Worksheet.
Copy and paste from your klunky old spreadsheet into this worksheet, then
publish it. This embeds the worksheet not in a separate unbound control, but
actually in a document in the item.

TIP: Create blank examples of all three Office Document forms and save them
as .oft files. Later versions of Outlook do not allow you to create new such
forms, but you can use and modify Office Document forms created in Outlook
2000.

--
Sue Mosher, Outlook MVP
Author of
Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers