Using vbnet to link excel objects to word document

  • Thread starter Thread starter Lisa
  • Start date Start date
L

Lisa

Hi -
I'm able to open excel workbooks and word documents, but I can't seem to
copy excel charts, named ranges, etc. to a word document. Anyone know of
good reference material in this area? What little documentation I've been
able to find focuses on using only one office app at a time.

Thanks for your help
 
I'm able to open excel workbooks and word documents, but I can't seem to
copy excel charts, named ranges, etc. to a word document.
Click to expand...

Can I see the code you are using to copy the excel object to the clipboard
please?

It is very difficult to diagnose without this.
 
Lisa said:
I'm able to open excel workbooks and word documents, but I can't seem to
copy excel charts, named ranges, etc. to a word document. Anyone know of
good reference material in this area? What little documentation I've been
able to find focuses on using only one office app at a time.
Click to expand...

Hi Lisa,

this is the minimal code to insert an Excel-Sheet into a Word-Document:

'set a reference to the Word xx.0 Object Library
Imports wd = Microsoft.Office.Interop.Word
Imports xl = Microsoft.Office.Interop.Excel

'Start Word
Dim wdApp As New wd.ApplicationClass
'Add an empty document
Dim wdDoc As wd.Document = wdApp.Documents.Add()
'Make Work visible
wdApp.Visible = True
'ClassType of an Excel-Sheet. Can of course also be a diagram,
'a chart, a sound-file, whatever supports OLE
Dim ClassType As Object = "Excel.Sheet.8"
'Location of the file
Dim FileName As Object = "C:\test.xls"
'Embed, not link
Dim LinkToFile As Object = False
'Add OleObject
wdDoc.InlineShapes.AddOLEObject(ClassType, FileName, LinkToFile)

[Reference: VBA]
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbawd10/html/womthAddOLEObject.asp

To edit embedded objects again you can use the following code:

'let's say our Excel-Sheet is the first Object in the document
Dim ole as wd.OLEFormat = doc.InlineShapes(1).OLEFormat
Dim progID as String = ole.ProgID
'it won't work without activating the Ole-Object first!!!
ole.Activate()
'just for security reasons. One can leave this away
If progID = "Excel.Sheet.8" Then
'cast the Ole-Object to an Excel.Workkook-Object
Dim wbk as xl.Workbook = CType(ole.Object, xl.Workbook)
'get a reference to the first sheet
Dim sht as xl.Worksheet = CType(wbk.Worksheets[1], xl.Worksheet)
'get Cell "A1"
xl.Range rng = CType(sht.get_Range("A1", "A1"), xl.Range)
'show the value of "A1"
MessageBox.Show(rng.Value2.ToString())
End If

Cheers

Arne Janning
 
Dim EApp As New Excel.Application()
Dim wa As New Word.Application()
Dim source As String
Dim target As String

Private Sub GenerateTheReport()
Dim doc As New Word.Document()
Dim r As Excel.Range
Dim ws As New Excel.Worksheet()

EApp.Workbooks.Open(source)
EApp.AskToUpdateLinks = False
doc = wa.Documents.Add

Try
ws = CType(EApp.Worksheets("Blue info"), Excel.Worksheet)
'"Table_Blue" is a named range in 'Blue info' worksheet
'='Blue info'!$A$2:$B$12
r = CType(ws.Range("Table_Blue"), Excel.Range)
doc.Content.PasteSpecial(, r) <=========== Error here
"Type mismatch"
doc.SaveAs(target)
Catch
MsgBox(Err.Description)
Finally
wa.Documents.Close()
EApp.Application.Quit()
End Try
End Sub

I get an exception when trying to PasteSpecial the range r. I know the
reference is valid. Any thoughts?
 
Lisa,

This will take some time for me to port the necessary vba code to .NET to
acomplish this.

Please check Arne's response and see if it works for you. If not, email me
at (e-mail address removed) (remove no spam) and let me know this issue
is not resolved and I will write you up a project to deal with this issue.
 
Back
Top