In this article, we are delighted to offer you the way to convert Excel worksheet to native Word table via VBA.
Office files take in various formats. Now and then, you will run into the demand to convert file format from one to another. For example, conversion between Excel file and Word document is one of the most required. Actually, we’ve discussed this topic on one of our previous articles: 5 Easy Ways to Transfer Excel Tabular Data into Your Word
Today, there is a much quicker macro-way to extract Excel workbook to a Word document. And all contents in a worksheet will be converted to a native Word table.
Install and Run Word Macro
We will run macro in Word. Since a part of codes in the macro involves triggering Excel object, you have to add an object library reference.
- First and foremost, open a new blank Word document.
- And press “Alt+ F11” to open VBA editor in Word.
- Then click “Normal” on the left column.
- Next click “Insert” and choose “Module”.
- Now you have got a new module. Double click on it as to open.
- Then click “Tools” tab on the menu bar.
- Choose “References” on the drop-down menu.
- In the “References-Normal” window, check “Microsoft Excel 14.0 Object Library” box and click “OK”.
- Next copy and paste following codes into the new module:
Sub ExtractWorksheetsToWordDocument() Dim objExcel As Excel.Application Dim objWorkbook As Excel.Workbook Dim objWorksheet As Excel.Worksheet Dim objTable As Table Dim dlgFile As FileDialog Dim strFileName As String Application.ScreenUpdating = False ' Select an Excel file from Browse window. Set dlgFile = Application.FileDialog(msoFileDialogFilePicker) With dlgFile If .Show = -1 Then strFileName = .SelectedItems(1) Else MsgBox "No file is selected! Please select the target file." Exit Sub End If End With Set objExcel = CreateObject("Excel.Application") Set objWorkbook = objExcel.Workbooks.Open(strFileName) objExcel.Visible = False ' Step through each worksheet in the Excel file and extract data to each _ individual section in Word document. For Each objWorksheet In ActiveWorkbook.Worksheets objWorksheet.UsedRange.Copy ActiveDocument.Paragraphs(ActiveDocument.Paragraphs.Count).Range.Paste ActiveDocument.Range.InsertAfter objWorksheet.Name If Not objWorksheet.Name = Worksheets(Worksheets.Count).Name Then With ActiveDocument.Paragraphs(ActiveDocument.Paragraphs.Count).Range .Collapse Direction:=wdCollapseEnd .InsertBreak Type:=wdSectionBreakNextPage End With End If Next objWorksheet For Each objTable In ActiveDocument.Tables objTable.Borders.Enable = True Next objTable ' Close the Excel application using the Quit method. objExcel.Application.Quit ' Release the object variables. Set objWorksheet = Nothing Set objWorkbook = Nothing Set objExcel = Nothing Application.ScreenUpdating = True End Sub
- Click “Run” or hit “F5”.
- Now there is the “Browse” window. Just select an Excel file and click “OK”.
Here is the outcome:
Make Sure Your Documents Still Works
There are possibly hundreds of documents stored on your computer. As time goes by, it’s easily to forget some of them. And when you remember checking them at some point, you only find them inaccessible. Then you should immediately resort to a tool to repair doc.
Vera Chen is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including fix xlsx and pdf repair software products. For more information visit www.datanumen.com