Using vbnet to link excel objects to word document

[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

[scorpion53061]

Re: Using vbnet to link excel objects to word document

> I'm able to open excel workbooks and word documents, but I can't seem to[color=blue]
> copy excel charts, named ranges, etc. to a word document.[/color]

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.

[Arne Janning]

Re: Using vbnet to link excel objects to word document

Lisa wrote:[color=blue]
> 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.[/color]

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.Offic e.Interop.Word
Imports xl = Microsoft.Offic e.Interop.Excel

'Start Word
Dim wdApp As New wd.ApplicationC lass
'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.Shee t.8"
'Location of the file
Dim FileName As Object = "C:\test.xl s"
'Embed, not link
Dim LinkToFile As Object = False
'Add OleObject
wdDoc.InlineSha pes.AddOLEObjec t(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.InlineShape s(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.Shee t.8" Then
'cast the Ole-Object to an Excel.Workkook-Object
Dim wbk as xl.Workbook = CType(ole.Objec t, xl.Workbook)
'get a reference to the first sheet
Dim sht as xl.Worksheet = CType(wbk.Works heets[1], xl.Worksheet)
'get Cell "A1"
xl.Range rng = CType(sht.get_R ange("A1", "A1"), xl.Range)
'show the value of "A1"
MessageBox.Show (rng.Value2.ToS tring())
End If

Cheers

Arne Janning

[Lisa]

Re: Using vbnet to link excel objects to word document

Dim EApp As New Excel.Applicati on()
Dim wa As New Word.Applicatio n()
Dim source As String
Dim target As String

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

EApp.Workbooks. Open(source)
EApp.AskToUpdat eLinks = False
doc = wa.Documents.Ad d

Try
ws = CType(EApp.Work sheets("Blue info"), Excel.Worksheet )
'"Table_Blue " is a named range in 'Blue info' worksheet
'='Blue info'!$A$2:$B$1 2
r = CType(ws.Range( "Table_Blue "), Excel.Range)
doc.Content.Pas teSpecial(, r) <=========== Error here
"Type mismatch"
doc.SaveAs(targ et)
Catch
MsgBox(Err.Desc ription)
Finally
wa.Documents.Cl ose()
EApp.Applicatio n.Quit()
End Try
End Sub

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


"scorpion53 061" <scorpion_53061 @nospamhereever yahoo.com> wrote in message
news:OA5WiV%23R EHA.3528@TK2MSF TNGP09.phx.gbl. ..[color=blue][color=green]
> > 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.[/color]
>
> 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.
>
>[/color]

[scorpion53061]

Re: Using vbnet to link excel objects to word document

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 admin@nospamkjm solutions.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" <spamme@herplac e.com> wrote in message
news:x4WdnXHTDL jHAiHdRVn-jg@eatel.net...[color=blue]
> Dim EApp As New Excel.Applicati on()
> Dim wa As New Word.Applicatio n()
> Dim source As String
> Dim target As String
>
> Private Sub GenerateTheRepo rt()
> Dim doc As New Word.Document()
> Dim r As Excel.Range
> Dim ws As New Excel.Worksheet ()
>
> EApp.Workbooks. Open(source)
> EApp.AskToUpdat eLinks = False
> doc = wa.Documents.Ad d
>
> Try
> ws = CType(EApp.Work sheets("Blue info"), Excel.Worksheet )
> '"Table_Blue " is a named range in 'Blue info' worksheet
> '='Blue info'!$A$2:$B$1 2
> r = CType(ws.Range( "Table_Blue "), Excel.Range)
> doc.Content.Pas teSpecial(, r) <=========== Error here
> "Type mismatch"
> doc.SaveAs(targ et)
> Catch
> MsgBox(Err.Desc ription)
> Finally
> wa.Documents.Cl ose()
> EApp.Applicatio n.Quit()
> End Try
> End Sub
>
> I get an exception when trying to PasteSpecial the range r. I know the
> reference is valid. Any thoughts?
>
>
> "scorpion53 061" <scorpion_53061 @nospamhereever yahoo.com> wrote in message
> news:OA5WiV%23R EHA.3528@TK2MSF TNGP09.phx.gbl. ..[color=green][color=darkred]
> > > I'm able to open excel workbooks and word documents, but I can't seem[/color][/color][/color]
to[color=blue][color=green][color=darkred]
> > > copy excel charts, named ranges, etc. to a word document.[/color]
> >
> > Can I see the code you are using to copy the excel object to the[/color][/color]
clipboard[color=blue][color=green]
> > please?
> >
> > It is very difficult to diagnose without this.
> >
> >[/color]
>
>[/color]