エクスポート

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


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

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


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

(1) 1行目は、ヘッダーです。 Aceessのフィールド名に合わせました。
(2) ワークシートは、Sheet1 を利用しました。
(3) データは、10万行をエクスポートできます。

ID    Data1    Data2
0000001 A-0000001 B-0000001
0000002 A-0000002 B-0000002
    ・・・・・・・・・・・
(同様のエクスポート・データが100,000行続きます。)
    ・・・・・・・・・・・
0099999 A-0099999 B-0099999


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

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

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

  Debug.Print "Start ADO " & Time

  Set oCn = New ADODB.Connection
  Set oRs = New ADODB.Recordset  '(2)
  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  '(3)−1)
  oRs.Open "SELECT * FROM Sheet1;", oCn, adOpenDynamic, adLockOptimistic '(3)−2)

  intMaxF = Range("A1").CurrentRegion.Columns.Count - 1 '(4)

  intL = 2 '(5)
  Do While (Cells(intL, 1).Value <> "") '(6)
    oRs.AddNew '(7) ★
    For j = 0 To intMaxF
      oRs.Fields(j + 1) = Cells(intL, j + 1).Value ' (8)
    Next j
    oRs.Update '(9) ★
    ntL = intL + 1 '(10)
  Loop
  oRs.Close '(11)
  oCn.Close

  Set oRs = Nothing '(12)
  Set oCn = Nothing

  Debug.Print "END ADO " & Time

End Sub

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

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

  Debug.Print "END AutoMation " & Time

End Sub


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

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

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



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


vba excel access 簡単です! Homeへ
posted by Yy at 05:33 | Comment(0) | エクスポート | このブログの読者になる | 更新情報をチェックする
×

この広告は1年以上新しい記事の投稿がないブログに表示されております。