By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,263 Members | 1,508 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,263 IT Pros & Developers. It's quick & easy.

Using vbnet to link excel objects to word document

P: n/a
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
Nov 20 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
> 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.


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.
Nov 20 '05 #2

P: n/a
Lisa wrote:
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.


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/de...dOLEObject.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
Nov 20 '05 #3

P: n/a
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?
"scorpion53061" <sc************@nospamhereeveryahoo.com> wrote in message
news:OA****************@TK2MSFTNGP09.phx.gbl...
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.


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.

Nov 20 '05 #4

P: n/a
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 ad***@nospamkjmsolutions.com (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.

"Lisa" <sp****@herplace.com> wrote in message
news:x4********************@eatel.net...
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?
"scorpion53061" <sc************@nospamhereeveryahoo.com> wrote in message
news:OA****************@TK2MSFTNGP09.phx.gbl...
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.


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.


Nov 20 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.