Code to calculate totals in multiple embedded Excel worksheets

[phrankndonna]

Hi, we have Word documents that have embedded Excel worksheets for logging specific information and attaching scores, weighting the scores, and totalling the scores for each section. We don't use an Excel workbook because of the other text and formatting surrounding the Excel objects, and we don't use the rudimentary arithmetic functionality in Word because a) it's not robust enough to do all we want, and b) the formatting we need 'breaks' the overall calculations.

Is there code that I can use that will 'grab' the figures/totals from the various embedded Excel worksheets and total them all up? Thanks.

Frank

[macropod]

For this kind of thing you'd probably do better to use an external workbook, with links to it from the Word document. That way, all of the calculations can be done in one place, without opening Word, and the total can be calculated there and captured in Word via a link also. With automatic link updating, the performance will be pretty much the same as you have now.

[phrankndonna]

Unfortunately, the workbook can't be external. We are using Word documents on a SharePoint site so that multiple people can modify the document at the same time. The embedded Excel worksheet sections enable that. Each section is slightly different, but the scores and calculations are calculated in each section within the Word document, but I haven't been able to figure out how to complete the overall calculations. It may be that we just have to calculate the totals externally manually.

[macropod]

Unfortunately, although you could set up links to the individual values in the embedded worksheets, by copying the Excel data in an object and pasting it somewhere else in Word via Paste Special with the 'paste link' option, the links thus created are unstable. The only other viable alternative is to use a macro to open each object, get & tally the data, then output the results to, say, a bookmark.

[snb]

See the attachment.

First activate one of the embedded sheets; exit the sheet; click the 'update' button

[phrankndonna]

That's exactly what I was thinking of. There will probably be other columns to total, but I believe I can follow this code to expand upon it. THANK YOU!!!