この記事では、SQLのSELECT文を使ってシートにある表の値の型を特定する方法についてご説明します。
【動画】SQLのSELECT文を使ってシートにある表の値の型を特定する実際の動き
本題に入る前に、まずは次の動画をご覧ください。
まずはマクロが自分自身のExcelファイルに接続して、SQLのSELECT文を使ってシートにある表のデータを取得します。
SELECT文を実行すると、recordsetから各列の型を取得することができます。
マクロ作成の流れ
Excelファイルの例
今回は次のExcelファイルを用意しました。
ExcelファイルにはセルB4からF11のセルの表があり、この表の列すべての型を右の黄色のセルに書き出します。
書き出した結果は下の通りです。
マクロを実行すると全列の型がセルに出力されます。
コードの例
Option Explicit
Private Sub btn_exec_Click()
Dim ws As Worksheet 'シート用変数
Dim adodbCon As ADODB.Connection 'Connection用変数
Dim rs As ADODB.Recordset 'レコードセット用変数
Dim sqlStr As String 'SQL文用変数
Dim field As ADODB.field '列
Dim cnt As Long 'カウンタ
Const rowPos As Long = 5 'データの開始行位置
'シートを取得する
Set ws = Worksheets("top")
'モジュールに関する各情報を出力するセルをクリアする
Worksheets("top").Range("H" & rowPos & ":I" & 100).ClearContents
'Connectionインスタンスの生成
Set adodbCon = New ADODB.Connection
With adodbCon
'接続情報の取得(自分自身のExcelファイルに接続する)
.Provider = "Microsoft.ACE.OLEDB.12.0"
.ConnectionString = "Data Source = " & ThisWorkbook.FullName & _
";Extended Properties =Excel 12.0;"
'データソースへの接続を開く
.Open
End With
'データの型を調べるため、1件だけデータを取得する
sqlStr = "SELECT TOP 1 * FROM " & " [" & ws.Name & "$B4:F11]"
'Recordsetオブジェクトのインスタンスを生成する
Set rs = New ADODB.Recordset
'カーソルタイプにキーセットカーソル使用
rs.CursorType = adOpenDynamic
'SELECT文を実行してRecordsetを開く
rs.Open sqlStr, adodbCon, adOpenStatic
'フィールドの型を取得する
For Each field In rs.Fields
'検索項目に入力された列名と、表の列名が一致した場合
Select Case field.Type
Case adVarWChar
'「検索項目」に入力された項目のデータが文字列の場合
ws.Range("H" & (rowPos + cnt)).Value = field.Name
ws.Range("I" & (rowPos + cnt)).Value = "文字列型"
Case adDouble
'「検索項目」に入力された項目のデータが数値型の場合(倍精度浮動小数点値)
ws.Range("H" & (rowPos + cnt)).Value = field.Name
ws.Range("I" & (rowPos + cnt)).Value = "数値型"
Case adDate
'「検索項目」に入力された項目のデータが日付型の場合
ws.Range("H" & (rowPos + cnt)).Value = field.Name
ws.Range("I" & (rowPos + cnt)).Value = "日付型"
Case adArray
'※
Case adBigInt
'※
Case adBinary
'※
Case adBoolean
'※
Case adBSTR
'※
Case adChapter
'※
Case adChar
'※
Case adCurrency
'※
Case adDBDate
'※
Case adDBTime
'※
Case adDBTimeStamp
'※
Case adDecimal
'※
Case adEmpty
'※
Case adError
'※
Case adFileTime
'※
Case adGUID
'※
Case adIDispatch
'※
Case adInteger
'※
Case adIUnknown
'※
Case adLongVarBinary
'※
Case adLongVarChar
'※
Case adLongVarWChar
'※
Case adNumeric
'※
Case adPropVariant
'※
Case adSingle
'※
Case adSmallInt
'※
Case adTinyInt
'※
Case adUnsignedBigInt
'※
Case adUnsignedInt
'※
Case adUnsignedSmallInt
'※
Case adUnsignedTinyInt
'※
Case adUserDefined
'※
Case adVarBinary
'※
Case adVarChar
'※
Case adVariant
'※
Case adVarNumeric
'※
Case adWChar
'※
Case Else
End Select
cnt = cnt + 1
Next field
'後処理
'recordsetを閉じる
rs.Close
'Connectionインスタンスを閉じる
adodbCon.Close
Set rs = Nothing
Set adodbCon = Nothing
End Sub
注目すべきコード①
最初に見て頂きたいのは21行目から33行目です。
'Connectionインスタンスの生成
Set adodbCon = New ADODB.Connection
With adodbCon
'接続情報の取得(自分自身のExcelファイルに接続する)
.Provider = "Microsoft.ACE.OLEDB.12.0"
.ConnectionString = "Data Source = " & ThisWorkbook.FullName & _
";Extended Properties =Excel 12.0;"
'データソースへの接続を開く
.Open
End With
コードの説明
以上のコードは、マクロが自分自身のExcelファイルに接続するための接続情報を取得して接続するコードです。
コードの詳細
21行目のコードでは、Connectionインスタンスを生成します。
このインスタンスがないとマクロが自分自身のExcelファイルに接続することができないので必ず生成しておきます。
26行目から28行目で接続情報を取得し、31行目でOpenメソッドを実行してマクロが自分自身のExcelファイルに接続します。
注目すべきコード②
次に見て頂きたいのは36行目から45行目です。
'データの型を調べるため、1件だけデータを取得する
sqlStr = "SELECT TOP 1 * FROM " & " [" & ws.Name & "$B4:F11]"
'Recordsetオブジェクトのインスタンスを生成する
Set rs = New ADODB.Recordset
'カーソルタイプにキーセットカーソル使用
rs.CursorType = adOpenDynamic
'SELECT文を実行してRecordsetを開く
rs.Open sqlStr, adodbCon, adOpenStatic
コードの説明
以上のコードは、シートにある表の列名とその列の型を取得するために一度SELECT文を実行している処理のコードです。
列の型を取得するには、表に対して一度SELECT文を実行する必要があります。(SELECT文を実行せずに、列の型だけを直接取得することはできないため)
SELECT文を実行すると、recordsetにデータが取得されて、その中のTypeプロパティから列の型を取得することができます。
Typeプロパティから列の型を取得する処理は、「注目すべきコード③」でご説明します。
また、SELECT文「SELECT TOP 1 * FROM ” & ” [” & ws.Name & “$B4:F11]”」の「$B4:F11」は、表の範囲の指定です。
表の範囲がセルB4からセルF11なので、「$B4:F11」と記述しています。
この表の範囲を正しく指定しないと正しく表の情報を取得することができないので気をつけましょう。
コードの詳細
36行目のコードは、データのある表のシート名をSELECT文のFROM句に指定してSELECT文を実行しています。
データのある表のシート名をSELECT文のFROM句に指定してSELECT文を実行することで、列の型を取得することができるようになります。
なぜSELECT文に「TOP 1」と記述しているのかというと、SELECT文の実行完了に時間をかけたくないからです。
(※もしシートに何万件もデータがあったら無駄に時間がかかってしまいます)
「TOP 1」だと1件だけのデータ抽出になるので無駄に処理時間をかけなくて済みます。
あくまで列の型が欲しくてSELECTを実行しているので、とりあえず今回は「TOP 1」を記述しています。
39行目のコードではRecordsetオブジェクトのインスタンスを生成し、42行目でカーソルタイプにキーセットカーソル使用します。
45行目のコードでは、36行目のSELECT文を実行します。
注目すべきコード③
次に見て頂きたいのは48行目から73行目です。
'フィールドの型を取得する
For Each field In rs.Fields
'検索項目に入力された列名と、表の列名が一致した場合
Select Case field.Type
Case adVarWChar
'「検索項目」に入力された項目のデータが文字列の場合
ws.Range("H" & (rowPos + cnt)).Value = field.Name
ws.Range("I" & (rowPos + cnt)).Value = "文字列型"
Case adDouble
'「検索項目」に入力された項目のデータが数値型の場合(倍精度浮動小数点値)
ws.Range("H" & (rowPos + cnt)).Value = field.Name
ws.Range("I" & (rowPos + cnt)).Value = "数値型"
Case adDate
'「検索項目」に入力された項目のデータが日付型の場合
ws.Range("H" & (rowPos + cnt)).Value = field.Name
ws.Range("I" & (rowPos + cnt)).Value = "日付型"
コードの説明
以上のコードは、表の列の型がどの型なのかを判定し、特定できた型をセルに出力する処理のコードです。
「文字列型」「数値型」「日付型」の型をそれぞれセルに出力しています。
ただし、列の型には「文字列型」「数値型」「日付型」を含めて40通りあります。
40通りはかなり数が多いので、今回は代表的な「文字列型」「数値型」「日付型」の場合だけ処理を記述しており、他の型については処理を省略しています。(75行目から221行目の部分)
もし40通りの全てを詳しく知りたい場合は下のページを参考にして、「文字列型」「数値型」「日付型」のコードを参考に、他の型についても改修してみてください。
参考 40通りの型についてDataTypeEnumコードの詳細
48行目のコードは、表の列の数だけ処理を繰り返すループのFor文です。
52行目のコードは、表の列の型を判定するSelect Case文で、表の列の型によってそれぞれ処理を行います。
54行目のコードは、表の列の型が「adVarWChar」(文字列)が条件のCaseで、「adVarWChar」の場合は58行目でH列のセルに列名を、59行目でI列のセルに「文字列型」の文言を出力します。
61行目のコードは、表の列の型が「adDouble」(倍精度浮動小数点値)が条件のCaseで、「adDouble」の場合は65行目でH列のセルに列名を、66行目でI列のセルに「数値型」の文言を出力します。
68行目のコードは、表の列の型が「adDate」(日付)が条件のCaseで、「adDate」の場合は72行目でH列のセルに列名を、73行目でI列のセルに「日付型」の文言を出力します。
注目すべきコード④
次に見て頂きたいのは75行目から221行目です。
Case adArray
'※
Case adBigInt
'※
Case adBinary
'※
Case adBoolean
'※
Case adBSTR
'※
Case adChapter
'※
Case adChar
'※
Case adCurrency
'※
Case adDBDate
'※
Case adDBTime
'※
Case adDBTimeStamp
'※
Case adDecimal
'※
Case adEmpty
'※
Case adError
'※
Case adFileTime
'※
Case adGUID
'※
Case adIDispatch
'※
Case adInteger
'※
Case adIUnknown
'※
Case adLongVarBinary
'※
Case adLongVarChar
'※
Case adLongVarWChar
'※
Case adNumeric
'※
Case adPropVariant
'※
Case adSingle
'※
Case adSmallInt
'※
Case adTinyInt
'※
Case adUnsignedBigInt
'※
Case adUnsignedInt
'※
Case adUnsignedSmallInt
'※
Case adUnsignedTinyInt
'※
Case adUserDefined
'※
Case adVarBinary
'※
Case adVarChar
'※
Case adVariant
'※
Case adVarNumeric
'※
Case adWChar
'※
コードの説明
以上のコードは、表の列の型がどの型なのかを判定する処理のコードです。
以上のコードには、各Caseの中に「’※」しか記述していません。
列の型は40種類あり数が多いので、今回は「注目すべきコード③」で説明した3つの型以外は処理を省いています。(「’※」の記述のみ)
もし3つの型以外にも必要な型がある場合は、40通りの型を詳しく説明している下のページを参考にして、「注目すべきコード③」の内容と同じように他の型についても必要な型だけ改修してみてください。(「’※」の部分にコードを追加してください)
参考 40通りの型についてDataTypeEnum動作確認
マクロ実行前
今回は以下のExcelファイルを用意しました。
マクロ実行後
マクロを実行すると、各列の型がすべて出力されました。
【注意】参照設定が必要です
一つ注意点があるのですが、先ほどのコードを動かすには参照設定が必要です。
参照設定の一覧(下の画像を参考)から次の項目(ライブラリ)にチェックを付けて「OK」ボタンをクリックします。
- Microsoft ActiveX Data Objects 2.8 Library(msado28.tlb)
なぜ必要かというと、Excelのマクロのコードの6行目の「ADODB.Connection」と7行目の「ADODB.Recordset」というオブジェクトが「msado28.tlb」というファイルを参照するからです。
Dim adodbCon As ADODB.Connection 'Connection用変数
Dim rs As ADODB.Recordset 'レコードセット用変数
この参照設定をしないと下の画像のエラーが出ますので必ず行う必要があります。
ここでは「msado28.tlb」とは何者かについては記事の本題から逸れてしまうので詳細は割愛しますが、マクロで「ADODB.Connection」「ADODB.Recordset」というオブジェクトを使う場合は参照設定しないと動かない、程度に思って頂ければと思います。
最後に
本記事では、SQLのSELECT文を使ってシートにある表の値の型を特定する方法についてご説明しました。
Excelのシートにある表の値の型を特定したい場合は本記事を参考にしてみてくださいね。
プログラミングのスキルを習得するなら
プログラミングのスキルを習得したい、今のスキルをもっと高めたい、そう考えているなら「プログラミングスクール」がおすすめです。
プログラミングのスキルの基礎を身につけるなら「TechAcademy」で1週間の無料体験があるので、これで「プログラミングの基礎」を学ぶのにおすすめですよ。
