Word VBA Code

[chandrashekar]

Hi,

am trying to paste excel graph to word using vbacode(using word) but I need to paste it in particular positions. May I know how to do it?

Please find below code which am using now.

Sub excel2word()

    
    Dim oExcel As Excel.Application
    Dim oWB As Workbook
    Set oExcel = New Excel.Application
    Set oWB = oExcel.Workbooks.Open("C:\Users\Chandra.char\Desktop\Test\VR.xls")
    oExcel.Visible = True
    oWB.Sheets("ViolationCount_Week").Select
    

    Set Chart = oWB.Sheets("ViolationCount_Week").ChartObjects("Chart 6")
    Chart.Copy
    'Chart.ChartArea.Copy
    With ActiveDocument.Sections(1).Range
       .PasteSpecial Link:=False, DataType:=wdPasteBitmap, _
            Placement:=wdInLine, DisplayAsIcon:=False
    End With
    
    Set oExcel = Nothing
    Set oWB = Nothing
    Set Chart = Nothing
End Sub

 

[Macropod]

Where are you trying to paste the chart? At the moment, it pastes over whatever is in Section 1 of the document.

 

[chandrashekar]

Hi,

am trying to paste it from excel to word but here I want to paste it at some particular location is it possible to assign to variable?


Regards,

Chandru

 

[Macropod]

I want to paste it at some particular location is it possible to assign to variable?

How about telling us how that location might be identified (e.g. via a bookmark, a content control, a table, a whole Section, a particular paragraph in a Section)?

 

[chandrashekar]

Hi,

Could you let me know which one would be better as I am doing it first time automating word using excel.


Regards,

Chandru

 

[Macropod]

What's best depends on your intention and context.

Bookmarks are probably the simplest to work with, but they're also easily deleted by inept users, so you might not want to use them in a document that people are likely to be editing before the macro is run.

A picture content control could be place anywhere in the document, have its 'cannot be deleted, property set and given a title. Your code could then reference the content control in much the same way as a bookmark.

Tables can be used in much the same way as bookmarks & content controls.

You could use a whole Section. Simply insert Section breaks either side of where you want the chart to go an reference that Section with the code you already have (i.e. replace the 1 in With ActiveDocument.Sections(1).Range). However, that's no good if you want your text to wrap around the chart.

You should experiment with the various options manually, then decide on which method best meets your automation needs.

 

[chandrashekar]

Hello,

Thanks for explaining it. Could you please share any example for a picture content method.


Regards,

Chandru

 

[Macropod]

The code for that might be as simple as:

Sub ExcelToWord()
Dim xlApp As New Excel.Application, xlWkBk As Excel.Workbook
Set xlWkBk = xlApp.Workbooks.Open("C:\Users\Chandra.char\Desktop\Test\VR.xls")
xlApp.Visible = False
xlWkBk.Sheets("ViolationCount_Week").ChartObjects("Chart 6").Copy
ActiveDocument.SelectContentControlsByTitle("Chart")(1).Range.Paste
xlWkBk.Close False: xlApp.Quit
Set xlWkBk = Nothing: Set xlApp = Nothing
End Sub

Where the content control's title is 'Chart'. Note that the content control must exist in the document before running the macro.

 

[chandrashekar]

Hello,

Thanks for the code let me try and come back to you..


Regards,

Chandru

 

[chandrashekar]

Hello,

I am getting error in The requested member of the collection does not exist in line
ActiveDocument.SelectContentControlsByTitle("Chart")(1).Range.Paste

Could you please help me on this.

Thanks in advance.

Regards,

Chandru