データベースとしてのExcelファイルの新規作成

カテゴリー名：　［ADOによるデータベースの新規作成］

　データベース操作のうち、新規作成とデータの書き込みを取り上げます。

　AccessよりExcelの方がなじみのある人が多いと思うので、まずはExcelファイルから取り上げます。

　Excelを動かすことなく VBScriptで操作します。

関連の Program Page

《このページの目次》

• 1.　Excelのxlsファイルへの書き込み
  • (1)　データベースを設定するための３要素
  • (2)　32bitか64bitかの確認
  • (3)　接続文字列(ConnectionString)
  • (4)　ADOの接続用オブジェクト
  • (5)　レコードセットの生成とオープン
  • (6)　カーソルタイプ、ロックタイプおよびオプション
  • (7)　データの書き込み
  • (8)　クローズによる締めくくり
• 2.　Excelのxlsxファイルへの書き込み

1.　Excelのxlsファイルへの書き込み

　データベースの解説は、「読む」から入るのが王道でしょうが、読むためのデータベースがないことには始まらないので「書く」からいきます。

　test.xls というファイルを作成します。

　テーブル名は Sheet1 です。

　「氏名」と「身長」の二つのフィールドからなるレコードを３人分登録します。

　解説すべきことがいろいろありますが、まずは VBScript を掲げます。解説は、その後に記します。

　これを実行すると test.xls が作成されます。既に test.xls が存在する場合は、それを削除した上で新たに作成します。

△　create_xls.vbs

 Option Explicit
 Dim DbName, TableName, TypeStr
 Dim FSO, DbPath, ShellObj, DriverStr
 Dim CN, ConnStr, RS, sql
 
 DbName = "test.xls"
 TableName = "Sheet1"
 TypeStr = "氏名 varchar,身長 float"
 
Set FSO = CreateObject("Scripting.FileSystemObject")
DbPath = FSO.GetAbsolutePathName(DbName)
If (FSO.FileExists(DbPath) = True) Then FSO.DeleteFile(DbPath)
Set FSO = Nothing

Set ShellObj = CreateObject("Wscript.Shell")
If ShellObj.Environment("Process").Item( _
        "PROCESSOR_ARCHITECTURE") = "x86" Then  ' 32bit版の場合
    DriverStr = "Provider=Microsoft.Jet.OLEDB.4.0;"
Else  ' 64bit版の場合
    DriverStr = "Provider=Microsoft.ACE.OLEDB.12.0;"
End If
Set ShellObj = Nothing
ConnStr = DriverStr & "Data Source=" & DbPath & ";" & _
    "Extended Properties=""Excel 8.0;HDR=Yes;"""

Set CN = CreateObject("ADODB.Connection")
CN.Open ConnStr
sql = "create table " & TableName & " (" & TypeStr & ");"
CN.Execute(sql)  ' テーブル(つまりワークシート)作成

Set RS = CreateObject("ADODB.Recordset")
sql = "select * from " & TableName & ";"
RS.Open sql,CN,0,2,1
RS.AddNew
    RS.Fields("氏名").Value = "鈴木xls"
    RS.Fields("身長").Value = 172.3
RS.Update
RS.AddNew
    RS.Fields(0).Value = "高橋xls"
    RS.Fields(1).Value = 168.5
RS.Update
RS.AddNew Array("氏名", "身長"), Array("田中xls", 183.6)
RS.Update
RS.Close
CN.Close
Set RS = Nothing
Set CN = Nothing

目次に戻る

(1)　データベースを設定するための３要素

　データベースを最初に設定する場合、データベースの名前、テーブルの名前、フィールドの名前とタイプが必要です。

　フィールドのタイプというのは、文字列なのか数値なのかといったデータ型のことです。

　先のVBScriptでは次の３行がそれらを定めています。

DbName = "test.xls"
TableName = "Sheet1"
TypeStr = "氏名 varchar,身長 float"

　「氏名」が文字列型、「身長」は数値型(浮動小数点数)です。

　Accessになじみのある人には、文字列型を表す varchar に桁数指定がないところに違和感を覚えるかもしれません。

　ここは varchar(20) などのように書いてもいいのですが、Excelファイルでは、その桁数指定が無視されます。なので、varcharとだけしました。

　Excelファイルは、本来、データベース用として設計されていないので、Accessファイルと比べた場合、いくつか機能的に不十分なところがあります。

目次に戻る

(2)　32bitか64bitかの確認

　マイクロソフトは、データベースにアクセスするためのエンジンを切り替えました。Office2003ころまでは JET.4.0、Office2007以降では ACE.12.0 です。

　32bitのWindowsでOffice2007以降が導入されていれば、おそらく JET, ACEの両法が使えると思います。Office2003までだと JETしか使えないケースがあるかもしれませんが。

　64bitのWindowsの場合は、JETが使えず、ACEだけ利用できるというケースが多いのではないでしょうか。

　正確には「Windowsが32bitか64bitか」ということではなく、VBScriptが実行されるときに暗黙のうちに呼び出されている WScript.exe または CScript.exe が32bit版か64bit版かの違いが影響します。64bit版だと、JETを使えずACEを利用せざるを得ないということです。

　というような事情があるので、今回のVBScriptでは 32bitか 64bitかを確認するようにしています。

　Set ShellObj = CreateObject("Wscript.Shell") とした上で、
　ShellObj.Environment("Process").Item("PROCESSOR_ARCHITECTURE") の値を見ます。

　この値が “x86” であれば32bit版、それ以外なら64bit版であると推測されます。たとえば “AMD64” だと64bit版です。

　32bit版ならJETエンジンを使い、64bit版だとACEエンジンを使うようにします。次に述べる「接続文字列」でその辺の設定をします。

目次に戻る

(3)　接続文字列(ConnectionString)

　データベース test.xls のフルパスが C:\work\test.xls の場合でいうと、データベースに接続するための接続文字列が下のようになります。

　長いので２行にして掲げますが、ほんとは１行です。

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\work\test.xls;
Extended Properties="Excel 8.0;HDR=Yes;"

　上は JET.4.0 を指定しているので 32bit用です。
　64bit用は下のようになります。

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\work\test.xls;
Extended Properties="Excel 8.0;HDR=Yes;"

　HDR=Yes というのは、ヘッダーの扱いを指定するものです。

　Yesだと、ワークシートの１行目をフィールド名とみなすことになります。

　Noにすれば、１行目をフィールド名ではなくデータとみなします。

　Excelファイルをデータベースとして扱う場合は、HDRをYesにしておくのが無難です。

　特に、ワークブックを新規に作成するときは、HDRをNoにしても意味ないのでYesにします。

　既存のワークブックを読み取ってデータベース操作するときは、HDRをNoにした方がいいケースもあると思いますが……

目次に戻る

(4)　ADOの接続用オブジェクト

　VBScriptの中で、ADOの接続用オブジェクトを変数CNに代入しています。

　このCNがデータベース操作の中核を担います。

　「接続文字列」が変数ConnStrに代入されている場合、CNの生成とオープンは次のようにして行います。

Set CN = CreateObject("ADODB.Connection")
CN.Open ConnStr

　SQL(構造化問合せ言語)の命令文が変数sqlに代入されている場合、
CN.Execute(sql) とすればSQL命令が実行されます。

　データベースのテーブル(Sheet1)を生成するための命令文は下のとおりです。

create table Sheet1 (氏名 varchar,身長 float);

　上を文字列として変数sqlに代入しておいて、CN.Execute(sql) とすればテーブルが生成されます。

　ここで疑問を感じる人がいるかもしれません。

　「テーブルを生成する前に、test.xlsというデータベース本体を生成する必要があるのではないか？」

　この疑問は当然で、実際、Accessデータベースの場合は、テーブルを生成する前にデータベース本体を生成しなければなりません。

　ところが、Excelの場合はテーブル生成時にデータベース本体が暗黙のうちに生成されます。

　既に test.xls というデータベース本体が存在していれば、新たに生成されたりはしませんが、test.xlsがまだない場合、新規に生成されます。

目次に戻る

(5)　レコードセットの生成とオープン

　テーブルを生成した後は、いよいよデータの書き込みです。

　ですが、書き込みの前にレコードを扱うための下準備を行う必要があります。

　レコードの処理は、レコードセットオブジェクトを設けて、それにより行います。

　まず Set RS = CreateObject("ADODB.Recordset") としてレコードセットオブジェクトを生成します。

　次にレコードをオープンしますが、パラメータをいくつか指定します。

　まず、下のSQL命令文を予め変数sqlに代入しておきます。テーブルから全てのフィールドを抽出するSQL命令文です。

select * from Sheet1;

　その上で、次のようにするとレコードセットを書き込み用にオープンできます。

RS.Open sql,CN,0,2,1

　改めて生成とオープンを連続して書くと下のとおり。

Set RS = CreateObject("ADODB.Recordset")
sql = "select * from Sheet1;"
RS.Open sql,CN,0,2,1

　既に存在しているデータベースから一定の条件に合致するデータだけ取り出したいようなときは、sqlをもっと複雑なものにした上でレコードセットをオープンします。

　第３、第４、第５パラメータの 0,2,1 については次の項で説明します。

目次に戻る

(6)　カーソルタイプ、ロックタイプおよびオプション

◇　カーソルタイプ

　RS.Open sql,CN,0,2,1 の 0 ですが、
これは、注目レコードの移動方式(カーソルタイプ)です。

　前から後ろに向かって順番どおりに移すタイプか、
後ろから前に戻るなど自由に移せるタイプかなどを指定するものです。
次の値を指定できます。

◇　ロックタイプ

　RS.Open sql,CN,0,2,1 の 2 は、ロックタイプです。

　レコードを読取り専用／更新可能のどちらでオープンするか、
更新可能のときは他の利用者の更新との兼ね合いをどうするかなどを指定します。
次の値を指定できます。

　ロックの詳しいことは分かりませんが、データベースを他の人と共有するとか、複数のソフトから同時期にアクセスするというのでなければ、「排他」でも「共有」でも、どちらでもいいと思います。

◇　オプション

　RS.Open sql,CN,0,2,1 の 1 はオプションです。第１パラメータの種類を明示するためのものです。

　サンプルではSQL命令文を変数sqlに代入して、それを第１パラメータとして指定しています。このような場合は、オプションを 1(定数名 adCmdText)にします。

　SQL命令文ではなくテーブル名を指定した場合は、オプションとして 512(adCmdTableDirect)または2(adCmdTable)を指定します。

　オプションは、省略されることも多いようです。省略すると自動判別されますが、指定した方が若干でもスピードアップにつながるようです。

　オプションの詳細については、マイクロソフト社の Recordset オブジェクトの基本を参考にして下さい。

　このサイトには、カーソルタイプやロックタイプの説明もあります。

目次に戻る

(7)　データの書き込み

　データの書き込みはレコードごとに行います。一人分を書き込んだら、次に二人目を書き込みます。

　まず、RS.AddNew として新しいレコードを書き込める状態にします。

　次はフィールドごとの書き込みです。下のようにします。

RS.Fields("氏名").Value = "鈴木"
RS.Fields("身長").Value = 172.3

　これで「氏名」と「身長」のデータをセットしたことになります。

　といっても、実際にデータベースに書き込みできた訳ではありません。メモリー上に一時記録されただけということのようです。

　ちゃんと書き込みするためには RS.Update を記述します。

　二人目のデータも同じように、RS.AddNew と RS.Update で囲む形で行います。

　さっきはフィールドの指定を “氏名” とか “身長” という文字列で行いましたが、インデックス番号で指定することもできます。

　その場合は、１からではなく０から始まります。下のような記述です。

RS.AddNew
    RS.Fields(0).Value = "高橋"
    RS.Fields(1).Value = 168.5
RS.Update

　AddNewにパラメータを渡してデータを書き込むこともできます。

　第１パラメータはフィールド名が格納された配列、第２パラメータが各データが格納された配列です。

　次のように記述します。

RS.AddNew Array("氏名", "身長"), Array("田中", 183.6)
RS.Update

　以上が主な書き込みの方法です。

目次に戻る

(8)　クローズによる締めくくり

　データベースへのアクセスが一通り終了したら、レコードセット(RS)、ADOの接続用オブジェクト(CN)の両法ともクローズします。

RS.Close
CN.Close

　これでデータベースの処理が終了です。

目次に戻る

2.　Excelのxlsxファイルへの書き込み

　Excel2007以降の test.xlsx は、ADOのJET.4.0エンジンで扱うことはできません。ACE.12.0エンジンで行います。

　32bit版, 64bit版のどちらであっても、ACE.12.0エンジンを用います。ACEが使えない環境では、残念ながらxlsxファイルを扱うことができません。

　xlsファイルとの違いは、この点だけです。

　したがって、接続文字列(ConnectionString)が違うだけということになります。

　xlsx用の接続文字列は下のとおり。２行になっていますが、ほんとは１行です。

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\work\test.xlsx;
Extended Properties="Excel 12.0 XML;HDR=Yes;"

　あと、32bit版か64bit版かの識別をしても仕方ないのでやりません。

△　create_xlsx.vbs

 Option Explicit
 Dim DbName, TableName, TypeStr
 Dim FSO, DbPath
 Dim CN, ConnStr, RS, sql
 
 DbName = "test.xlsx"
 TableName = "Sheet1"
 TypeStr = "氏名 varchar,身長 float"
 
Set FSO = CreateObject("Scripting.FileSystemObject")
DbPath = FSO.GetAbsolutePathName(DbName)
If (FSO.FileExists(DbPath) = True) Then FSO.DeleteFile(DbPath)
Set FSO = Nothing

ConnStr = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
    "Data Source=" & DbPath & ";" & _
    "Extended Properties=""Excel 12.0 XML;HDR=Yes;"""
Set CN = CreateObject("ADODB.Connection")
CN.Open ConnStr
sql = "create table " & TableName & " (" & TypeStr & ");"
CN.Execute(sql)

Set RS = CreateObject("ADODB.Recordset")
sql = "select * from " & TableName & ";"
RS.Open sql,CN,0,2,1
RS.AddNew
    RS.Fields("氏名").Value = "鈴木xlsx"
    RS.Fields("身長").Value = 172.3
RS.Update
RS.AddNew
    RS.Fields(0).Value = "高橋xlsx"
    RS.Fields(1).Value = 168.5
RS.Update
RS.AddNew Array("氏名", "身長"), Array("田中xlsx", 183.6)
RS.Update
RS.Close
CN.Close
Set RS = Nothing
Set CN = Nothing

　以上でこのページは終了です。

　データベースの本丸である Accessファイルについては次項で取り上げます。

　なお、データの書き込みをSQL命令文の insert into で行うこともできます(zip圧縮ファイルに含まれている create_xls02.vbs, create_xlsx02.vbsを参照)。

　SQL命令文のinsertについては Accessファイルのところで触れます。

〜　以上　〜