Sharing data between Word doc and imbedded Excel worksheet

[Firepig]

Is it possible to share data between a Word document and an Excel worksheet OLE embedded in the Word document?

I have a Word 2010 document I want to distribute to a number of users (so ideally I'd like to avoid VBA, but it may be necessary) who do not have the original source workbook. It has an embedded Excel 2010 worksheet that does some calculations slightly beyond the capabilities of table formulas in Word. But as well as displaying the worksheet as an embedded object, I want to use the results in the text of the Word document. I would ideally like to bookmark cells within the worksheet and then refer to them by Word cross-references, as I would results in a Word table, but that doesn't seem to be possible. I have tried inserting further OLE Excel objects linked to the imbedded object, but they fail to refresh and corrupt the document (and are inelegant anyway). Can I achieve what I want to do?

Also, my spreadsheet needs data inputted by the user. Forcing them to edit the imbedded spreadsheet is tricky, as they need to know what they are doing. It would be better if the data could be retrieved by the worksheet from elsewhere in the document. So the worksheet would be pulling in data from a, say, a Word bookmark. Can Word and an embedded workbook talk to each other like this?

Thanks for any help. Firepig

[macropod]

You can copy a range from the embedded worksheet and paste it into the document using the paste link option, but in my experience this isn't reliable (the links seem to be rather fragile). Your experience seems to mirror this.

As for the abilities of table formulae, you might find they're rather more extensive than you give Word credit for. To see how to do a wide range of calculations in Word, check out my Microsoft Word Field Maths Tutorial and my Microsoft Word Date Calculation Tutorial, both at:
http://www.gmayor.com/downloads.htm#Third_party

[Firepig]

You can copy a range from the embedded worksheet and paste it into the document using the paste link option, but in my experience this isn't reliable (the links seem to be rather fragile). Your experience seems to mirror this.

As for the abilities of table formulae, you might find they're rather more extensive than you give Word credit for. To see how to do a wide range of calculations in Word, check out my Microsoft Word Field Maths Tutorial and my Microsoft Word Date Calculation Tutorial

Paul, thank you, that's brilliant. By a combination of your two documents I am sure I can do what I need within a Word table, which would be a lot better, though it is going to take me some time to understand the material fully. The two things I needed and thought I couldn't do were an IF function (which it seems I can use - I got an error when I tried the first time when I tried it but that was probably me!) and calculation of the difference in days between two dates (which can be done but with some complexity, by the look of it). I also need a little bit of basis relative addressing, to cope with rows being added or deleted.

My worksheet/table calculates interest under the UK's late payment legislation, which has two elements - interest at 8.5% and a fixed sum depending on the size of the debt in three bands. So it takes the invoice date in column 1, adds a period of days to get the due date, deducts that from today's date to get a period of days, calculates the interest element and puts in in one cell; then uses an IF function to put the fixed sum (40, 70 or 100) in another cell in the same row; then totals the invoice, interest and fixed sum for the row. The easy bit is then to use SUM(ABOVE) to total the columns. I've attached the Excel form of it.

You have also solved an issue that had been bugging me for some time over numeric picture switches - it hadn't occurred to me that I could use /# £,0.00 with nothing before the comma and only one digit after it - all Microsoft's examples put a 0 or # before it and create space between the currency symbol and the number when the value is less than 1,000.

If I use a bookmark to reference a cell, and a user inputs a new value to the cell, is there any danger that he may accidentally delete the bookmark?

Your two papers are terrific. Thank you so much!

[macropod]

If you do your document setup using formfields or content controls, you can do the lot with vba (content controls) or formula fields, without the need to worry about what might happen to bookmarks. If you're going to be adding rows to a table using formfields you'd need a macro, and it would also be advisable if using content controls.

Technically, you could also do the job with vba and an ordinary table, using a SelectionChange event. This would obviate the need for any content controls, formfields relative cell referencing and so on. For an outline, see: http://gregmaxey.mvps.org/word_tip_pages/table_cell_events.html

[Firepig]

If you do your document setup using formfields or content controls, you can do the lot with vba (content controls) or formula fields, without the need to worry about what might happen to bookmarks. If you're going to be adding rows to a table using formfields you'd need a macro, and it would also be advisable if using content controls.

Technically, you could also do the job with vba and an ordinary table, using a SelectionChange event. This would obviate the need for any content controls, formfields relative cell referencing and so on. l (http://gregmaxey.mvps.org/word_tip_pages/table_cell_events.html)

Thanks again Paul. I'll stick with Word functions if I can get it to work - my users are not on a corporate network so trying to get them to overcome the security warnings generated by using VBA means they are much less likely to use it. They can be allowed to input the invoice date and amount and to add or delete rows directly by editing the document. If they break it, they can figure it out for themselves!

[macropod]

my users are not on a corporate network so trying to get them to overcome the security warnings generated by using VBA means they are much less likely to use it.
That wouldn't be an issue if you stored the template in a trusted location.

They can be allowed to input the invoice date and amount and to add or delete rows directly by editing the document. If they break it, they can figure it out for themselves!
That's hardly the kind of user support that's going to endear you to anyone. Adding rows with the complex field formulae isn't for the novice, even if you do use relative referencing.

[Firepig]

That wouldn't be an issue if you stored the template in a trusted location.

That's hardly the kind of user support that's going to endear you to anyone. Adding rows with the complex field formulae isn't for the novice, even if you do use relative referencing.

Thanks again Paul. On the first point, I know how to do that using Selfcert but most Word users don't, and they are pretty unlikely to want to follow complex instructions to set this up; I've previously issued them with useful macros and I suspect I have 0% takeup. I'm not likely to get my own digital signature to help them out. All this hard work will save them about five minutes very occasionally, so the main reason for getting it working is now my own satisfaction!

On the second, they are likely to be reasonably competent Word users and to be editing the document anyway. But I take the point about the difficulty of adding rows with the formula content; as it's unlikely there would ever be more than three invoices to work on, the answer is probably to give them three and let them delete rather than add.

Thanks again.

[macropod]

I think you're confused. I said nothing about digital signatures - I referred to a trusted location, which is something entirely differrent.

[Firepig]

I think you're confused. I said nothing about digital signatures - I referred to a trusted location, which is something entirely different.

You are right, I am confused! I hadn't come across the trusted locations concept in this context before. Until now if I wanted to use macros I have been saving them in a template in my Word Startup folder, but I have needed to Selfcert them before they would run without warnings (or without lowering macro security levels). But having just done some reading, my understanding is that if I set my Word startup folder as a trusted location, the signature would be unnecessary. Is that right?

It would certainly be easier to tell my potential users to save a template in Word Startup and then set it as a Trusted Location than it would be to educate them on the use of Selfcert.exe . Any disadvantages to that approach? I'm surprised I haven't come across it in all the articles I've read on using VBA - they seem to use the Selfcert route.

Thanks again for your help (and patience!)

[macropod]

By default, Word's startup folder is a trusted location, so you shouldn't need to set it as one.

For further info, see: http://office.microsoft.com/en-us/word-help/create-remove-or-change-a-trusted-location-for-your-files-HA010031999.aspx

[Firepig]

By default, Word's startup folder is a trusted location, so you shouldn't need to set it as one.

Thanks Paul, I read that article about Word 2007 before I responded, but in my Word 2010 installation the Word Startup folder isn't trusted by default (I haven't changed it). In fact the article says \Program Files\Microsoft Office\Office12\Startup is trusted by default; but the Word Startup folder is at user\Appdata\Roaming\Microsoft\Word\Startup. In Word 2010 I have two trusted locations including user\Appdata\Roaming\Microsoft\Templates but not including the Word Startup folder. As I understand it (probably wrong again) if the macros are not in the Normal template, the template needs to be in Word Startup to get loaded automatically.

[macropod]

Putting a template into the Startup folder isn't necessary and probably not advisable. Rather, you should consider placing the template in the relevant workgroup templates folder and, if that's not already a trusted location, make it one. That way it's available to all users in the workgroup and will apply itself only to documents based on that template.

[Firepig]

Putting a template into the Startup folder isn't necessary and probably not advisable. Rather, you should consider placing the template in the relevant workgroup templates folder and, if that's not already a trusted location, make it one. That way it's available to all users in the workgroup and will apply itself only to documents based on that template.

If the users were on a network, I'm sure that's right, but as I mentioned above they aren't - they are on their own PC's and I'm not responsible for them. All I can do is send them the document or template and some simple instructions on how to use it - after that it's up to them.

[macropod]

In that case, I'd suggest providing the users with a link to, for example: http://office.microsoft.com/en-us/word-help/add-remove-or-change-a-trusted-location-HA010354311.aspx with advice to store the template in a folder that is already defined, or that they will define, as a trusted location.

Of course, if you decide to forego all that goes with the macro approach, I'd suggest also using a table they can delete rows from and perhaps also foregoing the relative referencing. If you do the latter, you'll also need to warn them to always delete rows from the bottom up.

[Firepig]

In that case, I'd suggest providing the users with a link to, for example: http://office.microsoft.com/en-us/word-help/add-remove-or-change-a-trusted-location-HA010354311.aspx with advice to store the template in a folder that is already defined, or that they will define, as a trusted location.

Of course, if you decide to forego all that goes with the macro approach, I'd suggest also using a table they can delete rows from and perhaps also foregoing the relative referencing. If you do the latter, you'll also need to warn them to always delete rows from the bottom up.

Thanks, yes, that's certainly easier than trying to get them to mess about with Selfcert.exe as I have previously done. Any reason why the trusted location should not be their own Word Startup folder, so the macros are always available?

Alternatively, if I put the macro in a template, and then base the document on the template, and distribute both, with instructions to put the template in the Templates folder (which is trusted by default), does that work? Are macros in the document's own template loaded automatically? I guess that would be a better approach where the macros are specific to a particular document, as here.

I'll revisit the bare table approach. I can get away without relative addressing so long as they don't have to add rows - I can use absolute addressing within rows and sum(above) for the totals. I just need to understand the code for calculating the differences between dates.

[macropod]

Putting your template in the same folder as Word stores its Normal template in should be fine. FWIW, it's much easier to do all the calculations with a macro in a variable-row table than it is to achieve the same result via fields.

[Firepig]

Putting your template in the same folder as Word stores its Normal template in should be fine. FWIW, it's much easier to do all the calculations with a macro in a variable-row table than it is to achieve the same result via fields.

But the document needs to be based on that template, right? A macro in a template in \Templates that's not in use is not available, so far as I know; to achieve that it needs to be in Word Startup, unless I've got the wrong end of the stick again.

I agree that a macro would be much easier, but since the security was tightened I find macros are impractical for the purposes of documents you intend to share, for the reasons we have been discussing. It was much easier (but dangerous) when you could just receive and use a document containing macros. .docm files seem to be pretty useless without a mechanism for the user to trust them. Hmm, I wonder if that could be done in VBA...

[macropod]

You need to use a template so that each document can start afresh (ie not with the contents of a previous edit) and so that the users won't be likely to overwrite a previously-saved version with disparate data.

A template can be stored in any folder. Storing in the same folder as Normal.dot means it will be stored in what is usually a trusted location. If it isn't, the user can easily make it one. Opened from a trusted location, the macro will run automatically, without requiring user intervention.

Instead of cogitating endlessly about this, try it on your own system.

[Firepig]

Ah, OK, we're talking about different things. I have been contemplating a document with all the content for the user to edit and use, attached to a template containing only the macro. I appreciate that this could be done with a template but again, it would generate user confusion. The users are lawyers. They will rarely if ever generate new documents from templates - they use libraries of previous documents and are used to editing them, producing different versions etc. I've tried before to use templates for legal precedents and it doesn't seem to get acceptance. I think the main reason is that they can't find the document they're using for using the Word Open dialog box without remembering it's a template and changing the settings.

Thanks for your help and patience.