HTMLのTABLEタグをExcelでスピル形式の表に変換するユーザー定義関数の作り方

ユーザー定義関数

2023.04.19

この記事では、HTMLのTABLEタグをExcelのスピル形式の表に変換するカスタム関数の作成方法を紹介します。この関数を使えば、ウェブページやデータベースから取得したHTMLテーブルを簡単にExcelで扱うことができます。

ただしこの機能は「スピル」を用いるため、対応しているMicrosoft 365のExcelもしくはExcel 2021以降でのみ利用可能です。

1. カスタム関数の準備
2. 関数の使用方法
3. 注意点と制限事項

1. カスタム関数の準備

まず、ExcelのVBAエディタを開き、新しい標準モジュールを作成します。次に、以下のコードをそのモジュールに貼り付けてください。

Function ConvertHTMLTableToSpillArray(htmlString As String)
    Dim xmlDoc As Object
    Dim xmlTable As Object
    Dim xmlRow As Object
    Dim xmlCell As Object
    Dim spillArray() As Variant
    Dim rowCount As Long, colCount As Long
    Dim i As Long, j As Long

    Set xmlDoc = CreateObject("htmlfile")
    xmlDoc.body.innerHTML = htmlString

    Set xmlTable = xmlDoc.getElementsByTagName("table")(0)

    rowCount = xmlTable.Rows.Length
    colCount = xmlTable.Rows(0).Cells.Length
    ReDim spillArray(0 To rowCount - 1, 0 To colCount - 1)

    i = 0
    For Each xmlRow In xmlTable.Rows
        j = 0
        For Each xmlCell In xmlRow.Cells
            spillArray(i, j) = xmlCell.innerText
            j = j + 1
        Next xmlCell
        i = i + 1
    Next xmlRow

    ConvertHTMLTableToSpillArray = spillArray
End Function

これで、カスタム関数「ConvertHTMLTableToSpillArray」の準備が完了です。

2. 関数の使用方法

例として、セルA1に以下のHTMLのtableタグが入力されているとします。

<table>
<thead>
<tr>
<th>＼</th>
<th>A</th>
<th>B</th>
<th>C</th>
</tr>
</thead>
<tbody>
<tr>
<th>1</th>
<td>商品名</td>
<td>価格</td>
<td>在庫数</td>
</tr>
<tr>
<th>2</th>
<td>商品A</td>
<td>1000</td>
<td>50</td>
</tr>
<tr>
<th>3</th>
<td>商品B</td>
<td>2000</td>
<td>30</td>
</tr>
<tr>
<th>4</th>
<td>商品C</td>
<td>1500</td>
<td>20</td>
</tr>
</tbody>
</table>

次に、適当な空白セル（例：B2）で以下のように入力し、エンターキーを押します。