エクスポート

vba を使用して excel から access へエクスポートが簡単にできます！

vba excel access 簡単です！ Homeへ
エクスポート

vbaを使用して excel から access へデータをエクスポートできます。

ADOは、データをより簡単・高速にエクスポートできます。
エクスポートの例として、次に説明した excel ワークシートの１０万行Ａ，Ｂ２列のテーブルを対象にしました。

（１）　１行目は、ヘッダーです。　Aceessのフィールド名に合わせました。
（２）　ワークシートは、Sheet1 を利用しました。
（３）　データは、１０万行をエクスポートできます。

ID　　　　Data1　　　　Data2
0000001　A-0000001 B-0000001
0000002　A-0000002 B-0000002
　　　　・・・・・・・・・・・
（同様のエクスポート・データが１００，０００行続きます。）
　　　　・・・・・・・・・・・
0099999　A-0099999 B-0099999


一方、Accessは、上記のデータをインポートした結果作成できるテーブルを利用しました。
（１）　テーブルは、Sheet1 と命名しました。
（２）　テーブルは、３つのフィールドで構成させました。
　　　１）　ID　主キー　　　　
　　　２）　Data1 テキストフィールド　
　　　３）　Data2　テキストフィールド

１．　ADOを利用して excel から　access　のテーブルへエクスポートする方法
※　接続と解除、デバッグプリントについては、説明がありません。　「接続」記事を参照ねがいます。
主なポイントは、次の通りです。　（ ）内の番号は、説明の次にある各ステートメントのコメント内の番号と対応しています。
（１）　テーブル情報を変数として宣言します。
（２）　テーブル情報のオブジェクトを作成し、参照を変数に代入します。
（３）　レコードを追加する前に、テーブルのデータを削除します。
　　　１）　エクスポート先のSheet1テーブルのデータを全て削除します。
　　　２）　エクスポート先のSheet1テーブルを開きます。
（４）　セル範囲の列数より、フィールド数を求めます。
（５）　行番号を仮変数に代入します。　カーソルの位置と対応させています。
（６）　ワークシートのテーブルのセル範囲をループさせ、エクスポートします。
（７）　★　次の　Forループで情報をテーブルに追加します。
（８）　主キーID フィールドの隣のフィールドからエクスポート・データを入力します。
（９）　★　上記のForループでテーブルに追加した情報を含め、テーブルを更新します。
（１０）　次の行に移動します。
（１１）　テーブルを閉じます。
（１２）　テーブル情報のリソースをクリアーし、メモリを解放します。

Sub export_ws_ADO()
''VBEの「ツール」ー「参照設定」で　Microsoft ActiveX Data Objects X.XX Library にチェックを入れます。
　　Dim oCn As ADODB.Connection
　　Dim oRs As ADODB.Recordset　'（１）
　　Dim intL As Long, intMaxF As Integer　'使用する仮変数の宣言です。

　　Debug.Print "Start ADO " & Time

　　Set oCn = New ADODB.Connection
　　Set oRs = New ADODB.Recordset　　'（２）
　　oCn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
　　　　"Data Source=C:\Program Files\Microsoft Office\Office14\ACCWIZ\Imp_Accdb.accdb;"

　　oRs.Open "DELETE FROM Sheet1;", oCn　　'（３）−１）
　　oRs.Open "SELECT * FROM Sheet1;", oCn, adOpenDynamic, adLockOptimistic　'（３）−２）

　　intMaxF = Range("A1").CurrentRegion.Columns.Count - 1　'（４）

　　intL = 2　'（５）
　　Do While (Cells(intL, 1).Value <> "")　'（６）
　　　　oRs.AddNew　'（７）　★
　　　　For j = 0 To intMaxF
　　　　　　oRs.Fields(j + 1) = Cells(intL, j + 1).Value ' （８）
　　　　Next j
　　　　oRs.Update　'（９）　★
　　　　ntL = intL + 1　'（１０）
　　Loop
　　oRs.Close　'（１１）
　　oCn.Close

　　Set oRs = Nothing　'（１２）
　　Set oCn = Nothing

　　Debug.Print "END ADO " & Time

End Sub

２．　excel から　access　へテーブル（表・リスト）をオートメーションによりエクスポートする方法
オートメーションは、データ以外のフォームなど、ADOに無い accessオブジェクトの機能を使用する場合、活用できます。
このオートメーションは、Office 95などにも対応でき、応用範囲の比較的広い方法であることが分かります。
※　接続と解除、デバッグプリントについては、説明がありません。　「接続」記事を参照ねがいます。
ポイントは、２か所です。　（ ）内の番号は、説明の次にある各ステートメントのコメント内の番号と対応しています。
（１）　エクスポート・データを追加する前に、Sheet1テーブルのデータを全て削除します。
（２）　excel のワークシートは、セル範囲まとめて access へエクスポート転送できます。
　　　accessのインポートモードにより、シートの１０万行のセル範囲を転送します。
　　　エクスポートのオートメーションは、vba ステートメントがより単純なことが分かります。
Sub export_ws_automation()
　　Dim oApp As Object

　　Debug.Print "Start AutoMation " & Time
　　On Error Resume Next
　　Set oApp = GetObject(, "Access.Application")
　　If Err.Number <> 0 Then
　　　　 Err.Clear

　　　　Set oApp = CreateObject("Access.Application")

　　End If

　　oApp.OpenCurrentDatabase "C:\Program Files\Microsoft Office\Office14\ACCWIZ\Imp_Accdb.accdb"
　　oApp.Visible = True

　　oApp.Docmd.RunSQL "DELETE FROM Sheet1"　'（１）

　　oApp.Docmd.TransferSpreadsheet acImport, 8, "Sheet1", "C:\Documents and Settings\Administrator\デスクトップ\Exp_Book1.xlsm", True, "A1:B100000"
　　　　　　　　　　　　　　　　　　　　　　　　'（２）
　　Set oApp = Nothing

　　Debug.Print "END AutoMation " & Time

End Sub


３．　ADOとオートメーションのエクスポート処理スピード結果・比較

　データのエクスポートのベンチマークテストを行った結果、ADOがより高速に処理できました。
　実際、１０万行２列のexcelデータをaccessへエクスポートしました。
　結果は、次の通りです。
　　　（１）　ADO　　約４７秒
　　　（２）　オートメーション　約２分
したがって、エクスポート速度は、ADOがオートメーションより約３倍ほど速いことが分かりました。
　テスト環境は、次の通りです。
　　　（１）PentiumR 4 CPU 2.60 GHz
　　　（２）OS　Windows XP　Professional　SP3
　　　（３）Office　２０１０
※　エクスポート・テストは、前半で説明に使用したマクロで行いました。
　　なお、処理時間は、Alt ＋ F11 キーを押して表示させたVBEウィンドウの「イミディエイト ウィンドウ」にプリントされます。

※　vba ステートメントの詳細は、「接続」記事など関連した記事を参照願います。

ADOとオートメーションのvba を使用した excel と access の接続を活用しましょう！

vba excel access 簡単です！ Homeへ