Excel to Word

Jishnu Surendran

New Member
Joined
Jul 29, 2013
Messages
32
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
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,554
Messages
6,114,280
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top