Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 08-26-2015, 03:32 AM
catflap's Avatar
catflap catflap is offline Refer to cell in embedded Excel object within word Windows XP Refer to cell in embedded Excel object within word Office XP
Advanced Beginner
Refer to cell in embedded Excel object within word
 
Join Date: Aug 2015
Location: UK
Posts: 72
catflap is on a distinguished road
Default 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.
Reply With Quote
  #2  
Old 08-26-2015, 05:22 AM
macropod's Avatar
macropod macropod is offline Refer to cell in embedded Excel object within word Windows 7 64bit Refer to cell in embedded Excel object within word Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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]
Reply With Quote
  #3  
Old 08-26-2015, 08:48 AM
catflap's Avatar
catflap catflap is offline Refer to cell in embedded Excel object within word Windows XP Refer to cell in embedded Excel object within word Office XP
Advanced Beginner
Refer to cell in embedded Excel object within word
 
Join Date: Aug 2015
Location: UK
Posts: 72
catflap is on a distinguished road
Default

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....
Reply With Quote
  #4  
Old 08-26-2015, 03:56 PM
macropod's Avatar
macropod macropod is offline Refer to cell in embedded Excel object within word Windows 7 64bit Refer to cell in embedded Excel object within word Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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]
Reply With Quote
  #5  
Old 08-28-2015, 03:02 AM
catflap's Avatar
catflap catflap is offline Refer to cell in embedded Excel object within word Windows XP Refer to cell in embedded Excel object within word Office XP
Advanced Beginner
Refer to cell in embedded Excel object within word
 
Join Date: Aug 2015
Location: UK
Posts: 72
catflap is on a distinguished road
Default

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....
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Refer to cell in embedded Excel object within word Excel table: refer to a particular row? tinfanide Excel 10 08-05-2015 01:17 PM
Refer to cell in embedded Excel object within word 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
Refer to cell in embedded Excel object within word 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
Refer to cell in embedded Excel object within word Editing an embedded OLE object DugganSC Word VBA 1 08-29-2011 01:40 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 09:59 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft