EXCEL VBA？ 関数？

質問

お世話になります
EXCELからアクセスを見に行き必要な値を必要な位置に設置するにはどうしたらいいですか？

アクセス.mdb
ID|管理番号|使用者番号|数量|日付|
01|12E22100|12 |1 |2008/01/01
02|12345678|01 |5 |2008/01/01
03|12E22100|12 |1 |2008/01/01
04|12Y22100|12 |1 |2008/01/01
05|001234T7|015 |4 |2008/01/01
06|12E22100|12 |1 |2008/01/01

エクセル.xls
管理番号|01|2|3|4|12|015|
12E22100| | | | |3 | |
001234T7| | | | | |4 |
12345678|1 | | | | | |
エクセルがアクセスを見に行った実行結果です
ＶＢＡでこんなことって可能ですか？
そか一度一覧をエクセルのどこかに格納してから関数？
ご存知で詳しく説明できる方よろしくお願いします
できるならＶＢＡユーザーフォームで日付での絞り込みが理想です

回答No.6

noname#140971 · ベストアンサー

補足: csv に一旦出力も・・・。

まったくエクセルは操作したこともない門外漢ですが・・・。

[イミディエイト]
? DBSelect("SELECT * FROM tab1",",",vbCrLf)
ID,管理番号,使用者番号
1,1000,10
2,1001,20
3,1002;30

このデータを cvs ファイルで出力しシートに呼び込むなんてこともしたことがあります。
まあ、エクセルに整形表示する手順を省けないかと思ってやったこと。

FileWrite "XXX.csv", DBSelect("SELECT * FROM tab1",",",vbCrLf)

といった感じで出力し、シートに"XXX.csv"を呼び込むという手順。

シートに"XXX.csv"を呼び込むところはヘルプをみながら作成しました。
そう難しいことではなかったです。

補足

お返事ありがとうございます
なるほど一度アクセスからＣＳＶに吐き出しそれをエクセル側で拾う方法があるのですね
勉強になります

しかしＣＳＶを吸い上げたところで計算させるのはどこでどのように実行させるのかがなぞです

やはりＶＬＯＯＫＵＰ系の関数ですかね～？

回答No.5

Wendy02

こんばんは。

私は、あながち丸投げというものを否定してはいませんが、今回は、手数が多いような感じがしてくるので、「超初心者」という方には、ADOで構築するのは、VBAでは、完成までは行かないような気がしてくるのです。

>それか一度一覧をエクセルのどこかに格納してから関数？

それなら、改めて、関数で検索すればよいです。
Excelに移してしまえば、概ね、簡単に出来ます。ただし、Excel上では、やはり、データベース関数でないと、ちょっと、重くなってしまうのではないかと思います。

なお、一応、ADOでのVBAのポイントだけを書いておきます。しかし、VBAのレベルとしては、中級以上の人でないと、Excel VBAでは難しいと思います。人によっては、そのレベルを「プロのワザ」といいますが、それほどのこともないと思います。Excelだけをやっている人には、なじみが薄いです。

例：
ADOの参照設定をしておいて、

　myDBFname　="C:\test1.mdb"　'←ファイル名
　myTable = "Table1"　'←テーブル

　mySQL = "SELECT * FROM " & myTable & " WHERE "
　　Set cnADO = New ADODB.Connection
　　Connstring = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
　　myDBFname & ";Jet OLEDB:Database" & ";"

と、ADO で開いておいて、後は、以下のようなSQL で検索すればよいと思いますね。

param1 に管理番号
param2 に開始の日付 '(ここが日付型が文字型か）
param3 に終了の日付
で絞り込みます。

Set rsADO = New ADODB.Recordset　'インスタンスを起こし、

rsADO.Open mySQL &"管理番号 =" &　param1 & "AND 日付 Between #" & param2 &"# And #" & param3 &"#; "

としていけば、取れると思います。

それから、確かに、imogasiさんのご指摘の、MSクエリは一理ありますね。Excelさえあれば、MSクエリは使えます。基本的には、VBAは必要ありません。SQL は、MS式に書く必要がありますが、構文自体は、実際に、パラメータクエリにしてしまえばよいわけです。

検索するときに、QueryTable オブジェクトを使っていたはずですから、それを応用すれば、VBAに換えられます。記録マクロでも残りますので、QueryTable を既存のものを、再利用すればよいです。

Microsoft Help
http://office.microsoft.com/ja-jp/excel/HP102161131041.aspx#1

Excel豆知識41: エクセル：外部データの取り込み(パラメータクエリの利用）
http://www11.plala.or.jp/koma_Excel/contents6/mame6041/mame604101.html

補足

お返事ありがとうございます
ＡＤＯについては自分で生成したものではないですが
使用可能です

一度どこかのシートに落とし込みをしてまでは可能です
しかしどのような関数を使えば良いかわかりません
ＭＳクエリは試しましたがＡＤＯで値をＤＬできる以上必要はないですね

関数での検索方法あればご教授願います
当方としてはセルに関数を入力していくしかないと思い始めています

回答No.4

noname#79209

> あなたのスキルでは回答できない代物です。
と言われそうでコワイのですが、

むしろ、アクセス側のフォームで日付で絞り込んで、クロス集計クエリを作成し
これをエクセルファイルとしてエクスポートしたほうが楽なように思えますが．．．

補足

お返事ありがとうございます

そうですねAccess側でならできそうなのですが
フォーマットが決まっていますので・・・

逆にフォーマットに関数を入れたらいけそうなのですがその関数もわからず・・・・

やはりピボットなのですかね～

回答No.3

imogasi

>エクセルがアクセスを見に行った実行結果です
通常ではエクセルではこういうことは出来ないはず。何も書いてないが、ＭＳクエリを手操作したということですか。
データー外部データの取り込みー・・・
ーー
もしそれなら、エクセルのマクロの記録をとると、コードがわかるので、そのコードを適当に修正したら。
ーー
ＭＳクセス（ソフト）があれば、クエリで絞って、それをエクセルにエクスポートしたほうが（エクセルを通じて何でもやるより）簡単では無いですか。
ーーー
＞ＶＢＡでこんなことって可能ですか？
アクセスのモジュールや、エクセルＶＢＡの仕様経験あるのかな。
なければ現段階では質問は丸投げです。

補足

imogasiさんお返事ありがとうございます

imogasiさんの現状はＡＤＯ接続での関数が筋になるのかな？
ありがとうございました。

ＶＢＡ等の作成はしたことありますが
超初心者です
参考ＨＰをみながら作れる程度です

回答No.2

noname#140971

tab1:

ID__管理番号__使用者番号
_1______1000__10
_2______1001__20
_3______1002__30

エクセル:

1000__10
1001__20
1002__30

A列=DBLookup("管理番号", "tab1", "ID=" & 1)
A列=DBLookup("管理番号", "tab1", "ID=" & 2)
A列=DBLookup("管理番号", "tab1", "ID=" & 3)
B列=DBLookup("使用者番号", "tab1", "ID=" & 1)
B列=DBLookup("使用者番号", "tab1", "ID=" & 2)
B列=DBLookup("使用者番号", "tab1", "ID=" & 3)

と、エクセルの各セルに参照する式を書くという方法もあります。
こんな方法でよかったら DBLookup関数を補足します。
ただ、 DBLookup関数は、単にSQL文の実行結果を一つだけ戻すシンプルな関数です。
だから、補足するまでもないかもです。

[イミディエイト]
? DBSelect("SELECT * FROM tab1",,vbCrLf)
1;1000;10;
2;1001;20;
3;1002;30;

なお、このように各レコード毎のデータを列単位で区切って取得するのも手です。
この場合は、これらを For-Next等でエクセルの所定の位置にシコシコと表示します。

補足

ん～
なんかむずかしいですね
いってることが半分ぐらいしかりかいできませんが・・・

回答No.1

FEX2053

Excelのピボットテーブルを使えば一発ではないでしょうか？
http://hp.vector.co.jp/authors/VA014071/tips/xl_pvt1.html

補足

お返事ありがとうございます
ＰＶＴではだめです
決められたフォーマットでの仕様となります