エクセルからAccessのIDでを検出させセル指定で抜き出し

質問

お世話になります
可能かどうかも分からず質問します。
一般的にはAccessからエクセルへインポートとなるのですが
便利上Access、DBをIDで検出させ、必要な列だけセル指定でエクセルにインポートしたいのですが、エクセル側から可能ですか？
又、方法、関連サイトなどありましたら宜しくお願いします。

回答No.8

KenKen_SP · ベストアンサー

> ...2.xlsで実行をかけると1.xlsが勝手に立ち上がり

ボタンに割り当てたマクロを 1.xls を開いていない状態で確認して
見て下さい。たぶん、1.xls へのフルパス付で書かれているはずです。

マクロは基本的に「そのマクロが書かれたブックを開いた」状態で
初めて動作します。

ボタンを押したとき、1.xls に書かれたマクロを呼び出しているわけ
ですから、Excel は 1.xls を開きに行きますね。。

とりあえず、下記の点を修正します。

　　' // 該当データがあった場合
　　With ThisWorkbook.Worksheets("Sheet1") ' <-- 書き込むシート名

　　　　　　↓
　　' // 該当データがあった場合
　　With ActiveWorkbook.Worksheets("Sheet1") ' <-- 書き込むシート名

これで、1.xls が立ち上がってきますが、現在アクティブなブックの
Sheet1 に書き込まれるはずです。

1.xls が立ち上がる点については、気にしないようにするか、どう
しても気になるならアドイン（XLA）にするしかありません。

私ならアドインにしますかね...ただ、その方法まで提示することは
本スレの趣旨から外れてしまいます。また、アドインにする以上は
厳重なエラートラップをすべきなので、データや仕様・環境を想像
しながら書けるものでもありません。ご容赦を。

頑張って下さい^^

お礼

ありがとうございます
とりあえずですがpersonal.xlsに一人ずつ設定完了させました
又、アドインへの移行は言語そのものを変更させなければならない（少しだけ？）為、現状は回避させていただきました。
これから少しづつ覚えるしかありませんね
おかげさまでうまく、できたことをここでお詫び、お礼申し上げます。
ありがとうございました。

回答No.7

KenKen_SP

> やはりIDを検索させ列名1はA1へ、列名2はB2へ、列名3はF1へ
> 見たいな指定が望ましいのですがだめですか？

　# だめではないですけど、そういった具体的な仕様は初めて
　# でてきたのでは？
　# というのは横においておいて。

何となくそういった希望がでてくると思いましたので、#6 で
参考 URL を紹介いたしました。

そのページの中段に、データベースから取得したデータを項目
ごとに任意のセルに書き込むコードが掲載されています。

ただ、抽出されるデータ件数が複数の場合はどうするのです？
２件目のデータはどこに書き込むのでしょうか？
それとも、抽出されるデータは常に 1 つということが保証
されてる？

こういった説明が不足しているように思います。

コードは抜粋ですが、こんな感じでしょう。

　　' // データ抽出できたか？
　　If Rs.EOF And Rs.BOF Then
　　　　' // 該当データがなかった場合
　　　　MsgBox "該当データがありません", vbCritical
　　Else
　　　　' // 該当データがあった場合
　　　　With ThisWorkbook.Worksheets("Sheet1") ' <-- 書き込むシート名
　　　　　　.Range("A1").Value = Rs.Fields("列名1").Value
　　　　　　.Range("B2").Value = Rs.Fields("列名2").Value
　　　　　　.Range("F1").Value = Rs.Fields("列名3").Value
　　　　　　.Range("G5").Value = Rs.Fields("列名4").Value
　　　　　　.Range("G6").Value = Rs.Fields("列名5").Value
　　　　End With
　　End If

お礼

遅くなり申し訳ありません
ありがとうございます
お蔭様で、ようやく思い描いていたとおり完成しました。
しかしユーザー登録ボタンでてこずってます。
できればご教授お願いしたいのですが、、、、
質問違いで不快な気持ちになる場合は取りやめます。
どちらにせよ助かりましたありがとうございます

補足

すみませんはじめに言っておけばよかったですね
以後きおつけます。
申し訳ないのですが最後にもう一つお伺いしたいのですが質問違いになるのですが、、、
問題点
ボタン登録の件ですが1.xlsでお伺いしたマクロを作成し2.xlsで実行をかけると1.xlsが勝手に立ち上がり1.xlsに入力されます。

明細
現状ではモジュール1に
Sub フォーム()
UserForm1.Show
End Sub
を作成し
ユーザー設定コマンドのマクロボタンを作成しモジュール１を登録しています。
実行環境はワークシート名は変わらないですがBook名は毎回変わります

方法
作成したフォームをユーザー設定ボタンでマクロどの名前のxlsでも実行可能にしたい。（ワークシート名は変わりません）

質問違いで不快な思いをなされる場合は質問を取りやめます
すみません

回答No.6

KenKen_SP

VBE でユーザーフォームを挿入し、そこへ TextBox1 と CommandButton1
を配置して、下記コードをコピペします。

前回との違いを見比べて見て下さい。そうすれば、入力のインターフェース
がセル→フォームに変わったことによる違いや、一括ボンの方法ではなく、
ループさせてひとつひとつセルに転記させていく手法の雰囲気がわかると
思います。

参考URL:
http://www.asahi-net.or.jp/~ef2o-inue/vba_o/sub05_130_030.html

んで、、、すみません。今回はエディタに直接入力したのと、動作確認用
のサンプル DB をつくる時間がなかったので、実際に Excel での動作確認
をとってません。多分大丈夫だと思いますが、エラーがでたらすみません。

Private Const CONNECTION_STR = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="

' // 参照設定した場合は以下の定数宣言は不要
Private Const adOpenKeyset = 1
Private Const adLockReadOnly = 1

' // CommandButton1 クリックイベント
Private Sub CommandButton1_Click()

　　Dim Cn　　　 As Object ' ADODB.Connection
　　Dim Rs　　　 As Object ' ADODB.Recordset
　　Dim Col　　　As Object ' ADODB.Field
　　Dim lCode　　As Long
　　Dim sMdbPath As String
　　Dim sSql　　 As String
　　Dim r　　　　As Long
　　Dim c　　　　As Long

　　' // 問い合わせる ID コードを取得（※ ID フィールドは長整数型と仮定）
　　If IsNumeric(TextBox1.Text) Then
　　　 ' // 少数などがあれば切り捨てる
　　　 lCode = CLng(Int(TextBox1.Text))
　　Else
　　　 MsgBox "整数値で入力すべし", vbCritical
　　　 Exit Sub
　　End If

　　' // DB へのパス生成(要修正）
　　sMdbPath = "MDBへのフルパス"
　　' // データ転記開始行(要修正）
　　r = 3

　　' // データ抽出の SQL を生成(要修正）
　　' // 抽出データが必ず１件であると断定できる場合、データの
　　' // ソートが必要ない場合は、ORDER BY ～ は不要
　　sSql = ""
　　sSql = sSql & "SELECT ID ,列名1,列名2,列名3,列名4,列名5"
　　sSql = sSql & " FROM フォーム名かクエリ名"
　　sSql = sSql & " WHERE ID=" & CStr(lCode)
　　sSql = sSql & " ORDER BY ID"
　　' // SQL 文のデバッグ用コードです。通常時はコメントアウト
　　' Debug.Print sSql

　　' // データベースに接続
　　Set Cn = CreateObject("ADODB.Connection")
　　Cn.Open CONNECTION_STR & sMdbPath

　　' // データ抽出(今回は取得が目的なので読み取り専用）
　　Set Rs = CreateObject("ADODB.Recordset")
　　Rs.Open sSql, Cn, adOpenKeyset, adLockReadOnly

　　' // 転記先セルの初期化
　　Rows("3:" & CStr(Rows.Count)).ClearContents

　　' // データ抽出できたか？
　　If Rs.EOF And Rs.BOF Then
　　　　' // 該当データがなかった場合
　　　　MsgBox "該当データがありません", vbCritical
　　Else
　　　　' // 該当データがあった場合
　　　　Rs.MoveFirst
　　　　Do Until Rs.EOF
　　　　　　c = 0
　　　　　　For Each Col In Rs.Fields
　　　　　　　　c = c + 1
　　　　　　　　Cells(r, c).Value = Col.Value
　　　　　　Next Col
　　　　　　r = r + 1
　　　　　　Rs.MoveNext
　　　　Loop
　　End If

　　' // オブジェクトを開放
　　Rs.Close: Set Rs = Nothing
　　Cn.Close: Set Cn = Nothing

End Sub

お礼

粘り強く初心者にお相手して頂き
段階を踏んだ、ご指導等まことにありがとうございます。

補足

お世話になります
教えて頂いた物は一行に対して一行おきに貼り付ける又は貼り付ける列数などと理解いたしました。

やはりIDを検索させ列名1はA1へ、列名2はB2へ、列名3はF1へ見たいな指定が望ましいのですがだめですか？
一括ポンはがいいのですがなにか分けがあるのでしょうか？

エラーもなく動作はスムーズにいきます。

回答No.5

KenKen_SP

> [Microsoft][ODBC Vriver Manager]データソース名が長すぎます。

>> 　　' // DB へのパス生成(要修正）
>> 　　sMdbPath = ThisWorkbook.Path & "\e.mdb"

ThisWorkbook.Path というのは、このマクロが記述されたブック
が保存されているフォルダパスを返します。

MDB の保存場所等の提示がなかったので、ブックと MDB ファイル
は同一フォルダに配置するものと仮定させていただきました。

つまり、このブックが C:\Test フォルダにあるとすれば、変数
sMdbPath には C:\Test\e.mdb が代入されます。

もし、& マーク以降しか修正していないのであれば、変数の値は、

　　C:\Test\C\○○.mdb

となっているのかもしれませんね。フルパスで指定する場合は、

　　sMdbPath = MDB へのフルパス

とします。

> データ抽出の SQL を生成のところですが ～

sSql = ""
sSql = sSql & "SELECT *"
sSql = sSql & " FROM テーブルDB"
sSql = sSql & " WHERE ID=" & CStr(lCode)
sSql = sSql & " ORDER BY ID"

でどうですか？

データベースを操作する上で、SQL は基本になります。WEB 検索
等で基本的文法（SELECT、FROM、WHERE、ORDER BYなど）の意味を
調べてみてください。

SQL 文を簡易的に作る方法としては、Access なら GUI でクエリ
を作成できます。入門の段階ではこちらの方がわかりやすいかも。
それで、クエリのデザイン画面で SQL ビューにすれば、意図する
データ抽出にはどのような SQL 文を作成すれば良いかがわかります。

がんばって下さい＾＾

お礼

ご教授ありがとうございます
紳士に教えていただき勉強になりました。

補足

少し勉強させていただきました。
' // データ抽出の SQL を生成　現在はイントラの自作DB（上記）
sSql = ""
sSql = sSql & "SELECT ID ,列名1,列名2,列名3,列名4,列名5"
sSql = sSql & " FROM フォーム名かクエリ名"
sSql = sSql & " WHERE ID=" & CStr(lCode)
sSql = sSql & " ORDER BY ID"
Debug.Print sSql
で表示は致しました。
少しSQLの勉強ができ、かつ前に進めたので2度おいしいかったです

あつかましく、もう少しご教授をお願いしたいのですが、
現在は一つの行に一括貼り付けになっているのですが
セルの指定と、
フォーム内での作成をお願いします。
TXTBOXに検索値（ID）を入力しボタンクリックでの実行が望ましいです。
宜しくお願い申し上げます

回答No.4

KenKen_SP

#3 のコードを一部訂正します。

　　' // 該当データがあった場合はレコードセットの内容を一括転記
　　Range("A2").CopyFromRecordset Rs

は

　　' // 該当データがあった場合はレコードセットの内容を一括転記
　　Range("A3").CopyFromRecordset Rs

の間違い。。。失礼。

2行目に予めフィールド名を書き込んで置くとよいです。

お礼

はい了解しました。
ありがとうございます

回答No.3

KenKen_SP

[表示]-[ツールバー]-[コントロール ツールボックス] からシートに
CommandButton1 を配置し、それをダブルクリックして VBE を開きます。
そこに既に、

Private Sub CommandButton1_Click()

End Sub

という記述がありますが、それを全て削除してから 下記のコードコピペ
して下さい。エラー処理してませんが、こんな感じです。

B1 セルに抽出したい ID番号（整数値）を入力し、ボタンをクリックする
とデータをシート上に展開します。

まずは、この程度の仕組みから初めてみて下さい。

' // コードここから、、、貼付場所：シートモジュール
' //　参照設定する場合は Microsoft Active Data Object 2.x Library

' // DB 接続文字列
Private Const CONNECTION_STR = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="

' // 参照設定した場合は以下の定数宣言は不要
Private Const adOpenKeyset = 1
Private Const adLockReadOnly = 1

' // CommandButton1 クリックイベント
Private Sub CommandButton1_Click()

　　Dim Cn　　　 As Object ' ADODB.Connection
　　Dim Rs　　　 As Object ' ADODB.Recordset
　　Dim lCode　　As Long
　　Dim sMdbPath As String
　　Dim sSql　　 As String

　　' // 問い合わせる ID コードを取得（※ ID フィールドは長整数型と仮定）
　　If IsNumeric(Range("B1").Value) Then
　　　 ' // 少数などがあれば切り捨てる
　　　 lCode = CLng(Int(Range("B1").Value))
　　Else
　　　 MsgBox "整数値で入力すべし", vbCritical
　　　 Exit Sub
　　End If

　　' // DB へのパス生成(要修正）
　　sMdbPath = ThisWorkbook.Path & "\e.mdb"

　　' // データ抽出の SQL を生成(要修正）
　　sSql = ""
　　sSql = sSql & "SELECT *"
　　sSql = sSql & " FROM tMain"
　　sSql = sSql & " WHERE DatID=" & CStr(lCode)
　　sSql = sSql & " ORDER BY DatID"
　　Debug.Print sSql

　　' // データベースに接続
　　Set Cn = CreateObject("ADODB.Connection")
　　Cn.Open CONNECTION_STR & sMdbPath

　　' // データ抽出(今回は取得が目的なので読み取り専用）
　　Set Rs = CreateObject("ADODB.Recordset")
　　Rs.Open sSql, Cn, adOpenKeyset, adLockReadOnly

　　' // 転記先セルの初期化
　　Rows("3:" & CStr(Rows.Count)).ClearContents

　　' // データ抽出できたか？
　　If Rs.EOF And Rs.BOF Then
　　　　' // 該当データがなかった場合
　　　　MsgBox "該当データがありません", vbCritical
　　Else
　　　　' // 該当データがあった場合はレコードセットの内容を一括転記
　　　　Range("A2").CopyFromRecordset Rs
　　End If

　　' // オブジェクトを開放
　　Rs.Close: Set Rs = Nothing
　　Cn.Close: Set Cn = Nothing

End Sub

お礼

お返事ありがとうございます
まったくもって分かりづらい日本語をご理解していただき
VBAサンプルまで用意していただきありがとうございます
感謝感激です

補足

ありがとうございます
とりあえず動作確認をしたく
Private Sub CommandButton1_Click()
から
' // オブジェクトを開放
Rs.Close: Set Rs = Nothing
Cn.Close: Set Cn = Nothing

End Sub

まで貼り付け
DB へのパス生成(要修正）をフルパスで指定
"C\○○.mdb"
上記のように指定しました。

データ抽出の SQL を生成のところですが
sSql = ""
sSql = sSql & "SELECT *"
sSql = sSql & " FROM tMain"
sSql = sSql & " WHERE DatID=" & CStr(lCode)
sSql = sSql & " ORDER BY DatID"
Debug.Print sSql
どのように修正すればいいのかわかりません

又、今のまま実行をかけると
[Microsoft][ODBC Vriver Manager]データソース名が長すぎます。
とでます宜しくお願いいたします。

回答No.2

KenKen_SP

んー、、、VBA サンプルを書くにしても情報が少なくありませんか？
例えば、

　・MDB ファイルの保存場所
　・テーブルの情報
　　テーブル名、フィールド名、フィールドの型
　・どのように ID を指定するのか？ セル? TextBox?

のようなことです。

とりあえず、簡単なサンプルを書いてみますので、それを参考に
ご自分の環境に合わせて修正してみるなどして、動作の雰囲気を
つかんで見て下さい。

少々時間をください。

お礼

返答ありがとうございます
分かりにくい日本語で申し訳ありません

補足

お世話になります
ファイルの場所はイントラネットワーク内です
テーブル名:テーブルDB
フィールド名：ID（数値）テーブルDB1（文字列）テーブルDB2（日付）

内容がわかりくく申し訳ありません
エクセルVBAフォームにtextBox内にIDを入力しボタンクリックで
ID行にある必要なDBをセル指定で検出させるイメージです。

回答No.1

KenKen_SP

こんばんは。

Access 側で ID 抽出する 選択クエリ を作成し、それを Excel
形式でエクスポートする方が早いと思いますが。。

手っ取り早いのは、Microsoft Query の利用ですね。標準セット
アップではインストールされませんので、Office の追加インス
トールが必要になるかもしれません。

ここが参考になるかと。
http://www.asahi-net.or.jp/~ef2o-inue/kihon/sub01_05_22.html

VBA を使えば、もっと柔軟に操作できますよ。その事例も
上記のサイト内で紹介されてますので、ご参考に。

お礼

ありがとうございます
やはり締め切りしたほうが良いかな？ｗ
なにはともあれKenKen_SP様ありがとうございます

補足

早速のお返事ありがとうございます。
やはりAccess側からの操作が望ましいですかね～？

申し訳ありません補足ですがVBAフォーム形式が望ましいです。
しかしながら勉強にもなりました。

やはり不可能なのでしょうか？