Excel to Word

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

Thread: Excel to Word

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

    Default Excel to Word

     
    Guys,
    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")

    Err.Clear


    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")

    tbl.Copy


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




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

    EndRoutine:
    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
    Location
    Manitoba Canada
    Posts
    1,775
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    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...
    Code:
     If mydoc.tables.count = 1 then 
    mydoc.tables(1).delete
    end if
    Untested code and I've been wrong before. HTH. Dave

  3. #3
    Moderator Macropod's Avatar
    Join Date
    Aug 2007
    Location
    Canberra, Australia
    Posts
    2,540
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    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.
    Cheers
    Paul Edstein
    [MS MVP - Word]

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
  •  

 

 
DMCA.com