Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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.j pg", , , rng rng.InlineShapes.AddOLEObject , "c:\someexcel.xml", , , , , , rng wddoc.SaveAs "c:\del_modified.doc" wddoc.Close Set wddoc = Nothing Set wdApp = Nothing End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inserting Excel OLE Object
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.j pg", , , rng rng.InlineShapes.AddOLEObject , "c:\someexcel.xml", , , , , , rng wddoc.SaveAs "c:\del_modified.doc" wddoc.Close Set wddoc = Nothing Set wdApp = Nothing End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inserting Excel OLE Object
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.j pg", , , rng rng.InlineShapes.AddOLEObject , "c:\someexcel.xml", , , , , , rng wddoc.SaveAs "c:\del_modified.doc" wddoc.Close Set wddoc = Nothing Set wdApp = Nothing End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Inserting a MS Word object into an Excel spreadsheet (2 questions) | Excel Worksheet Functions | |||
Issues inserting wide Word object into Excel | Excel Discussion (Misc queries) | |||
inserting an object in Excel | Excel Programming | |||
Inserting an Object in Excel | Excel Programming | |||
Inserting Object in Excel Footer | Excel Programming |