EXCEL2000で外部参照を動的にしたい

質問

外部参照したい場合、　='D:\2002\05\[aaa.xls]sheet1!'A1　のように指定しますが，
ファイルのパスをあるセルに　"D:\DATA\2002\05\"　の様に定義しておき、
ファイルパスを定義したセルの内容を　"D:\DATA\2002\06\"　とかに変える事によって、
外部参照の対象を変える事はできるのでしょうか？

回答No.3

nishi6 · ベストアンサー

コードで算式を書き換えてみました。　まず、前提から
○外部参照している範囲を、C3～N52の50行、12列とします。
○セルC3が今、外部参照しているフォルダの年月部分をA1に書き込みます。
　D:\DATA\2002\05なら、年月部分の『2002\05』です
　　　　　　　　（最初の１回だけです。後は自動的に更新します）
○セルA2に新たにセットしたい年、セルB2に新たにセットしたい月を入力します。
　A1に対して、過去でも未来でも構いません。ただ、フォルダは存在する必要があります。
○参照するBookは一切、開かない。
○マクロを実行すれば、A2、B2に設定した年月による算式をC列にし、D列以降は月を増加させます。

A1、A2、B2、C3はコードの中に使っています。シートの状態を変える場合はコードも変更して下さい。
FDレベルで実行してみましたが、たいしたストレスもありません。Excel97です。

ツール→マクロ→Visual Basic Editor でVBE画面に移り、挿入→標準モジュール で標準モジュールを挿入します。 出てきたコードウインドウに下記マクロをコピーして貼り付けます。

ここから
↓
Sub setFormula()
　　Const srtAdr = "C3" '算式がセットされている左上のセル
　　Dim sNen As Integer, n As Integer '新たにセットする年の最初、年カウンタ
　　Dim sTuki As Integer, t As Integer '新たにセットする月の最初、月カウンタ
　　Dim col As Integer, rw As Integer '列、行カウンタ
　　Dim fml As String '算式
　　Dim srtFLD As String '左上のセルの今の年月
　　Dim pot As Integer '算式の書き換える位置

　　srtFLD = Range("A1")
　　sNen = ActiveSheet.Range("A2")
　　sTuki = ActiveSheet.Range("B2")

　　With ActiveSheet.Range(srtAdr)
　　　　pot = InStr(.Formula, srtFLD)
　　　　n = sNen
　　　　t = sTuki

　　　　'横に12ヶ月
　　　　For col = 1 To 12
　　　　　　t = sTuki + col - 1
　　　　　　　　If t > 12 Then
　　　　　　　　　　n = sNen + 1
　　　　　　　　　　t = t - 12
　　　　　　　　End If

　　　　　　'縦に50行
　　　　　　For rw = 1 To 50
　　　　　　　　fml = .Offset(rw - 1, col - 1).Formula
　　　　　　　　If Len(fml) > 0 Then
　　　　　　　　　　Mid(fml, pot, 7) = n & "\" & Right("0" & t, 2)
　　　　　　　　　　.Offset(rw - 1, col - 1).Formula = fml
　　　　　　　　End If
　　　　　　Next
　　　　Next
　　End With
　　Range("A1") = sNen & "\" & Right("0" & sTuki, 2)
End Sub

お礼

ありがとうございます。
なるほど，式を書き換えるという発想ですね。これなら色々とバリエーションがききますね。
列に年月もセットするように変更したり，機能アップして，実行してみました。
実際には672個のセルの計算式を書き換えるのですが，私の環境では，ほとんどストレスがありませんでした。

回答No.4

papayuka

こんにちは。

> 値をコピーしたい場合は，どの様になるのでしょうか？

Destinationを止めて、PasteSpecialにしてます。

Sub Test1()
Dim pBook As Workbook, cBook As Workbook, i As Integer
Dim myRange As Range, r As Range, s As String

　Set myRange = Sheet1.Range("A1:A3")
　Set pBook = Workbooks.Add(xlWBATWorksheet)

　i = 1
　For Each r In myRange
　　s = r.Text
　　Workbooks.Open (s)
　　Set cBook = ActiveWorkbook
　　s = Mid(s, 4, Len(s) - Len(Dir(s)) - 4)
　　pBook.Worksheets(1).Cells(1, i) = s
　　cBook.Worksheets(1).Range("A1:A50").Copy
　　pBook.Worksheets(1).Cells(2, i).PasteSpecial _
　　　　　Paste:=xlValues
　　Application.CutCopyMode = False
　　cBook.Close
　　i = i + 1
　Next r

　Set myRange = Nothing
　Set pBook = Nothing: Set cBook = Nothing
End Sub

お礼

補足に対しての回答ありがとうございます。何故か正しい値が入ってきませんでした。???
それと今回の私の環境ですと「ANo.#3」さんのと比べると実行速度にストレスがありましたので，次点とさせて頂きました。

回答No.2

papayuka

こんにちは。
リンクを貼らずにマクロで処理するのはどうでしょう、リンクは重たくなるし。。。

単純なサンプルマクロです。（エラー処理は無し）
新しいブックを作り、そこに各ブックのA1:A50をコピーして行きます。

Sub Test()
Dim pBook As Workbook, cBook As Workbook, i As Integer
Dim myRange As Range, r As Range, s As String

　Set myRange = Sheet1.Range("A1:A3")
　Set pBook = Workbooks.Add(xlWBATWorksheet)

　i = 1
　For Each r In myRange
　　s = r.Text
　　Workbooks.Open (s)
　　Set cBook = ActiveWorkbook
　　s = Mid(s, 4, Len(s) - Len(Dir(s)) - 4)
　　pBook.Worksheets(1).Cells(1, i) = s
　　cBook.Worksheets(1).Range("A1:A50").Copy _
　　　　　Destination:=pBook.Worksheets(1).Cells(2, i)
　　cBook.Close
　　i = i + 1
　Next r

　Set myRange = Nothing
　Set pBook = Nothing: Set cBook = Nothing
End Sub

使い方
１．新規ブックを開く
２．Sheet1のA1～A3に処理対象のフルパスを書く
　　例）　　　　A
　　　1　c:\2002\04\aaa.xls
　　　2　c:\2002\05\aaa.xls
　　　3　c:\2002\06\aaa.xls
３．Sheet1のシート名のタブ部分を右クリックして、コードの表示を選択
４．出てきたVBE画面のメニューから挿入－標準モジュールを選びサンプルマクロをコピペ
５．VBE画面を閉じる
６．Excelのメニューから－ツール－マクロ－マクロ－Test　を実行

参考になれば。

お礼

ありがとうございます。私はマクロ派（VBのスキルが低い）ので，VBの勉強になります。
実は参照先のセルは計算式なのです。値（計算結果）をコピーしたいのですが，
この例ですと，計算式自体がコピーされるので，値をコピーしたい場合は，どの様になるのでしょうか？
宜しくお願いします。

回答No.1

taka_tetsu

INDIRECTを使ってみてはいかがでしょうか？

補足

早速の書き込みありがとうございます。
しかし，INDIRECTの場合，外部参照先のブックを開いておく必要があるんですよ。（ファイルパスは指定できないのです。）

外部参照先ブック（ファイル名）は同じで，ファイルパスが違うものを集めたいのです。ファイル名が同じ為，参照先ファイルを開いておくことが出来ません。

\2001\04\aaa.xls　～　\2002\03\aaa.xls　のセルA1～A50を12ヶ月分参照して並べたいんです。
そして，以後，\2001\05\aaa.xls　～　\2002\04\aaa.xls の様に変動させたいのです。

どうか，お知恵をお貸し下さい。