ちょいくらブログ

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

*

エクセル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)に接続出来ない場合はセキュ …

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

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

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

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

メールの整理術 一番効率のよい仕分け方法は「仕分けしない事」一択

こんばんは。TAKENTAです。 皆さんはビジネスメールの仕分けをどのように行っ …

体質・病気・祖先まで知ることが出来るDNA検査 マイコード ヘルスケアとDNA検査の未来を想像

こんばんは。TAKENTAです。 皆さんはDNA検査といったらどんなことを思い浮 …

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

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

「マルチタスク」のメリットを最大限発揮する作業方法を説明

こんばんは。TAKENTAです。 今日の記事は「マルチタスク」と「シングルタスク …

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

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

エクセルで重複データをカウントする関数

こんばんは。TAKENTAです。 今日のエクセル勉強会の題目は、「重複データをカ …

ASUS VivoBook E203NA 電源が入らなくなった時の対処手順

コスパが良いと評判の「ASUS VivoBook E203NA」。我が家でも持ち …