エクセルで開いていないbookのセルの値が欲しい

エクセルが少し使える程度です。
1000個のブック内に同じ名前のシートが1個づつできています。
その1000個のシート毎にK列の最大値を求めたいのですが、
1000個のシートを”全て開くことなしに”できないでしょうか？

シート名は整理表から求めることができます。
シート名="C9&"\"&D9&"\["&A9&".xls]sheet1'"

シートが開いていれば、以下で求めることができるのですが。
=max（シート名!$k$2：$k$1250)

No.2 ベストアンサー 回答者： deus_ex_machina 回答日時： 2007/05/16 15:58

ご質問の内容を取り違えていましたらすみません。

状況・課題の確認も兼ねて冗長な説明になるかと思いますがご容赦ください。

まず、開いているブックへの参照は
Ａ：=max([Sample.xls]Sheet1!$k$2：$k$1250)
といった形になりますよね。

ここでリンク元のブックを閉じると、
数式は自動的に
Ｂ：=max('D:\***\[Sample.xls]Sheet1'!$k$2：$k$1250)
といった形に変わります。

この状態であれば、閉じたブックの値を参照することができます。
また、Ｂの形の数式を直接入力した場合でも
閉じたブックの値を参照できます。

問題は、対象のブックが大量にある場合に
Ｂの形の数式を順次入力していくのは現実的でない
ということだろうと思います。

対象ブックが開いている場合には、indirectを使用して
Ｃ：=max(INDIRECT("'"&A1&":\"&B1&"\["&C1&".xls]Sheet1'!$k$2：$k$1250"))
とすれば参照を動的に生成することができますが、
indirectでは閉じたブックを参照することはできません。

で、ようやく対策ですが、
Ｂの形の数式自体を文字列として生成し、
Ｄ：="=max('"&A1&":\"&B1&"\["&C1&".xls]Sheet1'!$k$2：$k$1250)"
メモ帳等を経由して貼り付けることで、
イの形の数式を一気に入力することができます。
(エクセルからエクセルへ直接の値貼り付けや数式貼り付けでは不可）

ただし、対象のブックが動いた場合には
その都度参照を貼りなおす必要がありますので、
対象のブックが頻繁に動くような場合には、
マクロを利用するほかはなかろうと思います。
１．#1さんの方法で直接参照する。
２．「整理表」の変更をイベントで取得して参照数式を書き換える。
３．リンク元ブックをバックグラウンドで開いてしまう（！）
といったアプローチが考えられます。

この回答へのお礼

> indirectでは閉じたブックを参照することはできません。
→　indirectでなければ閉じたブックを参照することができますが、
エクセルからエクセルへ直接の値貼り付けや数式貼り付けでは不可
なので、メモ帳等を経由して一気に貼り付ける。

と理解しました。ここで、「メモ帳等を経由して」とはどういうことでしょうか？

なお、マクロについて、#1さんのアドバイスを見よう見真似で試行錯誤中です。

ありがとうございました。

お礼日時：2007/05/16 16:57

No.4 回答者： noname#97729 回答日時： 2007/05/17 13:06

' フォルダ内のファイル名で文字列作成

Option Explicit
Dim strLine
Dim Fs
Dim objOutput
Dim oTarget
Dim oFile
Dim FileEx
Dim t
Const ForWriting = 2

Set Fs = WScript.CreateObject("Scripting.FileSystemObject")

Set objOutput = Fs.OpenTextFile("Maxresult.csv", ForWriting, True) ' Maxresult.csvを書き込みモードで開く

t = 0

' 現在のフォルダ内のファイル名を格納
Set oTarget = Fs.GetFolder(".")

For Each oFile In oTarget.Files 'すべてのファイルに繰り返す

FileEx = Fs.GetExtensionName(oFile) ' ファイル名から拡張子を抜き出す
If FileEx = "xls" Then ' 拡張子を比較 (.xls)のとき処理

strLine = ""
'ここで必要な文字列(関数式)を作成します
strLine = oFile.Name & "," & "=MAX('" & oTarget.Path & "\[" & oFile.Name & "]Sheet1'!K2:K1250)"
objOutput.WriteLine strLine ' 出力ファイルに書き出す
t = t + 1

End If

Next

objOutput.Close

MsgBox "処理終了 Excel 数 = " & t


---------------------------------------------------------
VBScript でちょこっと作ってみました。
フォルダ数が少なければ、たぶん使いやすいとおもいます
（ひとつのフォルダにファイルが沢山あるとき）


スクリプトの処理内容

対象のフォルダ内のすべてエクセルのファイル名とパスを収得して
「ファイル名」と「=MAX('D:\***\[*****.xls]Sheet1'!$k$2：$k$1250)」
のテキスト形の文字列をCSVの結果に出力します


使い方
先頭の　Option Explicit　から　MsgBox　の最後までコピーして
メモ帳などに貼り付けて保存してください。（名前はなんでもいいです）
そして　拡張子を(.txt)から(.vbs)に変更します
アイコンの形がメモ帳からSの形をしたものに変わります。
ＴＥＳＴフォルダをデスクトップにでも作り、そこにエクセルファイルをコピーして
同じＴＥＳＴフォルダにSの形をしたアイコン「(.vbs)のファイル」を入れてください
で、Sの形をしたアイコンをダブルクリックするとテキストの形で関数文字列を作り
(Maxresult.csv)のファイルができます。
CSVファイルをエクセルで開くと関数が値を収得してきます。



フォルダ数が多いと使いにくいと思います。
役に立つかわかりませんが、

この回答へのお礼

ここまで考えていただき、本当にありがとうございました。
少しづつ試みてみます。

お礼日時：2007/05/17 15:08

No.3 回答者： deus_ex_machina 回答日時： 2007/05/16 18:07

#2です。

説明が判りにくかったようですみません。
indirectの説明は余計でした。

indirectでなければ（式Ｂの形であれば）
閉じたブックを参照することができますが、
式Ｂを1000パターンも手打ちするのは大変です。

そこで、数式Ｄを使って1000パターンの数式Ｂを文字列として作ります。
しかし数式Ｄが返す「数式」はあくまで文字列なので、
そのままでは求める値を返しません。
文字列扱いになっている「数式」を普通の数式としてExcelに認識させる方法として、
文字列の「数式」をコピーして、いったんメモ帳などに貼り付け、
それを再度コピーして、Excelに貼り付け直す方法があります。
「メモ帳等を経由して一気に貼り付ける。」というのはそういう意味でした。


以下繰り返しになりますが、まとめです。

（私の理解では）ご質問の内容には２つの課題(条件)があります。
条件１．「閉じたブックの内容を参照したい」
条件２．「大量のブックの内容を参照したい」

もし条件１だけであれば、
Ｂ：=max('D:\***\[Sample.xls]Sheet1'!$k$2：$k$1250)
の形で数式を入力すれば解決します。
（閉じたブックの内容を参照できます）
しかし、大量のブックに対してこの形の数式をそれぞれ入力していくのは困難です。
つまり、この方法は条件２を満足しません。

条件２だけであれば、
Ｃ：=max(INDIRECT("'"&A1&":\"&B1&"\["&C1&".xls]Sheet1'!$k$2：$k$1250"))
といった形で数式を入力してフィルすれば解決します。
（「整理表」から順次参照元を取得して参照を機械的に生成できます）
しかし、この方法では閉じたブックを参照することは出来ません。
つまり、この方法は条件１を満足しません。

そこで、条件１・２を同時に満足する方法として提示したのが、
１．Ｂの形の数式をいったん文字列として1000パターン生成する。
　Ｄ：="=max('"&A1&":\"&B1&"\["&C1&".xls]Sheet1'!$k$2：$k$1250)"
２．１で生成した文字列を数式として入力しなおす。
　１で作った1000パターンの「数式」をメモ帳に貼り付ける
　↓
　それを再度エクセルに貼り付ける。
　↓
　1000パターンの「数式」が数式としてExcelに認識され、値を返す
という方法です。

乱長文失礼しました。

この回答へのお礼

ご推察のとおり、たくさんあることが最大の問題でした。
やってみます。
重ね重ねありがとうございました。

お礼日時：2007/05/16 18:45

No.1 回答者： noname#97729 回答日時： 2007/05/16 04:54

マクロがすこし分かれば、出来ると思いますが、

がんばって作ってみてはどうでしょう。

ブックを開かないでセルのデータを読む
http://officetanaka.net/excel/vba/tips/tips28.htm

この回答へのお礼

マクロがわからないのですが、（処理の手順をどこに書くのか、どこに記録してどう起動するのか）この際がんばってみます。
ありがとうございました。

お礼日時：2007/05/16 12:58