Manipulating Excel Worksheet Embedded into Document

[jasoncw]

Hi everyone.

I am trying to wrap up a project, but I am stuck. The project consists of taking the input values from a userform and entering them into various Word documents and Excel workbooks. The procedure is run from Word. I have no issue completing this part.

However, there are a few documents that have an Excel worksheet embedded into them. How can I manipulate these worksheets using VBA? I have attached an example for your reference.

I hope I have explained what I need to have accomplished well enough. If not, please let me know to explain further. TIA

Jason

[jasoncw]

Ok, I have found out how to get into edit mode, but I still do not know how to actually edit the worksheet.
With Selection
.GoTo What:=wdGoToObject, Which:=wdGoToNext, _
Count:=1, Name:="Excel.Sheet.8"
.ShapeRange(1).OLEFormat.DoVerb _
VerbIndex:=wdOLEVerbPrimary
End With

[jasoncw]

FYI, I have received a solution to this on another forum. So for future searching purposes, here is the response I received:

Posted by Leith Ross:

Hello Jason,

This macro will open the embedded worksheet and let you work on it. Place this code in a standard VBA module in your Word document. I provided an example of getting the totals as guide to retrieve data from the worksheet. If you have any questions, just ask.



'Written: March 28, 2008



'Author: Leith Ross


Sub OpenEmbeddedWorksheet()


Dim Wks As Object
Dim wdOLE As Object
Dim xlApp As Object


Const xlMaximized As Long = -4137
Const xlMinimized As Long = -4140
Const xlNormal As Long = -4143


'Create an instance of Excel
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
xlApp.WindowState = xlMinimized


'Wait - Word can't open the object until Excel is visible
While Not xlApp.Visible
'Delay loop
Wend


'Open the embedded Excel worksheet
Set wdOLE = ActiveDocument.InlineShapes(1).OLEFormat
wdOLE.DoVerb wdOLEVerbOpen


'Set a reference the worksheet
xlApp.Windows(1).WindowState = xlMaximized
Set Wks = xlApp.Windows(1).Activesheet


'Get the Totals
TotalPartA = Wks.Range("$B$4")
TotalPartB = Wks.Range("$C$4")
ToTalPartC = Wks.Range("$D$4")


'Close the Workbook and Excel
xlApp.ActiveWorkbook.Close
xlApp.Quit


'Release Objects in memory
Set wdOLE = Nothing
Set Wks = Nothing
Set xlApp = Nothing


End Sub

[lucas]

Jason, if you post the same question on other forums then we expect you to provide a link to it as soon as it happens. It is discourtious to people answering your questions to let them labor on somehing that may have already been solved.

Please read our FAQ and THIS (http://www.excelguru.ca/node/7)

If folks find you are doing this without providing the link they will stop helping you.......

[jasoncw]

Well, I posted here first, and when I didn't get a reply, I posted on another forum. As soon as I received a response, I posted here. So I'm sure there was no one laboring on this any more than if someone was replying to this thread. I do not post in more than one forum at a time unless I don't receive any replies from the first forum in which I post.

Anyway, I will be sure to post the link in the future.

[jasoncw]

Sorry again for not posting the link to the other thread I posted a couple days after I posted this one. Here it is for reference. (http://www.excelforum.com/showthread.php?t=639285)

Unfortunately, after I marked the thread as solved, another issue came up. I am now trying to figure out how to manipulate the number of visible cells once exiting out of Excel and returning back to the Word document.

Depending on the number of facilities needed, I may need to insert rows. After inserting rows in Excel, the number of rows displayed in the document does not change. How am I able to manipulate the size of this in VBA?

Also, if someone can mark this as "unsolved" I would appreciate it.

TIA for any input.

Jason

[jasoncw]

With the help of some others, I have developed a work-around to this issue. If anyone can figure it out, I would still appreciate a response, however.

My procedure opens the worksheet object, manipulates it, then copies > paste link to Word, then deletes the original worksheet object. Here is the snippet that I came up with:


Dim wdRng As Word.Range, wdShapeRange As Word.Range
Dim rngStart As Long, rngEnd As Long

'. . .

'open the embedded Excel worksheet
With ActiveDocument.InlineShapes(1)
rngStart = .Range.Start
rngEnd = .Range.End
Set wdRng = ActiveDocument.Range(rngStart - 1, rngStart - 1)
Set wdShapeRange = ActiveDocument.Range(rngStart, rngEnd)
wdShapeRange.Expand wdParagraph
Set wdOLE = .OLEFormat
wdOLE.DoVerb wdOLEVerbOpen
End With

'. . .

'copy the worksheet and paste link into the document
Wks.Range("A1").CurrentRegion.Copy
With wdRng
.PasteSpecial Link:=True, Placement:=wdInLine, DataType:=wdPasteOLEObject
.ParagraphFormat.Alignment = wdAlignParagraphCenter
End With

Jason