Convert an Excel file into a Word document

Objectives of the Code:

• Copy data from Excel (e.g., a range of cells) into Word.
• Format the content in Word.
• Generate a Word file from Excel.

VBA Code to Convert Excel to Word

Sub ConvertExcelToWord()
    ' Declare necessary objects
    Dim objWord As Object
    Dim objDoc As Object
    Dim ws As Worksheet
    Dim cell As Range
    Dim RangeToCopy As Range
    Dim i As Long, j As Long   
    ' Create a new Word application instance
    On Error Resume Next
    Set objWord = CreateObject("Word.Application")
    On Error GoTo 0   
    ' If Word is not opened, start it
    If objWord Is Nothing Then
        MsgBox "Word could not be launched", vbCritical
        Exit Sub
    End If   
    ' Make Word visible (optional)
    objWord.Visible = True  
    ' Create a new Word document
    Set objDoc = objWord.Documents.Add   
    ' Reference the current Excel worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Replace "Sheet1" with your sheet's name   
    ' Define the range of cells to copy (e.g., A1:B10)
    Set RangeToCopy = ws.Range("A1:B10") ' Replace with your desired range   
    ' Copy the range of cells into Word
    RangeToCopy.Copy   
    ' Paste the cells as a table in the Word document
    objDoc.Content.Paste   
    ' Optional: Format the table in Word
    With objDoc.Tables(1)
        .AutoFitBehavior (2) ' Automatically adjust column width
        .Style = "Table Grid" ' Apply a predefined table style
        .Rows.Alignment = 1 ' Align the rows to the center
    End With   
    ' Add a title to the Word document
    objDoc.Paragraphs.Add
    objDoc.Paragraphs.Last.Range.Text = "Table Exported from Excel"
    objDoc.Paragraphs.Last.Range.Style = "Heading 1"   
    ' Save the Word document
    Dim filePath As String
    filePath = Application.GetSaveAsFilename("C:\YourFolder\MyDocument.docx", "Word Files (*.docx), *.docx")   
    If filePath <> "False" Then
        objDoc.SaveAs filePath
        MsgBox "Word document saved successfully!", vbInformation
    Else
        MsgBox "Save canceled.", vbExclamation
    End If   
    ' Close Word
    objDoc.Close
    objWord.Quit   
    ' Release the memory
    Set objDoc = Nothing
    Set objWord = Nothing
End Sub

Detailed Explanation of the Code:

Creating a Word instance:

Set objWord = CreateObject("Word.Application")

This line creates a Word object using OLE Automation. If Word is already open, it uses the existing instance; otherwise, it starts Word.

Checking if Word is accessible:

If objWord Is Nothing Then
    MsgBox "Word could not be launched", vbCritical
    Exit Sub
End If

If Word can’t be started or found, it shows an error message and exits the procedure.

Creating a new Word document:

Set objDoc = objWord.Documents.Add

This line creates a new Word document where the Excel data will be pasted.

Referencing the Excel worksheet:

Set ws = ThisWorkbook.Sheets("Sheet1")

This line refers to the Excel worksheet that contains the data to be copied. Replace « Sheet1 » with the name of your sheet.

Defining the range of cells to copy:

Set RangeToCopy = ws.Range("A1:B10")

This defines the range of cells to be copied (e.g., A1:B10). You can change this to any range of your choice.

Copying the Excel cells to Word:

RangeToCopy.Copy
objDoc.Content.Paste

The Copy method copies the selected range in Excel, and the Paste method pastes this content into the Word document.

Formatting the table in Word:

With objDoc.Tables(1)
    .AutoFitBehavior (2) ' Automatically adjust column width
    .Style = "Table Grid" ' Apply a predefined table style
    .Rows.Alignment = 1 ' Align the rows to the center
End With

This block formats the pasted table. It automatically adjusts the column widths, applies a table style, and centers the rows.

Adding a title to the Word document:

objDoc.Paragraphs.Add
objDoc.Paragraphs.Last.Range.Text = "Table Exported from Excel"
objDoc.Paragraphs.Last.Range.Style = "Heading 1"

This section adds a title at the beginning of the Word document, indicating that the table was exported from Excel.

Saving the Word document:

filePath = Application.GetSaveAsFilename("C:\YourFolder\MyDocument.docx", "Word Files (*.docx), *.docx")
If filePath <> "False" Then
    objDoc.SaveAs filePath
    MsgBox "Word document saved successfully!", vbInformation
Else
    MsgBox "Save canceled.", vbExclamation
End If

This part opens a Save As dialog, allowing the user to choose where to save the Word document. If the user provides a valid location and filename, the document is saved.

Closing Word:

objDoc.Close
objWord.Quit

This closes the Word document and exits Word.

Releasing the objects:

Set objDoc = Nothing
Set objWord = Nothing

These lines release the memory occupied by the Word objects to avoid memory leaks.

Conclusion:

This VBA code allows you to copy a range of cells from Excel into a Word document, format the table in Word, and save the document. You can customize the code for different ranges of data or further tailor the Word document formatting as per your needs.