ALL BUSINESS MICROSOFT EXCEL TIPS

How to extract formatted text from a Word document, convert it into HTML and paste the resulting HTML-formatted text into a specified Excel cell

Explanation of the VBA Code: “CopyHTMLFromWordToExcel_WithFilePicker”

CopyHTMLFromWordToExcel_WithFilePicker

This VBA macro automates the process of extracting formatted text from a Word document, converting it into HTML, and pasting the resulting HTML-formatted text into a specified Excel cell.

Excel file: sqlookup_examples.xlsm

Word file: word_file_to_copy_2.docx

Word file to copy 2

View the VBA macro code in txt format


What the Code Does

  1. File Selection:
    • The macro opens a file dialog to let the user select a Word document (*.doc or *.docx).
    • If the user does not select a file, the macro exits.
  2. Temporary File Handling:
    • The selected Word file is copied to a temporary location. This ensures the original file remains unaffected during processing.
  3. Word Application Setup:
    • A new instance of Microsoft Word is created and runs in the background (invisible mode).
    • The Word document is opened in read-only mode.
  4. HTML Conversion:
    • The macro processes the content of the Word document paragraph by paragraph.
    • Each paragraph is analyzed for:
      • Lists: The macro detects ordered (numbered) and unordered (bulleted or square) lists and converts them into appropriate HTML tags (<ol>, <ul>, <li>).
      • Text Formatting: Bold, italic, and underlined text is converted to corresponding HTML tags (<strong>, <em>, <u>).
    • The resulting HTML is stored in a string variable (htmlFormattedText).
  5. Insert HTML into Excel:
    • The HTML-formatted string is pasted into a specified cell in Excel (R4 by default).
  6. Cleanup:
    • The Word document is closed, and the Word application is quit.
    • The temporary file is deleted.
    • All objects are released to free up memory.
  7. User Notification:
    • A message box informs the user that the operation is complete and displays the cell where the HTML has been pasted.

Key Features

  1. Handles Lists:
    • Supports nested lists with levels.
    • Differentiates between ordered (<ol>) and unordered lists (<ul>, including square styles).
  2. Text Formatting:
    • Applies HTML tags to represent bold, italic, and underlined text.
  3. Preserves Formatting:
    • Converts Word document formatting into equivalent HTML.
  4. User Interaction:
    • Lets the user select the Word file interactively via a file picker.

Practical Use Case

This macro is useful for:

  • Developers, content creators, or anyone who needs to transfer content from Word to Excel while preserving formatting in HTML.
  • Preparing content for web applications or databases that require HTML input.

How to Use the Macro

  1. Open Excel and access the VBA editor (Alt + F11).
  2. Insert the code into a new module.
  3. Run the macro (F5).
  4. Select a Word document when prompted.
  5. The formatted HTML text will appear in cell R4 (or another specified cell) of the active sheet.

Example Input and Output

Input (Word Document):

1. Introduction
   a. Bold text example.
   b. Italic text example.

- Bullet point
  - Nested bullet point

Output (HTML in Excel):

<ol>
  <li>Introduction
    <ul>
      <li><strong>Bold text example.</strong></li>
      <li><em>Italic text example.</em></li>
    </ul>
  </li>
</ol>
<ul>
  <li>Bullet point
    <ul>
      <li>Nested bullet point</li>
    </ul>
  </li>
</ul>

Let me know if you need further clarifications or enhancements to the macro!

Views: 5

Comments are closed.

Pin It