none
EXCEL VBA CODE TO EXPORT MY WORKSHEET TO MS WORD RRS feed

  • Question

  • CAN ANYONE PLEASE HELP ME WITH A COMPLETE EXCEL VBA CODE WHICH WILL EXPORT MY EXCEL DATA INTO WORD AND EVEN IF I EDIT THE DATA IN EXCEL ANOTHER TIME, IT WILL ALSO MAKE CHANGES IN THE THE WORD DOCUMENT I EXPORTED AS WELL. CAN ANYONE HELP ME OUT PLEASE. THANKS
    Wednesday, April 16, 2014 8:39 AM

Answers

  • Hi Charles,

    I would suggest you implementing it From Word side.

    We can insert a linked worksheet using inserting object feature.

    Here is VBA code:

    Sub InsertWorksheet()
        Dim s As InlineShape
        Set s = Selection.InlineShapes.AddOLEObject(ClassType:="Excel.Sheet.12", FileName _
            :="C:\Users\v-junhua\Desktop\1 - Copy.xlsx", LinkToFile:=True, _
            DisplayAsIcon:=False)
    End Sub
    

    An inserted worksheet means an inlineshape, we can update it using following code:

    Sub Updatelinkedsheet()
        Dim s As InlineShape
        Set s = ActiveDocument.InlineShapes(1)
        s.LinkFormat.Update
    End Sub 
    

    However, we don’t know when the linked worksheet would update, so if you want to auto update the linked worksheet, you could use Application.OnTime Method (Word) to update the linked worksheet at regular intervals.

    Hope this helps.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, April 17, 2014 5:01 AM
    Moderator
  • If you open both the Excel file and the Word document, you can:
    1. Copy the source range in Excel
    2. Switch to Word, select the destination, then use Paste Special with the 'paste link' option and the paste format of your choice.
    From then on, any changes in the copied Excel range will be reflected in the Word document.

    PS: Please don't type everything in capitals - it makes it hard to read and is like shouting at people.


    Cheers
    Paul Edstein
    [MS MVP - Word]

    Thursday, April 17, 2014 5:55 AM

All replies

  • Hi Charles,

    I would suggest you implementing it From Word side.

    We can insert a linked worksheet using inserting object feature.

    Here is VBA code:

    Sub InsertWorksheet()
        Dim s As InlineShape
        Set s = Selection.InlineShapes.AddOLEObject(ClassType:="Excel.Sheet.12", FileName _
            :="C:\Users\v-junhua\Desktop\1 - Copy.xlsx", LinkToFile:=True, _
            DisplayAsIcon:=False)
    End Sub
    

    An inserted worksheet means an inlineshape, we can update it using following code:

    Sub Updatelinkedsheet()
        Dim s As InlineShape
        Set s = ActiveDocument.InlineShapes(1)
        s.LinkFormat.Update
    End Sub 
    

    However, we don’t know when the linked worksheet would update, so if you want to auto update the linked worksheet, you could use Application.OnTime Method (Word) to update the linked worksheet at regular intervals.

    Hope this helps.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, April 17, 2014 5:01 AM
    Moderator
  • If you open both the Excel file and the Word document, you can:
    1. Copy the source range in Excel
    2. Switch to Word, select the destination, then use Paste Special with the 'paste link' option and the paste format of your choice.
    From then on, any changes in the copied Excel range will be reflected in the Word document.

    PS: Please don't type everything in capitals - it makes it hard to read and is like shouting at people.


    Cheers
    Paul Edstein
    [MS MVP - Word]

    Thursday, April 17, 2014 5:55 AM