MS-query>カレントフォルダを変更したい

質問

すみません。急ぎの質問でお願いします。

アクセスでデータを作成して、エクセルのピボットテーブルで分析しようとしています。
1つのフォルダに3つファイルがあり、１：DB用MDBファイル　２：入力用MDBファイル ３：分析用XLSファイルとなっています。

いろいろな種類があり、それをフォルダごとに管理しています。
『原本』と呼ばれるフォルダがあり、新しい種類が出来るごとにフォルダをコピーして
データを入力しています。

-+-原本フォルダ　DB用MDB　入力用MDB　分析用XLS
|
+-Aフォルダ　　　DB用MDB　入力用MDB　分析用XLS
|
+-Bフォルダ　　　DB用MDB　入力用MDB　分析用XLS
|
+-Cフォルダ　　　DB用MDB　入力用MDB　分析用XLS

アクセスは、起動時にマクロを入れて開いたフォルダからDB用MDBファイルに再リンクするようにしています。

エクセルはODBCで接続しており、MS-Queryを利用してピボットテーブルにて分析しています。
ここで、接続文字列がフォルダ毎に名称が変わるので、それをうまく指定できません。
開いたフォルダのDB用MDBにつなぐだけなのでエクセルのオプションにあるカレントフォルダを空白にしたのですが、MS-Queryには反映しませんでした。

すみませんが、『開いたフォルダのDB用MDBファイルにMS-Queryが接続する』良い方法を教えてください。よろしくお願い致します。

回答No.1

IDii24 · ベストアンサー

つまりAccessは必ずExcelと一緒のフォルダーにあるということでしょうか。
であればExcelを開いたときにマクロを走らせればよいだけだと思いますけど。

Private Sub Workbook_Open()

ActiveWorkbook.RefreshAll
With ActiveWorkbook.Connections("接続1").ODBCConnection
.BackgroundQuery = True
.CommandText = Array("SELECT * FROM table名")　ここは自分で変える。
.CommandType = xlCmdSql
.Connection = Array(Array( _
"ODBC;DBQ=" & Application.ActiveWorkbook.Path & "\Access_名.accdb;DefaultDir=" & Application.ActiveWorkbook.Path & ";Driver={Microsoft Access" _
), Array( _
" Driver (*.mdb, *.accdb)};DriverId=25;FIL=MS Access;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;SafeTransactions=0;Threads=3" _
), Array(";UserCommitSync=Yes;"))
.RefreshOnFileOpen = False
.SavePassword = False
.SourceConnectionFile = ""
.SourceDataFile = ""
.ServerCredentialsMethod = xlCredentialsMethodIntegrated
.AlwaysUseConnectionFile = False
End With
With ActiveWorkbook.Connections("接続1")
.Name = "接続1"
.Description = ""
End With
ActiveWorkbook.Connections("接続1").Refresh
ActiveWorkbook.RefreshAll

end sub

つまり
Application.ActiveWorkbook.Path が現在のフォルダー名を持ってきます。
こいつをBookのOpenで毎回走らせればリフレッシュです。
あとは不都合な部分をアレンジしてみてください。何時もフォルダ変更時にやってる操作をマクロ記録してみればこういう風に書かれるのでは？

マクロ付きExcelで保管することになりますが。

お礼

IDii24さん、ありがとうございました。
結局、毎回更新は不可能だったのですが、頂いたアドバイスから「最初に開いたところでピボットテーブルを構築する」ということに致しました。Application.ActiveWorkbook.Path を使い、作成したところ、コピー先のフォルダごとに正しいデータベースに接続できることが出来ました。
本当にありがとうございました。

補足

早速のご回答ありがとうございました。
すごくありがたいソースだったのですが、当方のofficeのバージョン記載忘れがネックとなってしまいました。申し訳ありません。当方 2000/2010の両使いとなっておりまして、2000で検証したところ「ActiveWorkbook.Connections("接続1").ODBCConnection」のところでオブジェクトエラーとなりました。
マクロ記録を作成し、アレンジしようとしたのですが、同一ポイントにピボットテーブルがあると「アプリケーション定義またはオブジェクト定義のエラー」になります。
「create」という単語がまずいと思うのですが、「update」ではだめでした。ここはどういった関数になるのでしょうか？
With ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal)
.Connection = Array(Array( _
"ODBC;DSN=MS Access Database;DBQ=\\（フォルダ名+ファイル名)
.mdb;DefaultDir=\\(フォルダ名);DriverId=25;FIL=MS Access;MaxBufferS" _
), Array("ize=2048;PageTimeout=5;"))
.CommandType = xlCmdSql
.CommandText = Array( _
"SELECT * FROM `\\(フォルダ名+ファイル名`.テーブル名 テーブル名)
.CreatePivotTable TableDestination:=Range("A1"), TableName:= _
"ピボットテーブル1"
End With