ちょいくらブログ

人生にほんのちょっとだけいい暮らしを

*

エクセルVBAでAccessに接続する方法

      2019/09/09

こんばんは。TAKENTAです。私の仕事はエンジニアという名の事務職?なのですが、エクセル・アクセス等を使用してのデータ編集作業が非常に多い仕事です。実はエクセルVBAは見よう見まねで使用していて、人に教えるほどのスキルは持っていないのですが自分が忘れないためにも記事に残していこうと思いました。

まずはエクセルからアクセスに接続してデータを抽出する方法を紹介していきます。(仕事上この手の操作は非常に多いのです)

ADOを使用してエクセルからアクセスへ接続

ADOとはActiveXデータオブジェクトの略であり、ADOにはデータベースのレコードを検索・抽出・追加・削除等の操作が用意されておりエクセルからでも簡単にアクセスファイルのデータを使用することが出来ます。

参照設定

まず、エクセルVBAでADOを使用するには準備が必要です。

エクセルVBAの参照設定(ツール→参照設定)を選択します。

excelvba01-01

続いて、「参照可能なライブラリファイル」から「Microsoft Active Data Objects X.X Library」を選択します。「X.X」はバージョンなので一番新しいものを選択しましょう。

excelvba01-02

モジュールへの記述

Dim CN As New ADODB.Connection
Dim RS As New ADODB.Recordset
Dim SQL As String
Dim AcFName As String
Dim i As Integer

AcFName = “C\TEST.mdb”       ‘接続したいアクセスファイルのフルパスを記述

CN.ConnectionString = “provider=Microsoft.Jet.OLEDB.4.0;” _
& “Data Source='” & AcFName & “‘”
CN.Open

Do Until Sheets(“SheetName”).Cells(i, 1) = “”   ‘SheetNameはエクセルのシート名を記述
Sheets(“SheetName”).Cells(i, 1).Select

SQL = “SELECT ”
SQL = SQL & “テストテーブル.ID, ”
SQL = SQL & “テストテーブル.氏名 ”
SQL = SQL & “FROM ”
SQL = SQL & “テストテーブル ”
SQL = SQL & “WHERE ”
SQL = SQL & “(((テストテーブル.ID)='” & Sheets(“SheetName”).Cells(i, 1).Value & “‘));”

RS.Open SQL, CN, adOpenDynamic, adLockOptimistic, adCmdText

If RS.EOF = False Then

Sheets(“SheetName”).Cells(i, 2).Value = RS(“氏名”).Value

End If

i = i + 1

RS.Close: Set RS = Nothing

Loop

CN.Close: Set CN = Nothing

上記コードは使用しているエクセルファイルの「SheetName」というシートから、「TEST.mdb」というアクセスファイルへ接続しています。

SheetNameのセル(i,1)のデータとTEST.mdbのテストテーブル内のフィールド「ID」で合致するデータを検索しています。(行数 i は変数)

そして、検索して合致したデータがあれば、隣のセル(i,2)へフィールド「氏名」のデータを入力しています。また、セル(i,1)のデータが空白になるまで同じ処理を繰り返します。

ここまで書いてみましたがコードの動きが分りづらいですね・・次回はもう少し画像を増やして説明してみます。

とりあえず「エクセルVBAでAccessに接続する方法」でした。1回目なので今日のところはこの程度でお許し下さい。

 - 仕事のマインド

ad-pc

ad-pc

Message

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です

  関連記事

Windows7でLandisk(NAS)に接続出来ない場合の対処方法

Windows7を導入して古いLandisk(NAS)に接続出来ない場合はセキュ …

エクセル2013 図形を選択する矢印どこいっちゃった?

こんばんは。TAKENTAです。 長かったWindowsXP時代が終わりを告げ、 …

エクセルで最終行(列)のデータを取得する関数

こんばんは。TAKENTAです。 本日は久々のエクセル勉強会です。 今日悩んだエ …

仕事が予定通りに進まないストレス その原因はメールを読む頻度にあった

こんばんは。TAKENTAです。 朝出勤した時には「今日はこの仕事を片付けよう! …

dynabook Toshiba Places アンインストール(Windows10)

  こんばんは。TAKENTAです。 今回は我が愛機dynabook( …

レジリエンスとは 失敗も挫折も乗り越える心の回復力 折れない心

こんばんは。TAKENTAです。 みなさん「レジリエンス」という言葉をご存知です …

なぜなぜ分析の事例 事故を論理的に分析し再発を防止する

こんばんは。TAKENTAです。 私は製造工場に勤務しています。その為お客さんへ …

Windows10でクイックアクセスに自動でフォルダーを追加させない設定

こんばんは。日々Windows10と格闘しているTAKENTAです。 本日はWi …

エクセルで工程能力(Cpk)を算出する計算式

製造業に携わる方ならば、工程のよしあしを判断する尺度として工程能力(Cp、Cpk …

会社組織における中間管理職の役割とは何か

こんばんは。TAKENTAです。 サラリーマンで一番キツイ立場とよく言われる「中 …