#1
|
||||
|
||||
Refer to cell in embedded Excel object within word
Hi there I have a word doc and have inserted the excel object within it. What I'd like to do is be able to refer to the value of cell A1 within the sheet with the aim of changing it from vba. The goal is for me to get the value of a merge field from a datasource attached to the word doc, then change the cell value in the excel sheet to this merge value. I'd loop through the datasource one record at a time. This would hopefully be a bit of a kludge way of taking advantage of excel's ability to shrink or expand text to fit a box - something I've never found a way to do in a word mail-merge without a lot of trouble. So what I need to know is how to refer to the cell in word vba. Thanks for reading! - I know it's a bit convoluted but I thought I'd better explain it all. |
#2
|
||||
|
||||
Referencing cells within an embedded Excel object isn't exactly straightforward, mainly because Office provides no deactivation method. Besides which, it's not apparent how you'd propose to make this work in the context of a mailmerge, unless the mailmerge is itself a macro-driven process. In any event, if you want to shrink or expand text, why not use a table cell with the 'Fit text' attribute set?
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#3
|
||||
|
||||
Thanks for responding.
I've tried the fit text thing before - it sounded like what I wanted initially, but unfortunately the result is that word just stretches the text, which can produce some ugly results. What I'm really after is something that will adjust the font size to fit instead, so it doesn't look distorted. To address your other point, I had envisioned running a mail merge by looping through the document's datasource in vba and printing the current record as I went - not ideal I know, but it would have to be something like that to get the excel cell updated with each record as it looped. I've been toying with this sort of thing: Dim objExcel As New Excel.Application Dim exWb As Excel.Workbook Set exWb = objExcel.Workbooks.Open("c:\mergesource.xls") exWb.Sheets("Sheet1").Cells(1, 1) = "Hello there" MsgBox exWb.Sheets("Sheet1").Cells(1, 1) This displays the corrected 'Hello there' text in the msgbox, but does not update the excel object in, so it doesn't help unless I can find some way to update or refresh it (the aim being to be able to make use of Excel's ability to re-size the text and then merge to this). I guess this is what you meant when referring to the 'deactivation method'? I'm starting to get the feeling I'm trying to get Word to do something it just doesn't want to do, and that never seems to lead to a positive result.... |
#4
|
||||
|
||||
It's not clear to me how you'll achieve anything better via an embedded Excel object, since you'll have the same issues with variable-length content there. Granted, such an object can be re-scaled, but that doesn't address issues of text-wrapping and font sizes. That said, you could try code like:
Code:
Sub Demo() Application.ScreenUpdating = False Dim objOLE As Word.OLEFormat, objXL As Object Dim R As Long, C As Long, X As Long R = 1: C = 2 X = 100 With ActiveDocument With .InlineShapes(1) If Not .OLEFormat Is Nothing Then If Split(.OLEFormat.ClassType, ".")(0) = "Excel" Then Set objOLE = .OLEFormat objOLE.Activate Set objXL = objOLE.Object objXL.ActiveSheet.Cells(R, C).Value = X On Error Resume Next objOLE.Application.Quit End If End If End With End With Set objXL = Nothing: Set objOLE = Nothing Application.ScreenUpdating = True End Sub
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#5
|
||||
|
||||
Sorry for late reply - got bogged down in another project now.
I'll give your code a try out as soon as I can. Only problem now if that we are due to upgrade to Office 2013 in the next week or two.... |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Excel table: refer to a particular row? | tinfanide | Excel | 10 | 08-05-2015 01:17 PM |
If (C1 = refer to a cell in a range) then (show the description in C2) & (corresponding price in C3) | lily | Excel | 9 | 10-23-2014 06:21 AM |
Edit embedded Visio object in Word | TimTDP | Visio | 12 | 01-26-2014 12:43 AM |
An embedded object's formatting gets jumbled | New Daddy | PowerPoint | 0 | 11-17-2013 08:13 PM |
Editing an embedded OLE object | DugganSC | Word VBA | 1 | 08-29-2011 01:40 PM |