アクセスで作成した会員名簿をエクセルで、参照・使用する事が出来るのでしょうか。

質問

アクセスで会員番号・氏名・郵便番号・住所・電話番号・生年月日等の項目を記述した会員名簿を作成しています。
●御指導いただき事
１．アクセスで作成した各項目を参照してエクセルで下記の事を行えないかと考えております。
Ａ・エクセルで作成した表に、Ｃ６：Ｃ５０５に会員番号を入力した時に、Ｅ６：Ｅ５０５に氏名を表示させる
Ｂ・封筒の宛名書き
２．参照の仕方
Ａ・アクセスの会員名簿を直接参照出来ますか、可能な場合その処理方法を。
Ｂ・アクセスの会員名簿を直接参照出来ない場合は、その処理方法を。

●アクセスは理解出来ていません、エクセルも初心者です。
　　お手数を、お掛けいたしますがよろしく御願い致します。

回答No.7

pkh4989 · ベストアンサー

すみません。
再度訂正致します。
参照カラム(C列)が固定(C7)になっていました。

'VBAで数式を設定するなら以下のようにしてください。
Sub 数式設定()
Dim wSht As Worksheet
Dim SRow As Long
Dim ERow As Long
Dim wRow As Long
'
'Set wSht = Workbooks("会員名簿参照.xls").Worksheets("sheet1") '←会員名簿を参照するエクセルファイル
Set wSht = Workbooks("Book1.xls").Worksheets("sheet1") '←会員名簿を参照するエクセルファイル

'D列に下記数式入力
'D列の何処から何処まで設定するのかわかりませんが
SRow = 2 '何処から(入力してください)
ERow = 100 '何処まで(入力してください)

For wRow = SRow To ERow
wSht.Cells(wRow, "D") = "=IF(C" & wRow & "="""","""",IF(ISERROR(VLOOKUP(C" & wRow & ",[会員名簿.xls]Sheet1!$A$1:$B$5000,2,FALSE)),""未登録です"",VLOOKUP(C" & wRow & ",[会員名簿.xls]Sheet1!$A$1:$B$5000,2,FALSE)))"
Next
End Sub

お礼

pkh4989様
度々の質問に対しご丁寧に解説していただきありがとうございました。
お蔭様で思い通りのエクスポートが出来ました。
心より感謝申し上げます。
今後もよろしく御願い申し上げます。
oguno

回答No.6

pkh4989

すみません。
VBAで数式設定に間違いがありましたので、訂正します。
Row設定が固定(2)になっていました。

'VBAで数式を設定するなら以下のようにしてください。
Sub 数式設定()
Dim wSht As Worksheet
Dim SRow As Long
Dim ERow As Long
Dim wRow As Long
'
Set wSht = Workbooks("会員名簿参照.xls").Worksheets("sheet1") '←会員名簿を参照するエクセルファイル

'D列に下記数式入力
'D列の何処から何処まで設定するのかわかりませんが
SRow = 2 '何処から(入力してください)
ERow = 100 '何処まで(入力してください)

For wRow = SRow To ERow
wSht.Cells(wRow , "D") = "=IF(C7="""","""",IF(ISERROR(VLOOKUP(C7,[会員名簿.xls]Sheet1!$A$1:$B$5000,2,FALSE)),""未登録です"",VLOOKUP(C7,[会員名簿.xls]Sheet1!$A$1:$B$5000,2,FALSE)))"
Next
End Sub

回答No.5

pkh4989

こんにちは。

以下のように修正しましたので、試してみてください。

'会員名簿に御指導いただいたVBAを記述する。
Public ADCN 'コネクト変数
Public ADRS 'レコードセット変数
Public ADCM 'コマンド変数
'ＤＢ接続
Sub SpConnect()
Dim wMdb As String
Dim wPass As String
'
wMdb = "C:\Documents and Settings\turutani\My Documents\銀嶺\筆王\顧客管理 ワーク.mdb" '← Accessのデータべース
wPass = "" '← データベースのパスワード
strDbConst = "Provider=MicroSoft.Jet.OLEDB.4.0;Data Source=" & wMdb & ";Jet OLEDB:Database Password=" & wPass
Set ADCN = CreateObject("ADODB.Connection")
Set ADCM = CreateObject("ADODB.Command")
Set ADRS = CreateObject("ADODB.Recordset")
ADCN.Open strDbConst
ADCM.CommandType = 4
ADCN.CommandTimeout = 0
Set ADCM.ActiveConnection = ADCN
End Sub
'ＤＢ切断
Sub SpDisconnect()
ADCN.Close
Set ADRS = Nothing
Set ADCM = Nothing
Set ADCN = Nothing
End Sub
'
Sub 会員名簿検索()

Dim SQL As String
Dim wSht As Worksheet
'
Set wSht = ActiveSheet
'見出し設定
wSht.Cells(1, 1) = "会員番号"
wSht.Cells(1, 2) = "氏名"
wSht.Cells(1, 3) = "郵便番号"
wSht.Cells(1, 4) = "住所１"
wSht.Cells(1, 5) = "住所２"
wSht.Cells(1, 6) = "電話番号"
wSht.Cells(1, 7) = "生年月日"
'
'ＤＢ接続
Call SpConnect
'
wRow = 1
SQL = "SELECT * FROM Q_MemberInput" '←会員名簿テーブルデータ全件を検索する
SQL = SQL & " ORDER BY MemberNo" '←会員番号順に抽出"

ADRS.Open SQL, ADCN, 3, 1, 1 '← 会員名簿テーブルをオープン
Do While ADRS.EOF = False '←抽出されたデータが終わるまで繰り返し
wRow = wRow + 1 '← 読込んだデータをワークシートへ表示する為にRowをCountUP

wSht.Cells(wRow, 1) = ADRS.Fields("MemberNo") '← ワークシートのセル(A2～)に会員番号を表示
wSht.Cells(wRow, 2) = ADRS.Fields("Name") '← ワークシートのセル(B2～)に氏名を表示
wSht.Cells(wRow, 3) = ADRS.Fields("Yuubin") '←ワークシートのセル(C2～)に郵便番号を表示
wSht.Cells(wRow, 4) = ADRS.Fields("Add1") '←ワークシートのセル(D2～)に住所１を表示
wSht.Cells(wRow, 5) = ADRS.Fields("Add2") '←ワークシートのセル(E2～)に住所２を表示
wSht.Cells(wRow, 6) = ADRS.Fields("Tel") '←ワークシートのセル(F2～)に電話番号を表示
wSht.Cells(wRow, 7) = ADRS.Fields("Birthday") '←ワークシートのセル(G2～)に生年月日を表示
ADRS.MoveNext '←次にレコードを読む
Loop
ADRS.Close
'
'ＤＢ切断
Call SpDisconnect
End Sub

'★会員名簿のEXCELカラムのサイズは各項目に合わせて設定して下さい。

'VBAで数式を設定するなら以下のようにしてください。
Sub 数式設定()
Dim wSht As Worksheet
Dim SRow As Long
Dim ERow As Long
Dim wRow As Long
'
Set wSht = Workbooks("会員名簿参照.xls").Worksheets("sheet1") '←会員名簿を参照するエクセルファイル

'D列に下記数式入力
'D列の何処から何処まで設定するのかわかりませんが
SRow = 2 '何処から(入力してください)
ERow = 100 '何処まで(入力してください)

For wRow = SRow To ERow
wSht.Cells(2, "D") = "=IF(C7="""","""",IF(ISERROR(VLOOKUP(C7,[会員名簿.xls]Sheet1!$A$1:$B$5000,2,FALSE)),""未登録です"",VLOOKUP(C7,[会員名簿.xls]Sheet1!$A$1:$B$5000,2,FALSE)))"
Next
End Sub

回答No.4

pkh4989

返事が遅くなりました。

簡単に説明致します。

ADRS.Open SQL, ADCN, 3, 1, 1 '← データベース(Access Mdb)をオープン
Do While ADRS.EOF = False '←抽出されたデータが終わるまで繰り返し
wRow = wRow + 1 '← 読込んだデータをワークシートへ表示する為にRowをCountUP
wSht.Cells(wRow, 1) = ADRS.Fields("会員番号") '← ワークシートのセル(A1～)に会員番号を表示
wSht.Cells(wRow, 2) = ADRS.Fields("氏名") '← ワークシートのセル(B1～)に氏名を表示
wSht.Cells(wRow, 3) = ADRS.Fields("郵便番号") '←上記と同様
wSht.Cells(wRow, 4) = ADRS.Fields("電話番号")
wSht.Cells(wRow, 5) = ADRS.Fields("生年月日")
ADRS.MoveNext '←次にレコードを読込み
Loop

補足

pkh4989様
ありがとうございます。

●今回の質問の主旨は、使用する会員（平均年齢７２歳）の方からアクセスからのエクスポートが難しいとの話があり、アクセスのデータを直接参照する事が出来ないかと考え質問させて頂いた次第です。

●ANo.4で御指導いただきました事を、参考に下記のように、作成しトライ致しました。
・エクセルで、会員名簿ファイルを作成する。　←　（ア）

・会員名簿に御指導いただいたVBAを記述する。
Public ADCN 'コネクト変数
Public ADRS 'レコードセット変数
Public ADCM 'コマンド変数
'ＤＢ接続
Sub SpConnect()
Dim wMdb As String
Dim wPass As String
'
wMdb = "C:\Documents and Settings\turutani\My Documents\銀嶺\筆王\顧客管理 ワーク.mdb" '← Accessのデータべース
wPass = "" '← データベースのパスワード
strDbConst = "Provider=MicroSoft.Jet.OLEDB.4.0;Data Source=" & wMdb & ";Jet OLEDB:Database Password=" & wPass
Set ADCN = CreateObject("ADODB.Connection")
Set ADCM = CreateObject("ADODB.Command")
Set ADRS = CreateObject("ADODB.Recordset")
ADCN.Open strDbConst
ADCM.CommandType = 4
ADCN.CommandTimeout = 0
Set ADCM.ActiveConnection = ADCN
End Sub
'ＤＢ切断
Sub SpDisconnect()
ADCN.Close
Set ADRS = Nothing
Set ADCM = Nothing
Set ADCN = Nothing
End Sub
'
Sub 会員名簿検索()
Dim SQL As String
Dim wSht As Worksheet
'
Set wSht = ActiveSheet
Call SpConnect
'
SQL = "SELECT * FROM Q_MemberInput" '　←　SQL = "SELECT * FROM 会員名簿"
SQL = SQL & " ORDER BY MemberNo" '　←SQL = SQL & " ORDER BY 会員番号"

ADRS.Open SQL, ADCN, 3, 1, 1 '← データベース(Access Mdb)をオープン
Do While ADRS.EOF = False '←抽出されたデータが終わるまで繰り返し
wRow = wRow + 1 '← 読込んだデータをワークシートへ表示する為にRowをCountUP

wSht.Cells(wRow, 1) = ADRS.Fields("MemberNo") '← ワークシートのセル(A1～)に会員番号を表示
'wSht.Cells(wRow, 1) = ADRS.Fields("会員番号") '← ワークシートのセル(A1～)に会員番号を表示
wSht.Cells(wRow, 2) = ADRS.Fields("Name") '← ワークシートのセル(B1～)に氏名を表示
'wSht.Cells(wRow, 2) = ADRS.Fields("氏名") '← ワークシートのセル(B1～)に氏名を表示
wSht.Cells(wRow, 3) = ADRS.Fields("Yuubin") '←上記と同様
wSht.Cells(wRow, 4) = ADRS.Fields("Add1") '←上記と同様
wSht.Cells(wRow, 5) = ADRS.Fields("Add2") '←上記と同様
wSht.Cells(wRow, 6) = ADRS.Fields("Tel") '←上記と同様

wSht.Cells(wRow, 7) = ADRS.Fields("Birthday")
ADRS.MoveNext '←次にレコードを読込み
Loop
ADRS.Close
'
Call SpDisconnect
End Sub

・会員名簿に「名簿更新」のボタンを作成する。

・会員名簿を参照するエクセルファイル
C列　→　会員番号入力列
D列　→　会員番号入力により氏名を表出する列
D列に下記数式入力
=IF(C7="","",IF(ISERROR(VLOOKUP(C7,[会員名簿.xls]Sheet1!$A$1:$B$5000,2,FALSE)),"未登録です",VLOOKUP(C7,[会員名簿.xls]Sheet1!$A$1:$B$5000,2,FALSE)))　→　（イ 数式）

何とか考えた通りに出来ましたが、一抹の不安がございますので、今一度御指導を御願い申し上げます。

●御指導いただきたき事項。
・全体の誤謬のチェック。
・（イ 数式）もVBAで記述出来るのでしょうか。
・エクセルで、会員名簿ファイルを作成する。　←　（ア）
この会員名簿の１行目には列項目（会員番号・氏名・・・）を表示したいので、御指導いただきましたVBAの修正。

●実物を御覧頂けないので大変な事とは思いますが宜しく御願い申し上げます。　　　oguno

回答No.3

pkh4989

EXCELのVBAからACCESSのデータベースを参照する例です。
参考にして下さい。

Public ADCN 'コネクト変数
Public ADRS 'レコードセット変数
Public ADCM 'コマンド変数
'ＤＢ接続
Sub SpConnect()
Dim wMdb As String
Dim wPass As String
'
wMdb = "C:\db1.mdb" '← Accessのデータべース
wPass = "" '← データベースのパスワード
strDbConst = "Provider=MicroSoft.Jet.OLEDB.4.0;Data Source=" & wMdb & ";Jet OLEDB:Database Password=" & wPass
Set ADCN = CreateObject("ADODB.Connection")
Set ADCM = CreateObject("ADODB.Command")
Set ADRS = CreateObject("ADODB.Recordset")
ADCN.Open strDbConst
ADCM.CommandType = 4
ADCN.CommandTimeout = 0
Set ADCM.ActiveConnection = ADCN
End Sub
'ＤＢ切断
Sub SpDisconnect()
ADCN.Close
Set ADRS = Nothing
Set ADCM = Nothing
Set ADCN = Nothing
End Sub
'
Sub 会員名簿検索()
Dim SQL As String
Dim wSht As Worksheet
'
Set wSht = ActiveSheet
Call SpConnect
'
SQL = "SELECT * FROM 会員名簿"
SQL = SQL & " ORDER BY 会員番号"
ADRS.Open SQL, ADCN, 3, 1, 1
Do While ADRS.EOF = False
wRow = wRow + 1
wSht.Cells(wRow, 1) = ADRS.Fields("会員番号")
wSht.Cells(wRow, 2) = ADRS.Fields("氏名")
wSht.Cells(wRow, 3) = ADRS.Fields("郵便番号")
wSht.Cells(wRow, 4) = ADRS.Fields("電話番号")
wSht.Cells(wRow, 5) = ADRS.Fields("生年月日")
ADRS.MoveNext
Loop
ADRS.Close
'
Call SpDisconnect
End Sub

(1) Alt+F11 (ツール -> マクロ -> Visual Basic Editor)
(2) 挿入 -> 標準モジュール -> 上記のモジュールを貼り付けて実行(F5を押す)する
※ シート上から実行 -> Alt+F8 (ツール -> マクロ ) -> マクロを選択して実行

補足

pkh4989様
ありがとうございます。
知識が無い為、試行錯誤しています。
修正しなければならいないと、思われる箇所を修正してみました。
（申し訳ないのですが、全くの山勘です）
●wMdb = "C:\db1.mdb" '← Accessのデータべース
・C:\Documents and Settings・・・・顧客管理 ワーク.mdb
●SQL = "SELECT * FROM 会員名簿"
SQL = SQL & " ORDER BY 会員番号"
・SQL = "SELECT * FROM Q_MemberInput"
・SQL = SQL & " ORDER BY MemberNo"
●wSht.Cells(wRow, 1) = ADRS.Fields("会員番号")
wSht.Cells(wRow, 2) = ADRS.Fields("氏名")
・wSht.Cells(wRow, 1) = ADRS.Fields("MemberNo")
・wSht.Cells(wRow, 2) = ADRS.Fields("Name")
★下記の数字のの部分は何を指定しているのかご教示お願い致します。
ADRS.Open SQL, ADCN, 3, 1, 1
Do While ADRS.EOF = False
wRow = wRow + 1
wSht.Cells(wRow, 1) = ADRS.Fields("会員番号")
wSht.Cells(wRow, 2) = ADRS.Fields("氏名")
wSht.Cells(wRow, 3) = ADRS.Fields("郵便番号")
wSht.Cells(wRow, 4) = ADRS.Fields("電話番号")
wSht.Cells(wRow, 5) = ADRS.Fields("生年月日")
★この意味も理解出来ないなら、無理だからあきらめろとお叱りを受けそうですが、完成出来なくても、もう少し勉強したいと思いますので、宜しくお願い致します。

oguno（７０歳の老人）より

回答No.2

Bickyon

単純は方法なら、Accessから目的のテーブルをExcel形式でエクスポートし、それを開いて加工するという方法もあります。その他、
・ＶＢＡでプログラムする。
・外部参照やピボットテーブルを使う。等
いずれかの方法でできますが、この場で回答するのはとても難しいです。
参考URLのサイト等を覗いて見てください。

封筒の宛名書きが主目的ならAccessのReportを使用したほうが良いような．．．

参考URL：

http://office.microsoft.com/ja-jp/excel/CH010003461041.aspx,http://www.happy2-island.com/

回答No.1

denbee

可能かどうかという質問であれば可能ですが、
VBAやらACCESSへの接続方法やら込み入った話になりますので、
書店で参考書を買い求めた方が早いと思います。