AccessDB VBA

質問

お世話になります
■Excel C5以下行から社員コードを入力するとK5以下行に社員名がジャンプしてくる（Accessから）VBAを作成させたい

ワークシートは増減あります、名前もランダムです
book内のワークシートすべてに適用させたい

おそらくADO接続？？
初心者なのでコード等よろしくお願いします

回答No.1

normo · ベストアンサー

こんにちは。こんな感じでいかがでしょうか？
Accessの方のテーブル構成がわからないので、社員コードが整数型で"primarykey"と設定されているものとしました。データ型等が違う場合は修正を加えてください。
コピー等で複数のセルを一度に変更した場合には対応していません。

1.ADOを参照設定しておく
2.標準モジュールに（A）をコピーして、定数の宣言部分（mdbのパス等）を修正する。
3.各シートのコードに（B）をコピー

>ワークシートは増減あり、book内のワークシートすべてに適用させたい
には対応していません。全てのシートに（B）を記入する必要があります。
シートを増やすときには他のシートをコピーするのでしょうか？そうであるなら、コピー元のシートにコードが記入されていればコードもコピーされるのでその手間は省けます。

--------------------（A）--------------------
Sub SetShainmei(Target As Range)

　Const cstDbPath As String = "C:\aaa\社員.mdb" 'mdbのフルパス
　Const cstTblName As String = "社員マスタ" 'テーブル名
　Const cstfldName As String = "社員名" 'フィールド名"

　Dim cn As New ADODB.Connection
　Dim rs As New ADODB.Recordset

　If Target.Column <> 3 Or Target.Row < 5 Or Target.Rows.Count > 1 Or Target.Columns.Count > 1 Then
　　Exit Sub
　End If

　cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & cstDbPath
　rs.Open cstTblName, cn, adOpenKeyset, adLockReadOnly, adCmdTableDirect
　rs.Index = "primarykey"
　rs.Seek Target.Value, adSeekFirstEQ

　If Not rs.EOF Then
　　Target.Offset(0, 8) = rs.Fields(cstfldName)
　Else
　　Target.Offset(0, 8) = Null
　End If

　rs.Close: Set rs = Nothing
　cn.Close: Set cn = Nothing

End Sub

--------------------（B）--------------------
Private Sub Worksheet_Change(ByVal Target As Range)
　SetShainmei Target
End Sub

お礼

良回答ありがとうございます

補足

お世話になります
返事送れてすみません
ファイル名:社員コード.mdb
DB:社員コード一覧
ID|社員コード|社員名
となり主キーではないです
又、社員コードは0から始まるものもあります

例0000123400
説明不足ですみません

回答No.2

anotherone

http://www.happy2-island.com/vbs/cafe02/capter00506.shtml
Set myRS = myDB.OpenRecordset("SELECT * FROM テーブル WHERE 条件")
myRS(0).Name　は項目名　myRS(0).Value　は項目の内容
myRS("項目1").Name　は項目名　myRS("項目1").Value　は項目の内容
0から始まる番号か　項目名で指定します
http://www.atmarkit.co.jp/fnetwork/rensai/sql01/sql1.html
http://www.rfs.jp/sb/sql/03/03_2.html
http://ash.jp/db/sql.htm
http://access-sql.seesaa.net/article/37627261.html