Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: Excel to Word

  1. #1
    New Member
    Join Date
    Jul 2013
    Post Thanks / Like
    0 Post(s)
    0 Thread(s)

    Default Excel to Word

    I have a macro code that copies a table in an excel file and paste to a specific word document. The excel table is basically a balance sheet which should be updated to word for reporting. Thus, the excel table gonna change whenever I make changes in books. The macro works fine when the word document does not have any tables and it inserts the relevant table. I need a revision in the code to identify whether a table with the same name exists and if it does, I need only edition in the table rather than pasting afresh from excel. Following is the code:

    Sub ExcelRangeToWord()

    Dim tbl As Excel.Range
    Dim WordApp As Word.Application
    Dim myDoc As Word.Document
    Dim WordTable As Word.Table

    Application.ScreenUpdating = False
    Application.EnableEvents = False

    Set tbl = ThisWorkbook.Worksheets("B.S").ListObjects("BalanceSheet").Range

    On Error Resume Next

    Set WordApp = GetObject(Class:="Word.Application")


    If WordApp Is Nothing Then Set WordApp = CreateObject(Class:="Word.Application")

    If Err.Number = 429 Then
    MsgBox "Microsoft Word could not be found, aborting."
    GoTo EndRoutine
    End If

    On Error GoTo 0

    Set myDoc = WordApp.Documents.Open("D:\Formats\Prototype.docx")


    myDoc.Paragraphs(1).Range.PasteExcelTable _
    LinkedToExcel:=False, _
    WordFormatting:=False, _

    Set WordTable = myDoc.Tables(1)
    WordTable.AutoFitBehavior (wdAutoFitWindow)

    Excel.Application.Visible = True

    Application.ScreenUpdating = True
    Application.EnableEvents = True

    Application.CutCopyMode = False

    End Sub

  2. #2
    Board Regular NdNoviceHlp's Avatar
    Join Date
    Nov 2002
    Manitoba Canada
    Post Thanks / Like
    3 Post(s)
    0 Thread(s)

    Default Re: Excel to Word

    Please use code tags...
    If I understand correctly, your code works once and then doesn't work because U have more than 1 table. I think it's likely better to delete the previous table rather than update it if it exists. Something like this before the paste...
     If mydoc.tables.count = 1 then 
    end if
    Untested code and I've been wrong before. HTH. Dave

  3. #3
    Moderator Macropod's Avatar
    Join Date
    Aug 2007
    Canberra, Australia
    Post Thanks / Like
    4 Post(s)
    0 Thread(s)

    Default Re: Excel to Word

    Why not just use an OLE link between the Word document and the Excel workbook - no code required?

    To set up the link, select the relevant range in Excel and copy it. Then switch to Word, select the destination range, then use Paste Special, with the 'paste link' option and your preferred paste format.
    Paul Edstein
    [MS MVP - Word]

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts