pickup

目次

TPOが重要

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

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

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

コード自体は簡単です。

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

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

ご視聴者様のコメント
エクセルとアクセスの連携の動画は衝撃的でした。
翌日書籍を購入し連携ファイルを作成しました。
アクセスのハウツウ動画webの情報非常に少ないです。
伊川さんの動画はとても参考になりました。
ありがとうございます
今はアクセスvbaを経てプログラムの学習をしてます
動画を楽しみにしてます
がんばってください







問題提起

(株)IKAWAの柄久 瀬瑠子(エク セルコ)は、

上司から、下記の依頼を受けました。

集計クエリは難しいので、ACCESSのデータをExcelで集計できないか?

どのようにして、ACCESSとExcelの連携をするのだろうか?








解決策

回答
sesuo2.jpg
・連携のコードをコピペで作成する。
・それほど難しくない。







ACCESS Excel連携

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

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

下準備

連携するACCESSとExcelのファイルが、同一フォルダ内にあること。(必須ではない)

Excel側の「ActiveX Data Object」ライブラリを有効にする。







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(“A6").CopyFromRecordset Data:=rs
 
Worksheets(“データ”).Range(“A5") = "ID"
Worksheets(“データ”).Range(“B5") = "日時"
Worksheets(“データ”).Range(“C5") = "名前"
Worksheets(“データ”).Range(“D5") = "所持金"

 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 完成図


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


集計も完了しました。







ここがポイント

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

 







まとめ

今回のまとめ
sesuko3.jpg
ExcelとACCESSを連携させることで、後処理業務が短縮できました。

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

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

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

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

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

スポンサーリンク

Twitterでフォローしよう

おすすめの記事