[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『Accessの特定テーブルから特定フィールドをExcelに抽出』（ぽむ）
お世話になります。 [データ.mdb]というAccessファイルがあり、a,b,c,d,e,fという六つのテーブルがあります。 これらの中から必要データをエクセルに抽出したいです。
１）必要なデータはテーブルa,c内のデータ（他のテーブルは参照しない） ２）テーブルa,c内の「手数料」「客先番号」という名前の2つのフィールドだけが必要 ３）テーブルa,c共にデータ量が60万行ほど存在する ４）使用しているExcelバージョンが2002のため、行数不足でそのまま抽出できない ５）実際に必要なデータは60万行のうち2万行程度 ６）テーブルa,c共に「売上店」という名前のフィールドから「DM」という文字列を含む行は抽出条件に含めない ７）６）によっておよそ2万行ほどになったデータをシート上に吐き出す
分かり辛く、補足が必要であれば仰って下さい。
上記のような処理を理想としているのですが、現状では
Workbooks.OpenDatabase Filename:=ThisWorkbook.Path & "\データ.mdb",CommandText:=Array("a"), CommandType:=xlCmdTable
Workbooks.OpenDatabase Filename:=ThisWorkbook.Path & "\データ.mdb",CommandText:=Array("c"), CommandType:=xlCmdTable
というコードで抽出する方法しか分からず、特定のフィールドではなくテーブルごと エクセルに抽出しています。また、６）を満たせないためオーバーフローします。
こういった処理を2002で行うことは不可能でしょうか？ とりあえず分割してでもシート上に吐き出しさえすれば後の処理で不要データは削除し、まとめられると思うのですが・・・。
不可能であれば2007を導入する事も検討しています。 何方かご教授頂ければ幸いです。宜しくお願い致します。
VBA で DAO 使用してデータを取得や、Access で クエリアクションを設定する 方法もありますが、今回の要件だけだったら下記のやり方などが手軽ではない でしょうか。 http://www.eurus.dti.ne.jp/~yoneyama/Excel/mous_jyo/jyo-7.html#gaibude-ta
これで解決したとしても、これを機会に最新版（今は2010です）に移行しておく のも手かもｗ。 (Mook)
Accessのクエリはつくれますか？ 2万レコード抽出するクエリを 外部データ取り込みでおわりだと思います。 （水上）
>Mookさん すいません、最終的にデータは2万程度に収まる予定だったのですが ご紹介頂いた方法で頭に記述した６）の作業を試みたところ、全然収まりませんでした…。。 確認した所大きな見落としがあり、この条件だけでは2万に収まりません。 しかし他に不要なデータがなく（ここは本当に無いか今検討中ですが）、 オーバーフローが避けられない状況になってきたので本格的に最新版導入を検討しようと思います。
>水上さん Accessの知識が社内で私含め誰も持っておらず、クエリの作成もした事がありません。 Accessで必要なデータに加工した後に、エクセルで取り込む方法も良さそうですよね・・・。 上述の通り必要データの見通しが甘かったせいで、実際は削っても65536件以上のデータ数に なってしまっているので、もう少し考えて見ます・・・。
(ぽむ)
クエリ SELECT TOP 50000 a.手数料, a.客先番号 FROM a WHERE (((a.売上店) Not Like "*DM*")) ORDER BY a.客先番号; このような簡単なクエリで aというテーブルの客先番号の若い50000件とりだせます。 次の50000件はこの50000件の>Max(客先番号)と条件を追加していけば 複数のクエリに外部データ取り込みができますので あまりむずかしいことはありません。 AccessClubとかMougで相談されればすぐにできるとおもいます。 もっとも水上はMoug出入り禁止ですので。。。 （水上）
返信遅くなりすいません！
Accessの[データ.mdb]ですが、違う部署の担当から毎月送られてくる事が決定しました。 ですのでそれ自体に毎回手を加えるよりは、条件を明確にしてExcelでマクロブックを作っておき そちらで毎月抽出しようと思います。 手法までご教授頂き、申し訳ありません。 今後本格的にAccessに着手する機会があれば参考にさせて頂きます。
本日、会社に頼みExcel2007をインストールしました。（どうせなら2010の方が興味あったのですが） オーバーフローの問題は解決したので、改めてMookさんに紹介頂いた先の方法でマクロ記録し、 そこから他所のサイトを参考に不要箇所を削り、 下記のコードでとりあえずエクセル上に抽出するという関門はクリアできました。
__________________________________________________________________ Sub 抽出()
Dim Cnn As String Dim strSQL1 As String, strSQL2 As String Dim Path As String, Fname As String Dim Qname As String, Tname As String Dim ws As Worksheet
Path = "C:\My Documents" Fname = "データ" Qname = "a" Tname = "a.手数料, 手数料.客先番号"
Cnn = "ODBC;DSN=MS Access Database;" & _
"DBQ= " & Path & "\" & Fname & ".mdb;" & _
"DefaultDir= " & Path & ";" & _
"DriverId=25;" & _
"FIL=MS Access;MaxBuf"
'抽出テーブル
strSQL1 = "SELECT" & " " & Tname & " FROM " & "`" & _
Path & "\" & Fname & "`." & Qname & " " & _
Qname & " W"
'抽出条件
strSQL2 = "HERE (a.売上店 Like '%DM%')"
Set ws = ActiveSheet
With ws.QueryTables.Add(Connection:=Cnn, Destination:=ws.Range("A1"))
.CommandText = Array(strSQL1, strSQL2)
.RefreshStyle = xlOverwriteCells
.Refresh
End With
End Sub __________________________________________________________________
ここから下位バージョンのExcelで閲覧ができるように色々コード追加し編集を行います。
今までAccessのデータを扱う事がほとんどなかったため、どのような機能を使えば良いのか分からず 検索ワードも曖昧な物になってしまい、初っ端で詰んでしまっていたので大変助かりました。 有難うございました！
先の処理で何か不明点がありましたらまた質問させて頂くかもしれませんので、 その時は宜しくお願い致します。
（ぽむ）
無事処理ができたようで何よりです。 なるほど、マクロの記録にすると、普通のデータクエリなのですね。 WHERE が変なところで切れていますがｗ。
内容を理解できれば、応用もしやすいと思いますので、頑張ってください。 (Mook)
すいません、上記で一旦解決したのですが、それとは別に新たな条件でデータ抽出する必要性が出てきました。最初の条件は
> １）必要なデータはテーブルa,c内のデータ（他のテーブルは参照しない） > ２）テーブルa,c内の「手数料」「客先番号」という名前の2つのフィールドだけが必要 > ３）テーブルa,c共にデータ量が60万行ほど存在する > ４）使用しているExcelバージョンが2002のため、行数不足でそのまま抽出できない > ５）実際に必要なデータは60万行のうち2万行程度 > ６）テーブルa,c共に「売上店」という名前のフィールドから「DM」という文字列を含む行は抽出条件に含めない > ７）６）によっておよそ2万行ほどになったデータをシート上に吐き出す
というものでしたが、それとは別に以下の条件で抽出を行いたいです。
１）必要なデータはテーブルa,b,c,d,e,f全てのテーブル内のデータ ２）全てのテーブル内の全てのフィールドが必要（フィールド数は一番多いもので54個） ３）各テーブル内の「売上店」という名前のフィールドから「DM」もしくは「DW」という文字列を含む行のみを抽出する
これをExcel2003でマクロ記録したところ、フィールド数が多すぎるせいか 記録終了後にVBEを開き中身を見ると、フィールド抽出の個所が赤字になっており処理ができませんでした。（文字列が長すぎる？）
なのでExcel2007で「外部データの取り込み」からマクロ記録したところ、下記のコードになりました。
Sub Macro1() ' ' Macro1 Macro '
'
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _
"OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Password="""";User ID=Admin;Data Source=C:\My Documents\データ.mdb;Mode=Share Deny" _
, _
" Write;Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";Jet OLEDB:E" _
, _
"ngine Type=5;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLED" _
, _
"B:New Database Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale o" _
, _
"n Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False" _
), Destination:=Range("$A$1")).QueryTable
.CommandType = xlCmdTable
.CommandText = Array("a")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.SourceDataFile = "C:\My Documents\データ.mdb"
.ListObject.DisplayName = "データ"
.Refresh BackgroundQuery:=False
End With
End Sub
この抽出方法だと抽出するテーブルは選べますが、抽出するフィールドを条件指定できません。 （上記３を満たせない） 2003だと途中で条件指定するダイアログボックスが出たのですが、2007だとそのようなものがなく そのまま全てのフィールドを抽出してしまいます。 シートに抽出後に不要なデータを削除する事も可能ではありますが、 上記コードだと抽出するまでに非常に時間がかかりますますので、 できれば最初から必要な行のみを抽出したいです。 Excel2007ではフィールドに「～を含む」等の条件で抽出することは不可能でしょうか？
(ぽむ)
むしかえしでもうしわけないですが Accessの新しくおくられてきたmdbを保存 そのコピーをどこかにおき名前を変更します。 たとえば最新.mdbとします。 最新.mdbにLinkするクエリのあるmdbを別につくります。 Excelからこのクエリを外部データとりこみます。 「～を含む」 Not Like "*DM*" DMをふくまないのNot削除するだけです。
以上でおくられてきたmdbを最新.mdbを上書きするだけで。。。 （水上）
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.