統合マクロでパスの取得方法

質問

お世話になります。
集計ファイルでエリア・商品で集計をとっています。
一つのファイルにまとめたいのでエリアをシート名による串刺し
商品を統合を使って集計していますが 毎回統合ボタンを押すのが大変で
マクロを使い下記のコードを使っていますがファイルのパスで固定されているので
人に渡すとファイル名の変更、ユーザーの違いによりエラーになってしまいます。
現在開いているファイルの名前とパスをマクロに取り込み統合させる事は可能でしょうか

Sub Macro1()
'
' Macro1 Macro
' マクロ記録日 : 2009/9/29 ユーザー名 : user
'
'
Sheets("一般A").Select
Range("b6").Select
Selection.Consolidate Sources:=Array( _
"'C:\Documents and Settings\user\デスクトップ\[8期資料.xls]〇〇一般A'!R6C2:R64C9", _
"'C:\Documents and Settings\user\デスクトップ\[8期資料.xls]□□一般A'!R6C2:R64C9", _
"'C:\Documents and Settings\user\デスクトップ\[8期資料.xls]△△一般A'!R6C2:R64C9", _
"'C:\Documents and Settings\user\デスクトップ\[8期資料.xls]××一般A'!R6C2:R64C9", _
"'C:\Documents and Settings\user\デスクトップ\[8期資料.xls]●●一般A'!R6C2:R64C9", _
"'C:\Documents and Settings\user\デスクトップ\[8期資料.xls]■■一般A'!R6C2:R64C9", _
"'C:\Documents and Settings\user\デスクトップ\[8期資料.xls]▲▲一般A'!R6C2:R64C9", _
"'C:\Documents and Settings\user\デスクトップ\[8期資料.xls]＋＋一般A'!R6C2:R64C9", _
"'C:\Documents and Settings\user\デスクトップ\[8期資料.xls]※※一般A'!R6C2:R64C9", _
"'C:\Documents and Settings\user\デスクトップ\[8期資料.xls]％％一般A'!R6C2:R64C9"), _
Function:=xlSum, TopRow:=False, LeftColumn:=False, CreateLinks:=False
Sheets("一般B").Select
Range("b6").Select
Selection.Consolidate Sources:=Array( _
"'C:\Documents and Settings\user\デスクトップ\[8期資料.xls]〇〇一般B'!R6C2:R64C9", _
"'C:\Documents and Settings\user\デスクトップ\[8期資料.xls]□□一般B'!R6C2:R64C9", _
"'C:\Documents and Settings\user\デスクトップ\[8期資料.xls]△△一般B'!R6C2:R64C9", _
・
・
・
・
End Sub

DIMとかActiveWorkbook.Pathとか組み込みましたが
うまく行かないです。

どうぞ助けてください。

回答No.3

myRange · ベストアンサー

回答２、myRangeです。

●２つとも開いてない場合も書いときますね。

集計ファイル（マクロあり）、８期資料.xlsとも同じフォルダーに入れておく

Selection.Consolidate Sources:=Array( _
"'C:\Documents and Settings\user\デスクトップ\[8期資料.xls]〇〇一般A'!R6C2:R64C9", _

これは、

　Dim myPath
　myPath = "'" & ThisWorkbook.Path & "\[8期資料.xls]"

　Selection.Consolidate Sources:=Array( _
　　　　　　myPath & "〇〇一般A'!R6C2:R64C9", _

以上です。

お礼

myRange様
ありがとうございます。今後の参考になります。

現在ファイルは１ファイルにまとめています。
[8期資料.xls]の中のファイル名の取得をご教授いただけませんか
8期資料最終.xlsとか8期資料(1).xlsとかにすぐ変えられてしまうので

テキスト開いて色々並べ替えていますが流し込むとXXXです。
もう少しなんですが・・・

回答No.4

myRange

＞現在ファイルは１ファイルにまとめています。

この意味は、集計ブックはなく
ブックは、８期資料.xls　のひとつだけで、
マクロも８期資料.xlsにあるということですね。

で、あれば、パス名もブック名も不要です。

"'C:\Documents and Settings\user\デスクトップ\[8期資料.xls]〇〇一般A'!R6C2:R64C9"

これは

"〇〇一般A!R6C2:R64C9"

となります。

【注意点】

＞"〇〇一般A●'●!R6C2:R64C9"

●の間の、’(シングルクォーテション）は不要なので外すこと。

'------------------------------------------------------
【回答２での反省点】

●集計ブック　と　8期資料.xls　が同じブックの場合●、

"'[8期資料.xls]〇〇一般A'!R6C2:R64C9"
と回答しましたが、これでも間違いではありませんが
これは今回の条件と同じですから、今回の回答のように
ブック名[8期資料.xls]を省略したものを提示すべきでした。
そうすれば再質問の必要はなかったですね。
申し訳ないことです。。(^^;;;
以上です。

お礼

myRange様
ありがとうございます。

同一ファイル内だとパス・ファイル名は必要無かったのですね
大変助かりました。
こんな遅い時間にご回答頂き大変感謝いたします。

本当に助かりました。ありがとうございます。

回答No.2

myRange

●集計ブック　と　8期資料.xls　が同じブックの場合

"'C:\Documents and Settings\user\デスクトップ\[8期資料.xls]〇〇一般A'!R6C2:R64C9"

これは

"'[8期資料.xls]〇〇一般A'!R6C2:R64C9"

これでＯＫです。

------------------------------------------------------

●集計ブック　と　8期資料.xls　が別ブックで
　２つとも開かれている場合は、上記と同じ書き方でＯＫです。

------------------------------------------------------

●集計ブック　と　8期資料.xls　が別ブックで
　８期資料.xls　が開かれていない場合は
　２つとも同じフォルダーにいれ、
　Thisworkbook.Pathなどで文字列を作成してやります。

　もしこの場合であれば、再質問願います。

以上です。

お礼

ありがとうございます。

パスは記載しなくても動きました。

後はファイル名が変更された時の
"'[8期資料.xls]〇〇一般A'!R6C2:R64C9"の
ファイル名を取得する方法も分かれば助かります。

回答No.1

rivoisu

.Consolidateなんていうメソッドは使ったことが無いので勉強になりました。

さて質問のなかで「開いているファイル」とあるので
Workbooksコレクションを For Each Next でなめまわしてThiswkrobook以外のブック名を取得しこれに”〇〇一般A'!R6C2:R64C9”を追加して文字列配列を作る
その配列をSorceにする。
ということで解決しそうです。