Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.word.tables
|
|||
|
|||
Batch Exporting Excel Tables to a Word Document
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!!!! |
#2
Posted to microsoft.public.word.tables
|
|||
|
|||
Batch Exporting Excel Tables to a Word Document
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 -- macropod [MVP - Microsoft Word] ------------------------- wrote in message ups.com... 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!!!! |
#3
Posted to microsoft.public.word.tables
|
|||
|
|||
Batch Exporting Excel Tables to a Word Document
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" wrote: 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 -- macropod [MVP - Microsoft Word] ------------------------- |
#4
Posted to microsoft.public.word.tables
|
|||
|
|||
Batch Exporting Excel Tables to a Word Document
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 [MVP - Microsoft Word] ------------------------- "Sean" wrote in message ... 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" wrote: 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 -- macropod [MVP - Microsoft Word] ------------------------- |
#5
Posted to microsoft.public.word.tables
|
|||
|
|||
Batch Exporting Excel Tables to a Word Document
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 -- macropod [MVP - Microsoft Word] ------------------------- |
#6
Posted to microsoft.public.word.tables
|
|||
|
|||
Batch Exporting Excel Tables to a Word Document
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 "Sean" wrote in message ... 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" wrote: 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 -- macropod [MVP - Microsoft Word] ------------------------- |
Reply |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Form field v. Control Text box and Exporting info into Excel?? | Microsoft Word Help | |||
Exporting a Word document to PDF | Microsoft Word Help | |||
Copying excel tables into word document | Page Layout | |||
Exporting Word Form Data into Excel | Microsoft Word Help | |||
controlling tables in word linked to excel tables | Tables |