Batch Exporting Excel Tables to a Word Document

[sean.healey]

I have a large Excel document with about 50 tabs. On each tab is a
formatted table with data.

I would like to export all of these tables into a Word document
without having to hit "Copy" and "Paste" 50 times. Is there a way for
me to automatically export all of these tables into Word?

Thank you!!!!

 

[macropod]

Hi Sean,

One way to expedite the process is with a macro. It could be in either your Word Document or your Excel Workbook and, although the
code would execute in much the same fashion, you'd need different code depending on which app does the automation.

Another way, if your data ranges in Excel are named, is to Copy one such range in Excel, then use Edit|Paste Special|Link in Word
(choose whatever format you want). In Word you could then:
.. press Alt-F9 to expose the field coding
.. copy & paste the link field as many times as needed
.. edit the named ranges in the field copies
.. press F9 to update the links, then Alt-F9 again to toggle the field code display off.
At this point, your Word and Excel files are linked so that any changes in the Excel file will be reflected in Word. You may or may
not want that. If not, select the linked objects and press Ctrl-Shift-F9 to break the links.

Cheers

 

[Sean]

Thanks, macropod.

I don't think linking to the Excel document will work because I just want to
send the Word documents to a client. Having to also send the Excel workbooks
will be too much of a hassle for this technically-disabled client.

Any idea on how I do this with a macro? I know a little bit about macros but
have not done much work with them and have never created my own before. Any
guidance you could give me would be appreciated.

Thanks!

 

[macropod]

Hi Sean,

The following Excel macro copies Range "A1:J10" on the first worksheet as an OLE object to a bookmark named "xlTbl" in a Word
document named "C:\My Documents\MyFile.doc", and leaves the document active & visible:

Sub ExportToDoc()
Dim wdApp As Word.Application
Dim WdDoc As String
'Establish link to Word
WdDoc = "C:\My Documents\MyFile.doc"
If Dir(WdDoc) <> "" Then
Set wdApp = New Word.Application
wdApp.Visible = True
With wdApp
'open the Word Document
Documents.Open Filename:=WdDoc
With wdApp
Dim BmkNm As String
BmkNm = "xlTbl"
With ActiveDocument
If .Bookmarks.Exists(BmkNm) Then
'Copy the Excel range
ActiveWorkbook.Sheets(1).Range("A1:J10").Copy
.Bookmarks(BmkNm).Range.PasteSpecial Link:=False, _
DataType:=wdPasteOLEObject, _
Placement:=wdInLine, DisplayAsIcon:=False
.Save
Else
MsgBox "Bookmark: " & BmkNm & " not found."
End If
End With
End With
End With
Else
MsgBox "File: " & WdDoc & " not found."
End If
Set wdApp = Nothing
End Sub

It's not clear from your post whether you want an embedded worksheet, or a table. You can change the data type to suit. I'll leave
it to you to add looping to process all sheets and destinations in the Word document.

Cheers

 

[macropod]

PS: You'll need to add a reference to Microsoft Word via Tools|References in the Visual Basic Editor.
Also if you name the ranges in Excel, and use the same names for the bookmarks in Word, that will greatly simplify the coding for
the loop. For example, if the bookmarks are named 'Table1' - 'Table9' and the corresponding range in Excel are also named 'Table1' -
'Table9', then instead of:

Dim BmkNm As String
BmkNm = "xlTbl"
With ActiveDocument
If .Bookmarks.Exists(BmkNm) Then
'Copy the Excel range
ActiveWorkbook.Sheets(1).Range("A1:J10").Copy
.Bookmarks(BmkNm).Range.PasteSpecial Link:=False, _
DataType:=wdPasteOLEObject, _
Placement:=wdInLine, DisplayAsIcon:=False
.Save
Else
MsgBox "Bookmark: " & BmkNm & " not found."
End If
End With

you could use:

With ActiveDocument
Dim i as Integer
For i = 1 to 9
'Copy the Excel range
ActiveWorkbook.Range("Table"&i).Copy
.Bookmarks("Table"&i).Range.PasteSpecial Link:=False, _
DataType:=wdPasteOLEObject, _
Placement:=wdInLine, DisplayAsIcon:=False
Next i
.Save
End With

This does the looping I referred to.

Cheers

 

[Doug Robbins - Word MVP]

If after creating the link, you select the data that is linked and then use
Ctrl+Shift+F9, the data will be converted to ordinary text and the link to
the Excel sheet is dispensed with.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP