アクセス⇔エクセル データの移動

質問

エクセルでは横並び、アクセスでは縦並びで持っているデータがあるのですが、
エクセルからアクセスにエクスポートする場合は追加クエリで済むのですが、
アクセスの縦並びのデータをエクセル用に横並びにしてエクスポートするにはどの様にすればよいでしょうか？
IDごとに横並びにしたいのですが、分かる方お願いします。

回答No.3

n-jun · ベストアンサー

Accessは未体験に近いため、ExcelからＤＢにＤＡＯ（ＡＤＯが未体験）で接続して、
シートに書き出す方法を取ってみました。

ＤＢのパス名・ＤＢ名及びテーブル名はこちらのテスト環境の
ままです。
同一ＩＤの最大設定は２０になってます。

Sub　test()
　　　　'「参照設定」で　[Microsoft　DAO　3.x　Object　Library]　を参照します。
　　　　Dim　mdb　As　DAO.Database
　　　　Dim　mrs　As　DAO.Recordset
　　　　Dim　Dic　As　Object
　　　　Dim　i　As　Long,　j　As　Long,　cou　As　Long
　　　　Dim　st　As　String
　　　　Dim　v,　vv

　　　　Application.ScreenUpdating　=　False

　　　　Set　Dic　=　CreateObject("Scripting.Dictionary")
　　　　Set　mdb　=　OpenDatabase("R:\db1.mdb")
　　　　Set　mrs　=　mdb.OpenRecordset("aaa",　dbOpenTable)

　　　　If　mrs.EOF　Then
　　　　　　　　MsgBox　("データはありません")
　　　　　　　　Exit　Sub
　　　　End　If

　　　　ReDim　v(1　To　20,　1　To　mrs.RecordCount)
　　　　ReDim　vv(1　To　mrs.RecordCount)

　　　　With　Worksheets("Sheet1")
　　　　　　　　　.Cells.ClearContents
　　　　　　　　　mrs.MoveFirst
　　　　　　　　　For　cou　=　1　To　mrs.RecordCount
　　　　　　　　　　　　　st　=　Trim(mrs.Fields(0))
　　　　　　　　　　　　　If　Not　Dic.exists(st)　Then
　　　　　　　　　　　　　　　　j　=　j　+　1:　vv(j)　=　st
　　　　　　　　　　　　　　　　v(1,　j)　=　mrs.Fields(1)
　　　　　　　　　　　　　　　　Dic(st)　=　Array(1,　j)
　　　　　　　　　　　　　Else
　　　　　　　　　　　　　　　　i　=　Dic(st)(0)　+　1
　　　　　　　　　　　　　　　　v(i,　Dic(st)(1))　=　mrs.Fields(1)
　　　　　　　　　　　　　　　　Dic(st)　=　Array(i,　j)
　　　　　　　　　　　　　End　If
　　　　　　　　　　　　　mrs.MoveNext
　　　　　　　　　Next
　　　　　　　　　ReDim　Preserve　v(1　To　20,　1　To　Dic.Count)
　　　　　　　　　ReDim　Preserve　vv(1　To　Dic.Count)
　　　　　　　　　.Range("A2").Resize(Dic.Count,　1).Value　=　Application.Transpose(vv)
　　　　　　　　　.Range("B2").Resize(Dic.Count,　20).Value　=　Application.Transpose(v)
　　　　　　　　　.Range("A1").Value　=　"ＩＤ"
　　　　　　　　　With　.Range("A1").Offset(,　1).Resize(,　.Range("A1").CurrentRegion.Columns.Count　-　1)
　　　　　　　　　　　　　　.Value　=　"=""名前　""　&　column()-1"
　　　　　　　　　　　　　　.Value　=　.Value
　　　　　　　　　End　With
　　　　　　　　　mrs.Close
　　　　　　　　　mdb.Close
　　　　　　　　　Set　Dic　=　Nothing
　　　　End　With
　　　　Application.ScreenUpdating　=　True
End　Sub
＃　参照設定に注意願います。
　　Excel及びAccessはＸＰ、ＤＢはAccess2000形式でテストしました。
ご参考になるかどうか・・・

補足

＞ExcelからＤＢにＤＡＯ（ＡＤＯが未体験）
プログラムはやったことがないので意味がわからないのすが・・・
どういう意味ですか？

ExcelからデータベースをＤＡＯ形式で呼び出すということでしょうか・・・
初歩的なことが分からなくてスミマセン＾＾；

回答No.5

n-jun

>ExcelからデータベースをＤＡＯ形式で呼び出すということでしょうか・・・
>初歩的なことが分からなくてスミマセン＾＾；

DAOでデータを取得する。
http://www.asahi-net.or.jp/~ef2o-inue/vba_o/sub05_130_010.html
上記に書かれているような内容です。

なにぶん、Accessは立ち上げて少々触った程度のレベルでして、
Excelにコピペして並べ替えるのならば、こんな感じかな？と
思いました。

VBAの編集画面
http://t_shun.at.infoseek.co.jp/My_Page/Excel-VBA/vba_page03.htm
新規Bookの、ツール＞マクロ＞Visual　Basic　Editorをクリックして
画面を呼出します。

左側のプロジェクトエクスプローラーのVBAProjectで右クリック、
挿入＞標準モジュールで出てきた画面にコードをコピペ
（一部修正）して、参照設定に注意（１番目のリンク先参照）して
VBA画面を閉じます。

Excelの、ツール＞マクロ＞マクロをクリック。
マクロ名testを選択してＯＫ。

こんな感じです。

お礼

ExcelのDAOに関してですが検索すればすぐに関連することが出てきますね。
時間が無いのでＶＢＡのすべてを勉強することは出来ないかもしれませんが、概要程度は勉強しておこうと思います。
とても助かりました。
ありがとうございます。

回答No.4

n-jun

ＤＢのパス名・ＤＢ名及びテーブル名はこちらのテスト環境の
ままです。
　　　　Set　mdb　=　OpenDatabase("R:\db1.mdb")
　　　　Set　mrs　=　mdb.OpenRecordset("aaa",　dbOpenTable)
Ｒドライブ直下のdb1.mdb
テーブル名：aaa

同一ＩＤの最大設定は２０になってます。（最大項目数）
　　　　ReDim　v(1　To　20,　1　To　mrs.RecordCount)
　　　　ReDim　Preserve　v(1　To　20,　1　To　Dic.Count)
　　　　.Range("B2").Resize(Dic.Count,　20).Value　=　Application.Transpose(v)
項目数が多い場合には、２０を変更して下さい。

Set Dic = Nothing の次に
Erase v, vv　'←追加願います

回答No.2

n-jun

過去ログより
http://okwave.jp/qa2452277.html
http://okwave.jp/qa1965522.html
参考になるかもです。

補足

ありがとうございます。
過去ログで似たような質問があったのですね。
私の検索の仕方が甘かったようで助かりました。
クロス集計で確かに横列にできたのですが、以下のようになってしまいます。
ID 1 2 3 4
1熊猿
2　　　羊山羊
IDの数が増えるごとにフィールドの増えてしまうのですが・・・
私のやり方が悪いのでしょうか？

回答No.1

pbforce

1度立て並びでシートに取り込んで、コピーして行列を入替えて貼り付けは、だめですかね？

そんな単純な並びではないですか？

補足

データ量が膨大なのでただのコピー＆ペーストでは時間がかかります。
それとも、縦横をIDごとに自動的に移動できるのでしょうか？
例　アクセスデータ
ID 名前
1 熊
1 猿
2 羊
2 山羊

例　エクセルデータ
ID　名前　名前２
1 熊　猿
2 羊　山羊