Excel VBAを利用してデータベースの新しいテーブルを作成する方法

この記事では、Excel VBAを使用して、Excelのデータを基にデータベースの新しいテーブルを作成する方法について詳しく解説します。具体的なコード例とその解説、さらに応用例を3つを取り上げます。この知識を利用することで、日常業務をより効率化することができるでしょう。

Excel VBAとデータベースの基礎知識

VBA (Visual Basic for Applications)は、Microsoft Office製品を自動化するためのプログラム言語です。Excel VBAを使って、Excelのデータを操作し、データベースに送信することができます。

データベースは、大量の情報を整理し、保存・取得するシステムです。今回は、Excelのデータを利用してデータベースの新しいテーブルを作成する方法を学びます。

Excelデータをデータベースに変換する基本的なコード

以下のコードは、Excelのデータを取得し、データベースに新しいテーブルを作成する基本的なものです。

Sub CreateTableFromExcel()
    Dim conn As Object
    Dim strQuery As String
    Dim lastRow As Long
    Dim lastCol As Long
    Dim colName As String
    Dim i As Long

    ' データベース接続の設定
    Set conn = CreateObject("ADODB.Connection")
    conn.Open "YOUR_DATABASE_CONNECTION_STRING"

    ' Excelのデータ範囲を取得
    lastRow = ThisWorkbook.Sheets("Sheet1").Cells(ThisWorkbook.Sheets("Sheet1").Rows.Count, 1).End(xlUp).Row
    lastCol = ThisWorkbook.Sheets("Sheet1").Cells(1, ThisWorkbook.Sheets("Sheet1").Columns.Count).End(xlToLeft).Column

    ' SQLクエリの作成
    strQuery = "CREATE TABLE NewTable ("

    For i = 1 To lastCol
        colName = ThisWorkbook.Sheets("Sheet1").Cells(1, i).Value
        strQuery = strQuery & colName & " VARCHAR(255), "
    Next i

    strQuery = Left(strQuery, Len(strQuery) - 2) & ")"

    ' テーブルの作成
    conn.Execute strQuery

    ' 接続を閉じる
    conn.Close
    Set conn = Nothing
End Sub

このコードは、Excelの最初の行にカラム名が書かれている前提で、データベースに新しいテーブルを作成します。

コードの解説

1. ADODB.Connectionオブジェクトを作成し、データベースに接続します。
2. Excelのデータ範囲を取得します。
3. SQLクエリを組み立て、新しいテーブルを作成します。
4. データベース接続を閉じます。

応用例

1. データ型を指定してテーブルを作成

デフォルトでは、すべてのカラムのデータ型がVARCHAR(255)として設定されています。しかし、実際のニーズに応じてデータ型を変更したい場合もあるでしょう。

' ... [中略]
    strQuery = "CREATE TABLE NewTable ("

    For i = 1 To lastCol
        colName = ThisWorkbook.Sheets("Sheet1").Cells(1, i).Value
        If IsNumeric(ThisWorkbook.Sheets("Sheet1").Cells(2, i).Value) Then
            strQuery = strQuery & colName & " INT, "
        Else
            strQuery = strQuery & colName & " VARCHAR(255), "
        End If
    Next i
' ... [中略]

2. データも一緒にインサート

テーブル作成後、データも一緒にインサートする例です。

' ... [中略]
    ' データのインサート
    For row = 2 To lastRow
        strQuery = "INSERT INTO NewTable VALUES ("
        For col = 1 To lastCol
            strQuery = strQuery & "'" & ThisWorkbook.Sheets("Sheet1").Cells(row, col).Value & "',"
        Next col
        strQuery = Left(strQuery, Len(strQuery) - 1) & ")"
        conn.Execute strQuery
    Next row
' ... [中略]

3. テーブル名を動的に変更

作成するテーブル名を動的に変更する例です。

Dim tableName As String
tableName = InputBox("テーブル名を入力してください")
' ... [中略]
    strQuery = "CREATE TABLE " & tableName & " ("
' ... [中略]

まとめ

Excel VBAを使って、Excelのデータを基にデータベースの新しいテーブルを作成する方法について詳しく解説しました。このテクニックを利用することで、日常業務の自動化やデ

ータの整理がより効率的に行えるようになります。今後もVBAの知識を深め、さまざまな業務プロセスを効率化していきましょう。