How to Convert Excel Worksheet to Native Word Table via VBA

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.

1. First and foremost, open a new blank Word document.
2. And press “Alt+ F11” to open VBA editor in Word.
3. Then click “Normal” on the left column.
4. Next click “Insert” and choose “Module”.
5. Now you have got a new module. Double click on it as to open.
6. Then click “Tools” tab on the menu bar.
7. Choose “References” on the drop-down menu.
8. In the “References-Normal” window, check “Microsoft Excel 14.0 Object Library” box and click “OK”.
9. 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

10. Click “Run” or hit “F5”.
11. 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.

Author Introduction:

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