〔ＶＢＡ・マクロ〕EXCELからACCESSデータをひっぱるには？

質問

当方のレベルはアクセス初級者（基本的な操作）、
エクセルはごく簡単なＶＢＡをいじる中級者です。

アクセスにて顧客マスター、商品マスターを作成しています。
エクセルで納品書を作成しています。

現状では外部データの取り込みをつかって
エクセルシートに顧客データ（シート１）と商品データ（シート２）を
取り込んだ後に、納品書（シート３）でシート１、２に
ＶＬＯＯＫＵＰ関数をかけて、納入先と商品情報を
伝票におとしこんでいます。

こんなまどろっこしいことをせずに、
エクセルからアクセスのテーブルへ直接データを取り込む方法は
ないものでしょうか？

どなたかよろしくお願いいたします。

回答No.7

venzou · ベストアンサー

>当方のレベルはアクセス初級者（基本的な操作）、
>エクセルはごく簡単なＶＢＡをいじる中級者です。

Accessのレポートと言う方向になっていますが、
この条件でしたら、エクセルで帳票もありだと思います。

その理由は、
・mdbからデータを引っ張るのはVBA＋ADOを使えばそれほど難しくない。
　Ecxel VBA経験者なら大丈夫でしょう。

・Excelの方が表現力が豊かなので、最終的な帳票の見栄えは良い。
　もちろん、作りこめばの話ですが・・・。

・ADOに慣れることは、今後VB等の他の開発言語を使う際に役立つ。
　そういう機会が有ればの話ですが・・・。

Excel帳票のメリットは他にも
・帳票をExcel形式で保存できるので、後から簡単に加工できる。
・メールで送ったりも出来る。
　（Accessは持っていないが、Excelは持っている、という人は多い）

もちろん、Accessの勉強のためレポートを使うのもありだと思いますし、
その方が簡単かもしれません。

参考のため、Excelでの方法を書いてみます。
どちらを選択するかは、お好みで。

****Excel VBA+ADO で mdbファイルからデータを取り込む例****

まずAccessの方で、帳票用のクエリを作ります。
データの入っているテーブルと、マスターテーブル等を結合して、並び順なども指定しておきます。この辺りはデータベースの方が便利ですので、ExcelのVLOOKUP関数は使わない方向で・・・。

データベースを扱う上でSQL文（クエリ）の学習は必須です。SQL文は標準化されていますので、Access以外のデータベースでも応用が利きます。覚えておいて損はないと思います。

　SQL　　SELECT　　INNER JOIN

上記のキーワードを検索すれば必要な情報は見付かると思います。この辺りが、データベースを扱う際の、最初の難関でしょう。

もっと具体的な情報が必要でしたら、テーブルの構造（テーブル名、フィールド名、主キー）を補足してください。

次に、Excelの準備ですが、VBAの画面で[ツール]→[参照設定]を開き

　Microsoft ActiveX Data Objects 2.7 Libray

を選択しておきます。（バージョンは多少異なるかも。最新の物を選択）

----サンプルソース----
　Dim cnn As New ADODB.Connection
　Dim rst As New ADODB.Recordset

　Dim dbName As String
　Dim dbPath As String

　dbName = "db1.mdb" 'MDBファイル名
　dbPath = ActiveWorkbook.Path & "\" 'MDBのPath 今回は ExcelのBook と同じフォルダを指定

　' MDB との接続を確立する。決まり文句です。
　cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & dbPath & dbName & ";"

　'レコードセットを開く。今回は"Q1"というクエリを参照のため開きます。
　'この部分で、SQL文を指定することも可能です。
　rst.Open "Q1", cnn, adOpenForwardOnly, adLockReadOnly

　'レコードが無くなるまで繰り返します。
　Do Until rst.EOF

　　'Recordset![フィールド名]で各フィールドのデータを参照できます。
　　Debug.Print rst![ID], rst![DATA]

　　rst.MoveNext '次のレコードに移動します。
　Loop

　rst.Close
　cnn.Close
　Set cnn = Nothing
　Set rst = Nothing
----------------------

これだけのコードでデータベースのデータを参照できます。
後は、ExcelのVBAの知識があれば帳票を作ることは可能でしょう。
（この部分が多少面倒かも・・・(^^;、Accessのレポートにするか悩むところです）

ADOに関しては、Webで検索すれば解説しているサイトは見付かると思いますので、詳しくはそちらで。

補足

おおおおうっ！師匠！おおおんっ！
私の気持ちを汲んでいただきありがとうございますぅぅっ！

レポート作れば簡単コールに責め立てられ
ヨヨと泣いておりました・・・
既に見栄えはそこそこいいフォーマットをつくっていたんですぅぅ！

いざ、アクセスレポートに叛旗をひるがえさんっっっ！

でもＡＤＯって何・・・・orz

回答No.6

gatt_mk

>できれば、アクセスでの納品書の作り方がわかる本・サイト等をおしえてくださいませ。。。

このあたりでいかがですか？
http://www.amazon.co.jp/o/ASIN/4898146902/503-7159227-1001546?SubscriptionId=02ZH6J1W0649DTNS6002

下記サイトでは初心者向けのACCESS開発手法の説明があります。
http://www.naboki.net/access/beginner/index.html

回答No.5

gatt_mk

できることはできますけど。

ACCESS側のレポート作成で納品書を作成するほうが、はるかに簡単だと思います。
ExcelからACCESSのDBのデータを直接使うのは、それなりにACCESSを勉強されてからでないと難しいかと思います。（はっきり言ってレポートの作成より、EXCEL側でのACCESSデータ取り込み用VBA作成のほうが桁違いに難しいです）

お礼

できれば、アクセスでの納品書の作り方が
わかる本・サイト等をおしえてくださいませ。。。

補足

了解です。

では、アクセスで納品書をつくるとして、
必要なのは
(1)レポートフォーマットの作成方法
(2)顧客データと売上データ（商品×数量）の入力方法
になりますよね。

参考書カモーーーンｗ

回答No.4

noname#60992

エクセル側からアクセスのデータの更新などがあるのなら、ＡＤＯを使うという手もあります。　ＳＱＬ文なども発行できるので、どうしてもエクセルから行いたいのであれば、このあたりを探ってみてはいかがでしょうか？　私はエクセルのＶＢＡはあまり知りませんので、詳細はよくわかりませんが、エクセルの特定のセルに、sqlの結果を入れていけばよいと思います。　
まあ、Ｎｏ２の方のおっしゃるとおりアクセスでレポートを作るほうがはるかに簡単だと思いますけれど。

補足

顧客コードと商品コードと数量を入力したら、
自動的に金額計算されたシートがでてくる・・・
入力フォームの作成方法であるとか、データのつなぎ方・・
という部分がアクセスでどうやっていいか、わかっていない

参考書をひきながらではとてもじゃないが前にすすまない
という、状態です。

皆さんが、簡単と前提にある知識が
私にとって今なんだかよくわかっていない、という
厳しい現実を前にたたずんでいる状態で
こんなことになってます。

回答No.3

nicotinism

Accessで全て行えば・・とも思いますが、完成までに時間がかかりそうですね。
Excelに MS Queryをインストールして・・リンク先（後半部分）でも良さそう。
私は使ったことはありませんけど (^^ゞ

井上 治 氏のHPです。
Microsoft Accessなど、外部で作成されたデータソースをシートに取り込む方法です
http://www.asahi-net.or.jp/~ef2o-inue/kihon/sub01_05_22.html

回答No.2

noname#182251

納品書もアクセスで作った方が、もっと楽だと考えますが。
何か不都合があるのでしょうか？

補足

初級者ゆえ
作り方がよくわかっていないんです。
参考書は買ってるんですがいまいち理解できていない。

よって慣れたエクセルのほうがいいやー

という判断に。。。笑

回答No.1

CHRONOS_0

インポートとかリンクです
インポートを自動化したいときだけ
>〔ＶＢＡ・マクロ〕
ですね