Inserting Excel OLE Object

Hi,

I saw an example in this forum to push changes from Excel to Word, and
modified it slightly to inste excel table(OLE tables) from Excel to Word. I
can get it work for inserting the entire table, but would it be possible to
insert just a portion of the table? i.e. i have a huge spreadsheet, but I
want to display just the first two columns in Word....

Thanks you for the response.

Aga

Here is my code from Excel

Public Sub rnn()
Dim rng As Object
Dim wdApp As Object
Set wdApp = CreateObject("Word.Application")

Dim wddoc As Object
Set wddoc = wdApp.Documents.Add("c:\del.doc")

Dim bkMark As Object
Dim bkMarks As Object
Set bkMarks = wddoc.Bookmarks
For Each bkMark In bkMarks
Debug.Print bkMark.Name
If (bkMark.Name = "img_bk") Then
Set rng = bkMark.Range
End If
Next bkMark



' rng.InlineShapes.AddPicture
"C:\Projects\VisualScreen_2BETA\Code\vs_logo.jpg", , , rng
rng.InlineShapes.AddOLEObject , "c:\someexcel.xml", , , , , , rng

wddoc.SaveAs "c:\del_modified.doc"
wddoc.Close

Set wddoc = Nothing
Set wdApp = Nothing

End Sub

Your code is inserting an OLE object from an Excel XML file. The entire XML file is
inserted into the OLE object. You would need to store a subset of the worksheet as
the source XML file.

Alternatively, you could open the Excel workbook in Excel through Word VBA
automation, then make the changes and insert the desired range into Word.

You could write code to edit the range of the workbook prior to inserting it. The
following edits a regular workbook (not XML, my laptop only has Excel 2000), saves
it with a new name, and then inserts it:

Sub Macro1()
Dim xlWbk As Excel.Workbook
Dim sFileName1 As String

Const sFileName As String = "C:\MyFile.xls"
sFileName1 = Replace(sFileName, ".xls", "_1.xls")

Set xlWbk = GetObject(sFileName)
With xlWbk.worksheets(1)
.Columns(4).Delete
.Rows(10).Delete
End With
xlWbk.SaveAs sFileName1
xlWbk.Close
Set xlWbk = Nothing

Selection.InlineShapes.AddOLEObject _
ClassType:="Excel.Sheet.8", FileName:=sFileName1, _
LinkToFile:=False, DisplayAsIcon:=False

End Sub

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

raven wrote:

> Hi,
>
> I saw an example in this forum to push changes from Excel to Word, and
> modified it slightly to inste excel table(OLE tables) from Excel to Word. I
> can get it work for inserting the entire table, but would it be possible to
> insert just a portion of the table? i.e. i have a huge spreadsheet, but I
> want to display just the first two columns in Word....
>
> Thanks you for the response.
>
> Aga
>
> Here is my code from Excel
>
> Public Sub rnn()
> Dim rng As Object
> Dim wdApp As Object
> Set wdApp = CreateObject("Word.Application")
>
> Dim wddoc As Object
> Set wddoc = wdApp.Documents.Add("c:\del.doc")
>
> Dim bkMark As Object
> Dim bkMarks As Object
> Set bkMarks = wddoc.Bookmarks
> For Each bkMark In bkMarks
> Debug.Print bkMark.Name
> If (bkMark.Name = "img_bk") Then
> Set rng = bkMark.Range
> End If
> Next bkMark
>
>
>
> ' rng.InlineShapes.AddPicture
> "C:\Projects\VisualScreen_2BETA\Code\vs_logo.jpg", , , rng
> rng.InlineShapes.AddOLEObject , "c:\someexcel.xml", , , , , , rng
>
> wddoc.SaveAs "c:\del_modified.doc"
> wddoc.Close
>
> Set wddoc = Nothing
> Set wdApp = Nothing
>
> End Sub

But Jon,
How do you it with an Excel Range?
I have 5 ranges and I would like to select which range to copy across Word.
Francisco


"Jon Peltier" wrote:

> Your code is inserting an OLE object from an Excel XML file. The entire XML file is
> inserted into the OLE object. You would need to store a subset of the worksheet as
> the source XML file.
>
> Alternatively, you could open the Excel workbook in Excel through Word VBA
> automation, then make the changes and insert the desired range into Word.
>
> You could write code to edit the range of the workbook prior to inserting it. The
> following edits a regular workbook (not XML, my laptop only has Excel 2000), saves
> it with a new name, and then inserts it:
>
> Sub Macro1()
> Dim xlWbk As Excel.Workbook
> Dim sFileName1 As String
>
> Const sFileName As String = "C:\MyFile.xls"
> sFileName1 = Replace(sFileName, ".xls", "_1.xls")
>
> Set xlWbk = GetObject(sFileName)
> With xlWbk.worksheets(1)
> .Columns(4).Delete
> .Rows(10).Delete
> End With
> xlWbk.SaveAs sFileName1
> xlWbk.Close
> Set xlWbk = Nothing
>
> Selection.InlineShapes.AddOLEObject _
> ClassType:="Excel.Sheet.8", FileName:=sFileName1, _
> LinkToFile:=False, DisplayAsIcon:=False
>
> End Sub
>
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Peltier Technical Services
> Tutorials and Custom Solutions
> http://PeltierTech.com/
> _______
>
> raven wrote:
>
> > Hi,
> >
> > I saw an example in this forum to push changes from Excel to Word, and
> > modified it slightly to inste excel table(OLE tables) from Excel to Word. I
> > can get it work for inserting the entire table, but would it be possible to
> > insert just a portion of the table? i.e. i have a huge spreadsheet, but I
> > want to display just the first two columns in Word....
> >
> > Thanks you for the response.
> >
> > Aga
> >
> > Here is my code from Excel
> >
> > Public Sub rnn()
> > Dim rng As Object
> > Dim wdApp As Object
> > Set wdApp = CreateObject("Word.Application")
> >
> > Dim wddoc As Object
> > Set wddoc = wdApp.Documents.Add("c:\del.doc")
> >
> > Dim bkMark As Object
> > Dim bkMarks As Object
> > Set bkMarks = wddoc.Bookmarks
> > For Each bkMark In bkMarks
> > Debug.Print bkMark.Name
> > If (bkMark.Name = "img_bk") Then
> > Set rng = bkMark.Range
> > End If
> > Next bkMark
> >
> >
> >
> > ' rng.InlineShapes.AddPicture
> > "C:\Projects\VisualScreen_2BETA\Code\vs_logo.jpg", , , rng
> > rng.InlineShapes.AddOLEObject , "c:\someexcel.xml", , , , , , rng
> >
> > wddoc.SaveAs "c:\del_modified.doc"
> > wddoc.Close
> >
> > Set wddoc = Nothing
> > Set wdApp = Nothing
> >
> > End Sub
>
>