Excel to Word

[Jishnu Surendran]

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

 

[NdNoviceHlp]

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 
mydoc.tables(1).delete
end if

Untested code and I've been wrong before. HTH. Dave

 

[Macropod]

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.