ACCESS　Excel連携　データはACCESS　集計はExcel

TPOが重要

こんにちは。伊川(@naonaoke)です。

今回は、ExcelとACCESSの連携について紹介します。

この連携を知っているだけで、ものすごくスキルがあるように思われます。

コード自体は簡単です。

もともと、ACCESSはデータベース、Excelは表計算なのです。

ガムシャラに、ACCESSに計算をさせるとか、Excelでテータベースを作成するというのは、本来の姿ではないのです。

仕事に役立つExcelデータベース 第4版 (Excel徹底活用シリーズ)

created by Rinker

• Amazon
• 楽天市場

問題提起

解決策

回答

・連携のコードをコピペで作成する。

・それほど難しくない。

ACCESS　Excel連携

ADOとはActiveX Data Objectの略でアプリケーションが一元的にデータベースをアクセスするためのライブラリです。ADOを使用することにより、Accessデータベース以外のデータベースやSQLサーバーにアクセスできる他、Visual BasicやVisual #Cなどの他の言語から、Access VBAと同じ方法でデータベースにアクセスできるようになります。

なんとなく難しいように感じますが、ブログをよんでいただければ、理解できると思います。

ACCESS　Excel連携　作成手順その1　データベース確認

今回は、日付別の所持金の合計を算出します。

今回のデータは、約7,000件のデータをExcelへ転送して、SUMIF関数で合計を出します。

ACCESS　Excel連携　作成手順その2　 ActiveX Data Objectを有効にする

赤枠の向かって左側にチェックを入れてください。

ActiveX Data Objectが有効になります。

これで、ACCESSとExcelは、ツーカーの仲になります。

ACCESS　Excel連携　作成手順その3　 データベースを転送するコード

基本的に書き換えるのは、赤枠の中だけです。

相対パスは、同一フォルダ内にACCESS、Excelのファイルが存在していることです。

相対パスに対して、絶対パスというのもあります。

しかし、絶対パスは、ACCESS、Excelのファイルを移動する度、にパスを書き換えるというデメリットがあります。

Sub データ取り込み()
  Dim con As New ADODB.Connection
  Dim rs As New ADODB.Recordset
  Dim conStr As String

 conStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ActiveWorkbook.Path & "\所持金.accdb"
  con.Open ConnectionString:=conStr
  rs.Open Source:="MT_所持金", ActiveConnection:=con, _
    CursorType:=adOpenKeyset, LockType:=adLockOptimistic

  Worksheets(“データ”).Range(“A６").CopyFromRecordset Data:=rs
 
Worksheets(“データ”).Range(“A５") = "ID"
Worksheets(“データ”).Range(“B５") = "日時"
Worksheets(“データ”).Range(“C５") = "名前"
Worksheets(“データ”).Range(“D５") = "所持金"

 MsgBox "データ取込完了“

End Sub

ACCESS　Excel連携　作成手順その4　SUMIFSをVBAで実行する

Sub sumifs()

Dim ws As Object
Set ws = Worksheets("データ")

Dim i As Long, j As Long, a As Long

maxROW2 = Cells(Rows.Count, "A").End(xlUp).Row

maxColumns = Cells(5, Columns.Count).End(xlToLeft).Column

For i = 6 To maxROW2
For j = 2 To maxColumns

Cells(i, j) = WorksheetFunction.sumifs(ws.Range("D:D"), ws.Range("C:C"), Cells(i, 1), ws.Range("B:B"), Cells(5, j))

Next j
Next i

MsgBox "集計完了"
End Sub

ワークシート関数と対比してみる

ワークシート関数を、VBAで実行するときは、ダイアログボックスを想像しながら、コードを書くとイメージが沸きます。

ACCESS　Excel連携　作成手順その5　完成図

データの取込が完了しました。

集計も完了しました。

ここがポイント

ここがポイント

入コード自体は難しいかもしれませんが、やっていることはメチャクチャ簡単です。ExcelとACCESSの転送ができると、一目おかれるようになります。

まとめ

今回のまとめ

ExcelとACCESSを連携させることで、後処理業務が短縮できました。

今回のサンプルファイルは、No92です。

毎日の業務が、3時間短縮できます

わからない事を延々と考えるのは、無駄です。

• なんで作動かないの？
• もうやだ！VBAなんか嫌い！
• ネットで調べても情報がない！

必ず作動するコードが、ここにあります。

サンプルファイルの概要、個別コンサルの概要にについて

Related posts:

ACCESSとExcelの連携　ACCESSで管理　Excelで分析　その3 【ACCESSとExcelの連携】リストボックスの値をセルに転記 【ExcelとACCESSの連携】ACCESSでサブクエリをVBAで実行することを考える 【Excel ACCESS連携】〇〇を含むデータを抽出しろと言われたら？