会社の業務で、ExcelでつくったCSVデータをAccessのテーブルに自動で取込みたい時ってあると思います。そういった時はAccessのインポート機能を使ったり、手動でコピペしたりするのではないでしょうか。
そういった方法が面倒でAccessVBAが使える方であれば、プログラムを作って自動で複数のCSVを指定のテーブルに取込めるようにできる方もいるでしょう。
しかしこの時に何か問題が起きないでしょうか。例えば次のような問題です。
- 全てのCSVデータがAccessの指定のテーブルに取込めない(例えば10行のデータがあったら7行分しか取込まれない)
- 元のCSVデータを順番通りにAccessの指定のテーブルに取込めない
といった具合です。
このような問題が起きる時というのは、TransferTextを使って取込みをしたりField オブジェクトを使ってひとつずつデータを入れていくという方法を取られたりしているのではないでしょうか。
以下の前回の過去記事をつくった後にこのことに気づきました。
そのため今回はこの問題点を解決するためのコードをつくったので、整理していきたいと思います。
今回のコードを作った理由
今回のコードを作った理由というのは冒頭部分でも書きましたが、Accessを使ったとあるシステムを作るときに以下のことに気づいたからです。
- 全てのCSVデータがAccessの指定のテーブルに取込めない(例えば10行のデータがあったら7行分しか取込まれない)
- 元のCSVデータの順番通りにAccessの指定のテーブルに取込めない
ちゃんとテストしていなかったわけではないのですが、どうやらAccessのテーブルに取込むための元データの方にいろいろと空白があると全てのデータが取込まれない時があるみたいですね。
自分がテストした時は全てのセルにデータが入ったものだったのでこのことに気づきませんでした。
とは言うものの、業務の都合上一つの表で全てのセルにデータが入らない時だってもちろんあるでしょう。こういった空欄のセルがあっても全ての行のデータを取込めるようにしないといけません。
このためにいろいろと試行錯誤をして例えば以下のようなコードをつくったりしてみました。
For r = 1 To 100
rs.AddNew
For c = 1 To 10
rs.Fields(c-1).Value = wb.sheets(1).cells(r, c)
Next c
rs.Update
Next r上記のコードから言わんとすることは何となくわかっていただけるかと思います。CSVのデータをAccessの指定のテーブルの各フィールドに順番に入力していくというコードです。
これはこれで高速に元データの全てをテーブルに入れることは出来ます。けれどもここでまた問題が発生します。それは「元データの順番」と「Accessに取込まれた時のレコードの順番」が「一致しない」のです。
Accessの画面に表示されるレコードの数から全ての元データが取込めたというのはわかります。けれども、元データの最初と最後の行にある数値と、Accessのテーブルの最初と最後のレコードの数値が一致しません。
何が起きたのか最初はよくわからなかったのですが、どうやら順番が変わってしまっているということがわかりました。自分が調べた範囲では次のようなことが原因になっているようです。
内部の詳しい事はわからないのですが、どうやらAccessはRDBMS(リレーショナルデータベースマネジメントシステム)を使っているのでDBとして最適な処理ができるよう順番を変えてしまうようです。
そういったことがあったので、順番が変わらないように試行錯誤をしてみたりもしました。例えば
- レコードセットオブジェクトのopenメソッドで、引数のCursorTypeをadOpenForwardOnlyに指定
- 指定のテーブルの主キーをなくす
- 特定のフィールドのデータ型をオートナンバー型にする
など試行錯誤してはみたのですが、なかなか解決できませんでした。
こういった状況から考えたのが次のコードです。
AccessVBAのRunCommandで順番通り全てのCSVデータを取込むコード
目的のコードを確認する前に以下の準備が必要です。
今回も前回同様コードを記述する前にデスクトップ上でもどこでも新しくフォルダを作成して下さい。フォルダ名はなんでも構いません。そのフォルダ内にAccessファイル、それとAccessの指定のテーブルにデータを取り込むためのCSVファイルを3つ程用意します。
プログラムの実行前にAccessに用意されたテーブル構造とCSVファイルのテーブル構造は事前に一致させておく必要があります。
また、テーブルのどれかのフィールドにオートナンバー型が設定されている状態で、下記のコードのように最初に内部のデータを削除してからCSVを入れる形だと、オートナンバー型の数値は削除した最後の数値から連続してどんどん増えていきます。
データの内容を削除しても、再度「1」から始まるという訳ではないので、この点を考慮していただければと思います。
注文が多くなって申し訳ないですが、一番左側のフィールドの値に空欄があると、一回テーブルを閉じて再度開いた時に空欄のレコードだけが一番上に来てしまいます。そのため、順番を崩したくない場合は一番左側のフィールドは何らかの値を入れておく必要があります。
VBEの参照設定で「Microsoft ActiveX Date Objects 6.1 Library」にチェックも入れておく必要があります。もしかしたら人によってバージョンの数値が異なるかもしれません。
以下が「AccessVBAのRunCommandで順番通り全てのCSVデータを取込むコード」になります。ちなみに確認した環境はAccess2013で標準モジュールに入れて実行します。
Sub コピー貼付()
Dim Efile As String
Dim FSO As Object
Dim f As Variant
Dim Ex As Variant
Dim wb As Variant
Dim wc As Variant
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
On Error GoTo Err1
Set Ex = CreateObject("Excel.Application")
Set FSO = CreateObject("Scripting.FileSystemObject")
DoCmd.SetWarnings False
Ex.DisplayAlerts = False
DoCmd.Echo False
'----------------------------------------------------------------
ConectString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & _
CurrentProject.FullName & ";"
cn.Open ConectString
rs.Open Source:="テーブル1", _
ActiveConnection:=cn, _
CursorType:=adOpenKeyset, _
Locktype:=adLockOptimistic
strSQL = "DELETE * FROM テーブル1"
cn.Execute strSQL
'-----------------------------------------------------------------
'CSVデータを指定のテーブルに貼り付けていく
For Each f In FSO.GetFolder(CurrentProject.Path).Files
If FSO.GetExtensionName(f.Path) = "csv" Then
Efile = Application.CurrentProject.Path & "\" & f.Name
Set wb = Ex.WorkBooks.Open(Efile)
Set ws = wb.activesheet
Ex.Visible = True
ws.UsedRange.Select
Ex.Selection.Resize(Ex.Selection.Rows.Count - 1).offset(1, 0).Select
ws.range("A2").Activate
Ex.Selection.copy
ans = SysCmd(acSysCmdGetObjectState, acTable, "テーブル1")
'もしテーブル1が開いていなければ開く
If ans <> 1 Then
DoCmd.OpenTable "テーブル1"
End If
DoCmd.GoToRecord acDataTable, "テーブル1", acNewRec
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdPaste
Ex.Visible = False
wb.Close
Set wc = Nothing
Set wb = Nothing
End If
Next
DoCmd.SetWarnings True
Ex.DisplayAlerts = True
DoCmd.Echo True
Set Ex = Nothing
rs.Close
cn.Close
Set rs = Nothing
Set cn = Nothing
Exit Sub
Err1:
DoCmd.SetWarnings True
Ex.DisplayAlerts = True
DoCmd.Echo True
Set Ex = Nothing
rs.Close
cn.Close
Set rs = Nothing
Set cn = Nothing
End Sub上記のコードの概要を簡単に説明すると、処理させている内容は全然難しいことではなくCSVからAccessへの単なるコピペです。Accessの標準モジュールでExcelを操作する処理が入るので若干面倒な部分はありますが。もう少し具体的に説明すると以下のような感じになります。
最初は定型どおり変数の宣言、データベースと指定のレコードセットへ接続をします。以下のコードは「テーブル1」テーブルの内容を一旦削除するコードです。必要がなければコメントアウトします。
strSQL = "DELETE * FROM テーブル1" cn.Execute strSQL
次の
'CSVデータを指定のテーブルに貼り付けていく For Each f In FSO.GetFolder(CurrentProject.Path).Files
以降のコードで、今回のコードが記述されたAccessファイルがあるフォルダ内のCSVデータを処理していきます。
CurrentProject.Pathで今回のコードが記述されているAccessファイルが格納されているフォルダまでのパスを取得、FSO.GetFolderで、そのパスのフォルダ内にあるファイルを全て順番に処理していきます。
このコード以降のポイントはオブジェクト変数「Ex」です。AccessからExcelを扱うことになるので、Excelオブジェクトを通して操作しないと動いてくれません。それ以外にもExcelのワークブックオブジェクト、ワークシートオブジェクトも取得する必要があります。
いつもはExcel内だけで操作が完結するか、もしくはExcelからAccessを操作する時が多いので本体をExcelとして考えがちです。ですから、こういったExcel以外のMicrosoftのオフィスソフトを操作する時に、このExcelオブジェクトの生成を忘れてしまいがちです。
例えば以下のコードを作るときに少し躓きました。
Ex.Selection.Resize(Ex.Selection.Rows.Count - 1).offset(1, 0).Select
このコードにはExcelオブジェクトの「Ex」が必要になります。最初の内は何が足りないかわからなかったので以下のように書いていました。
Selection.Resize(Selection.Rows.Count - 1).offset(1, 0).Select
CSVのデータ部分だけを選択するというコードなんですが、選択するのはAccessではなく、操作するフォーマットがExcelになるので「Ex」が必要になるというわけです。
で、このコード以降は、CSVのデータ部分だけをコピーしてAccessの「テーブル1」テーブルに貼付をしていくことになります。これ以降で注意する部分はテーブルが開いているかどうかの判定です。それが以下のコードになります。
ans = SysCmd(acSysCmdGetObjectState, acTable, “テーブル1”)
SysCmdメソッドを使って、指定のテーブルが開いているかどうかの判定を変数ansに格納しています。開いていたら「1」を格納します。その１がansに格納されているかどうかを以下のコードで判定しています。
If ans <> 1 Then DoCmd.OpenTable "テーブル1" End If
For Each文で繰り返し処理をしているので、このコードを入れないと繰り返しテーブルを開く処理をしてしまいエラーが出ます。そのため、一度開いたら二度は開かないようにこのコードを入れています。
以下のコードで新規レコードにカーソルを移動させます。
DoCmd.GoToRecord acDataTable, "テーブル1", acNewRec
ポイントは最終レコードではなく「新規」レコードです。Accessのテーブルの画面を見た感じでは最終レコード(一番下のレコード)から新しくデータを入力できそうに見えますが、Accessの認識では「データが入っているレコードの最後」が最終レコードになっています。
だから、GoToRecordメソッドの3つ目の引数は、新規レコードを指す「acNewRec」にしないと上手く動作してくれません。
次のポイントが以下のRunCommandメソッドを使ったコードです。
DoCmd.RunCommand acCmdSelectRecord DoCmd.RunCommand acCmdPaste
RunCommandメソッドについて、Microsoftのページには以下のように書かれています。
Access の各メニュー コマンドまたはツールバー コマンドには対応する定数があります。この値を指定して RunCommand メソッドを使うと、コードでコマンドを実行できます。
RunCommand メソッドを使用して、カスタム メニューまたはカスタム ツールバー上のコマンドは実行できません。 このメソッドは、組み込みメニューおよび組み込みツールバーに対してのみ使用できます。
今回の記事の処理を実現させたいと思ってインターネットでいろいろと調べていたら、偶然この「RunCommandメソッド」というものを見つけました。
Accessにこんなコードがあったんですね。自分は今まで『VBAエキスパート公式テキスト Access VBA ベーシック』や『VBAエキスパート公式テキスト Access VBA スタンダード』という本を読んできたのですが、このRunCommandメソッドというのはなかったです。
『AccessVBA逆引き大全600の極意 2002／2003／2007対応』というAccessVBAの辞書的な本も手元にあるので調べてみたのですが、最後の方の索引(P.834)にはRunCommandは載ってます。
けれども、RunCommandの所に書かれたページを見ても全然使用例が書かれていません。(なぜ？)もしかしたらこのRunCommandというのはAccessVBAでの裏ワザ的なもの？なのかもしれませんね。
話を戻しますが、
DoCmd.RunCommand acCmdSelectRecord
で、ここまでの状態でカーソルがあるレコードを選択します。実際に手作業でExcelにあるデータなどをAccessのテーブルにコピペしてもらうとわかると思いますが、レコードを全体を選択した状態じゃないと貼付できません。
テーブルで、Excelでいうところのセルを選択した状態で貼付しようとしても上手くいきません。そのためacCmdSelectRecordが必要になります。
次の
DoCmd.RunCommand acCmdPaste
で、選択したレコードに対して、CSVのデータを貼付する、という処理を実行します。通常のAccessのコードだと、こういった手作業でのコピペ処理ができないので助かります。
以上の処理をFor Eachで指定のフォルダ内にある全てのCSVに実行していく形になります。
補足として、以下のコードを入れておかないと今回の処理の途中で確認メッセージが何度も表示されてしまいます。
DoCmd.SetWarnings False Ex.DisplayAlerts = False
一応念のためその度ごとに内容を確認しておきたい、ということであればTrueの部分も含めてコメントアウトか削除していただくとよいかと思います。
その他の補足としては、今回のコードはSlection.copyとかRunCommandの部分も含めてGUI的な処理が多いのでスピードが落ちます。
そのため、なるべく高速化できるようにDoCmd.Echo Trueというコードを入れるようにしています。
以上が今回のコード全体の説明になります。
まとめ
Accessに触れるようになってきたからこそわかるようになってきましたが、やはりExcelとAccessの勝手は違うようですね。
使う用語がExcelでは行や列と呼ばれるものが、Accessだとレコードやフィールドと呼ばれたりします。それだけではなく、機能的にも違う部分というかAccessには独特の癖があるのかもしれません。
今回の事例のように、CSV(使っているフォーマットはExcel)をAccessのテーブルに入れる時に全てのデータが取込めなかったり順番が勝手に変わってしまうという現象は、これまでExcelを使ってきて一度も経験したことはなかったと思います。
こういうことがあるということは、他にも何らかの不具合や癖があるかもしれないと考えるのが普通です。実際以下の過去記事のようにAccessのVBEでデバッグをするにも苦労した時がありました。
Accessを使いこなせるようになるためには、今回の事例からExcelとは違う操作性とか慣れが必要になってくるんだろうと思いました。
追記
なお、今回のコードは確かに元データと同じ順番で全て取込み(テーブルへの貼付)まではできるのですが、取込んでテーブルを閉じ、もう一度開くと順番が変わってしまう時もありました。
これ以降のデータの処理という点では何も問題はないとは思います。この部分について修正できるのかどうか調べて、何か解決方法があれば、またこのブログで書いていこうと思います。
コメント