[[20140409081928]] 『他のブックの複数シートに入力された内容を一覧ブ』(izumi) ページの最後に飛ぶ

[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]

 

『他のブックの複数シートに入力された内容を一覧ブックに表示したい』(izumi)

複雑な内容で、私の表現で理解していただけるかどうか…がんばります

 「詳細ブック」と「一覧ブック」の2つのブックあります。
 「詳細ブック」には同じ形式の表が1シート1表の形で何十シートも入っており
 各シートのD1とシート名はリンクされ「m"月"d"日"(aaa)」の表記で、
 日付(シート名)はすべて毎月の第一火曜・水曜・金曜、第二火曜・水曜・金曜…
 のように定期的?になっております。
 今回行いたいのは「一覧ブック」を作成し、一覧ブックの1シート内に第一火曜・水曜
 ・金曜、第二火曜・水曜・金曜…の日付一覧を作り、日付の横セル(日付がA1ならB1)
 を「詳細ブック」の同日のシートのE5セルとリンクさせたいのです。(詳細ブックに入
 力があったら、一覧ブックに内容が入るようにしたい)
 個別にリンクさせるなら  ='パス名[ブック名]シート名'!セル名 で可能でしたが
 すべて個別で対応するのは数が多すぎて困難であるため、何とか関数またはVBAででき
 ないかと思い相談させて頂きました。
 A1に「詳細ブック」の対応TEXTが入っているのでB2に「 ='パス名[ブック名]シート名'!
 セル名」 を入れ「シート名」部分をA1TEXT参照で出来れば一番簡単だったのですが
 それは無理なようで、つまずいてしまいました。
 長文で申し訳ありません。よろしくお願いします。

< 使用 Excel:Excel2010、使用 OS:Windows7 >


  A1に「詳細ブック」の対応TEXT というのはシート名なのでしょうか?

 そうであれば
 =INDIRECT("'パス名[詳細ブック.xlsx]"&A1&"'!E5")
 といった感じで、できないでしょうか。
(Mook) 2014/04/09(水) 12:53

Mookさん、対応ありがとうございます。
 A1に入るのはシート名です。
  =INDIRECT("'パス名[詳細ブック.xlsx]"&A1&"'!E5")
 で入れてみましたが #REF!が出て、「セルの範囲が無効」となります
 色々試したのですが「詳細ブック」を閉じていても開いていてもエラーは一緒でした。
 何か間違っているのでしょうか。
 お手数をおかけします。
(izumi) 2014/04/09(水) 16:36

 参照ファイルが 詳細ブック.xlsx という名前の前提ですが、開いている状態で、
  =INDIRECT("'[詳細ブック.xlsx]"&A1&"'!E5")
 で表示しないでしょうか。

 #REF! は参照先指定の記述が正しくないときのエラーです。

(Mook) 2014/04/09(水) 16:51


 補足
http://support.microsoft.com/kb/213933/ja
 を見ると、INDIRECT は開いているファイルのみが対象のようです。

 閉じてしまうと、参照エラーになってしまいますね。
 閉じていたままで参照したい場合は、直接参照しないとできないかもしれません。
(Mook) 2014/04/09(水) 16:58

Mookさん。色々ありがとうございます。
 詳細ブック名は実在のに変え、拡張端子も.xlsmに変えました。そこで気づいたのですが
 auto_openでシートを指定しているのですが、それが悪さしている訳でなないですよね?
 http://support.microsoft.com/kb/213933/ja 確認しました。
 残念ですが「詳細ブックを毎回開けないでも概要を確認したい」というのが今回の目的
 でしたので、INDIRECT は向いていませんでした。
 私の説明が足りなくて余計なお手数をお掛けしてしまいました。
 何か他に良い方法があればよいのですが…無理難題だったかもしれません。
(izumi) 2014/04/09(水) 17:36

 マクロ案ですが、A1:A5 にシート名が書いてある場合、A1:A5 を選択した状態で
 下記を実行すれば、B1:B5 に参照式が設定される例です。
 関数で解決しないときには、代替案としてどうでしょうか。

 真ん中の式の部分は実際の名前やパスに変えて実行してください。

 Sub Sample()
    Dim r As Range
    For Each r In Selection
        If r.Column = 1 And r.Value <> "" Then
            r.Offset(0, 1).Formula = "='D:\Data\[詳細ブック.xlsx]" & r.Value & "'!E5"
        End If
    Next
 End Sub

(Mook) 2014/04/10(木) 00:39


Mookさんへ
 すごい!完璧にに動きました。こんなことできるんですね!
 何日も悩んでいたのが吹き飛びました。
 ところで、もうひとつ贅沢をいってもよいでしょうか…
 一覧ブックには一年分の日付をA列、C列 D列(例です)ずらっと並べようと思っているのですが
 1.範囲選択ではなく、A列全部を対象とし、空セルは無視するような設定はできないでしょうか?
 2.そのマクロをブック起動時自動で動くようにすることは可能でしょうか?
 (無理ならボタンに登録します)
 何度もすいません。よろしくお願いします。
(izumi) 2014/04/10(木) 08:28

 マクロに興味を持ったのであれば、少しずつでも中身にも関心を持って
 もらえればですが、現在のコードでも空白を無視するようになっていて
 If 文中の r.Value <> ""  がその判定です。

 A列全体を対象にするのであれば、Selection を
 Intersect(ActiveSheet.UsedRange, Columns("A:A"))
(シートの使用範囲のA列が対象)に変更してください。

 ただ現在はA列以外は処理しないようにしているので(r.Column = 1 の部分)、
 A列を指定しての実行であれば、条件文は
      If r.Value <> "" Then
 だけにできます。

 起動時自動で動くようにするのであれば、ThisWorkbook シートの下に
 Private Sub Workbook_Open()
    WorkSheets("Sheet1").Activate  '// 処理対象シートを選択(シート名は実際の名前に変更)
    Sample
 End Sub

 と置けば起動時に処理されると思います。 
 (Sample は上記とセットで、適当に名前を変えた方が良いと思いますが。)
(Mook) 2014/04/10(木) 10:53

Mookさん ご指導ありがとうございます。
 ご指摘の部分を直し、A列の変換が問題なくできました。
 ついでに(A:A)を(C:C)にして、sampleCも作りましたそちらも無事動きます。
 2つのマクロを1つにできないかと、(A:A) &(C:C)(A:A),(C:C)など試みてみましたが
 上手くいきませんでした。これは可能なのでしょうか?
 Private Sub Workbook_Open() の部分、起動時に Sampleで止まってしまいます。
 CallSample にしてもダメでした。何が悪いのでしょうか?
 ここで CallSampleA & SampleC とか
 CallSampleA
 CallSampleC とかで複数のマクロを呼び出せればスムーズなのですが…
 何度もごめんなさい。
 よろしくお願いします。
(izumi) 2014/04/10(木) 12:33

 ご自身で考えて、いろいろ手を動かしているのはいいことですね。
 その結果での質問は歓迎です。

 A と C 列を同時に処理したいのであれば For Each の対象を変更すればよいかと
 思います。
 いろいろな書き方があると思いますが、今回はまずループの基本である For 文
 を使ってみましょうか。

 現在 For Each となっているところを
 Dim r As Long
 Dim c As Long
 For c=1 To 3 Step 2   '// 列のループ
     For r=1 To ActiveSheet.UsedRange.Rows.Count '// 行のループ
         If Cells(r,c).Value <> "" Then
              Cells(r,c+1).Formula = "='D:\Data\[詳細ブック.xlsx]" & Cells(r,c).Value & "'!E5"
         End If
     Next
 Next

 としてどうでしょうか。
 セルの指定の仕方は大きく二つ、
 Cells(行,列) と Range("アドレス") を覚えると良いと思います。
 Cells(2,3) と Range("C2") はどちらも C2 セルを指します。

 ですので Cells(r,c) で 処理したいセルを指し、Cells(r,c+1) はそのセルの
 右隣を指します。
(Mook) 2014/04/10(木) 13:01

Mookさま。
 マクロできました。期待通りに動くと、とても楽しいですね。

 Private Sub Workbook_Open() の部分がどうも上手く動きません。
 起動時に Sampleで止まって(黄色くなって表示)エラーがでます。
 CallSample ではないかと思い試してみましたが
 「コンパイルエラーSubまたはFunctionが定義されておりません」というエラーでダメでした。
 何が悪いのでしょうか?

(izumi) 2014/04/10(木) 14:09


 スペースが無いのが気になりますが、
 Sample だけにするか、 Call Sample となっているでしょうか。

 あと気になったのは、Sample は標準モジュールにおいているでしょうか。
(Mook) 2014/04/10(木) 14:21

Mookさま。
 全てうまく行きました!
 やはり私の無知が原因でした。
 標準モジュールではなくシート1に入れておりました。
 見よう見まねで、ほんとに少々ですがマクロはいじっておりましたが
 入力場所の区別もつかないレベルでした。
 そのあたり、これから勉強したいと思っています。
 今回は最後までお付き合いして頂き、
 本当にありがとうございました。
(izumi) 2014/04/10(木) 14:45

Mookさん
 一度全てうまく行ったのですが…どうなっているのか全く分からないのですが
 今朝、一覧ブックを開けたら、いきなりメッセージが出て
 「一覧には読み取れない内容が含まれています。このブックの内容を回復しますか?ブックの発行元が信頼
 できる場合は「はい」を」クリックしてください」と表示され、さらに「読み取れなかった内容を修復また
 は 削除することにより、ファイルを開くことができました」「削除されたレコード:/xl/worksheet1.xml
 パーツ内の数式」「削除されたレコード:/xl/calcChaim.xmlパーツ内の数式(計算のプロパティ)」とあり
 ました。
 ブックを開けると昨日までできていた Ssmpleマクロが参照先のエラーがでて、昨日までと同じ参照場所を
 入力しても同じエラーがでます。
 ブックが壊れたのかと思い、新しいブックにマクロのSsmple のみをコピペし試したのですが、同様の参照
 先エラーがでました。
 マクロは
 Formula = "='C:\Users\Desktop¥[詳細ブック.xlsm]" & r.Value & "'!E5"
 と入れてありますが、実際B列のセルに入った内容は
 ='C:\Users\[Desktop¥[詳細ブック.xlsm]5月7日(水)]Desktop¥[詳細ブック'!E5
 と何だか変な参照になっております。
 どこが悪いかわかるでしょうか?
(izumi) 2014/04/11(金) 13:36

 昨日動いたときと、現在のPCやユーザは同じものでしょうか。
 デスクトップはユーザ毎、PC毎の設定なので、絶対指定するときには注意が必要です。

 参照先のファイルを移動する場合も同様です。
(Mook) 2014/04/11(金) 14:03

MooK様。
 何度もすいません。同じPC,同じユーザーです。
 B列で #REF!でて、そこをクリックすると
  ='C:\Users\[Desktop¥[詳細ブック.xlsm]5月7日(水)]Desktop¥[詳細ブック'!E5
 が出るのですが、後半のDesktop¥[詳細ブック がおかしいように思うのですが、どうでしょうか?
 ='C:\Users\Desktop¥[詳細ブック.xlsm]" 5月7日(水) "'!E5" ならわかるのですが…
 なぜこうなってしまうのかがわかりません。
 申し訳ありません。もう一度一から設定してみますね。
(izumi) 2014/04/11(金) 15:50

 うーん、
 'C:\Users\Desktop\[詳細ブック.xlsm]" 5月7日(水) "'!E5"
 でもおかしいですね。

  "='D:\Data\[詳細ブック.xlsx]" & Cells(r,c).Value & "'!E5"
 の変更は大丈夫でしょうか。

 これは文字列としてみると
       マクロでの記述                        実際の文字
      -----------------------------------------------------------------
        "='C:\Users\Desktop\[詳細ブック.xlsx]"  ⇒  'C:\Users\Desktop\[詳細ブック.xlsx]
        Cells(r,c).Value               ⇒   5月7日(水)
        "'!E5"                         ⇒   '!E5
 ですから、結果として式に入るのは
        ='C:\Users\Desktop\[詳細ブック.xlsm] 5月7日(水)'!E5
 のはずです(式には「"」は出てきません)。

 マクロを動かした直後の式が正しくない場合は式を再確認してください。

 追伸:
 B列は文字列だと思っていましたが、もしかして数式が入っているでしょうか。
 であれば、変わるかどうかわかりませんが、
 Cells(r,c).Value を Cells(r,c).Text にしてどうでしょうか。
(Mook) 2014/04/11(金) 19:08

Mookさん 色々ありがとうございました。
 結論から言うと、私のミスが重なっていたようです。
 一覧ブックのA列の日付はセルの書式をm"月"d"日"(aaa)として表示は5月7日(水)です
 が、実際の内容は
 2014/05/07で、詳細ブックのシート名は文字列として5月7日(水)が入っているため、
 参照できなかった
 ようです。教えてもらいながらテストしていた一覧ブックは上書きしてしまったので
 定かでないのですが
 きっとA列に文字列として5月7日(水)が入っていたようです。
 A列1列で運用が可能だったので Cells(r,c).Valueではなく、
 r.Offset(0, 1).Formula = "='D:\Data\[詳細ブック.xlsx]" & r.Value & "'!E5"
 の方を試用していたので、一応& r.Value &をr.Text に変えてみましたが、2014/05/07
 は5月7日(水)に変わらないようでしたので、文字列で 5月7日(水) を入れたら動きまし
 た。変なエラーがでたのでビックリしましたが、結局ワタシの設定の問題でした。
 ところで…ストーカーみたいで大変申し訳ありませんが、
 A列日付が詳細シートになかった場合エラーが出て止まってしまうのですが、
 色々調べて無視して先に進ませるようにOn Error Resume Nextを入れたら途中では
 止まらなくなりましたが、最後には「シートの選択」が出て
 しまいます。使用するのがPC音痴の方が多いため、メッセージが出ないようにできると
 助かるのですが可能でしょうか?
 本当に何度もすいません。
(izumi) 2014/04/12(土) 13:07

 多くの方が勘違いする部分なのですが、「エラーが出ない」と「うまく処理できている」は
 同じことではありません。
 On Error Resume Next はエラーが出ても「無視する」だけで、期待通りに処理できる
 ようになったわけではないのです。

 >一覧ブックのA列の日付はセルの書式をm"月"d"日"(aaa)として表示は5月7日(水)です
 >が、実際の内容は2014/05/07で、詳細ブックのシート名は文字列として5月7日(水)が
 >入っているため、
 これを回避するのが、Value から Text への変更なので、見た目が同じであればこれで対応
 できたはずなのですが・・・。

 運用がわからないので、どのようにするのがベストか判断がつきませんが、起動時に参照
 を更新するのであれば、設定時だけでも「詳細ブック.xlsx」を開いてシートの有無を確認
 しながら式を設定するのがベストのような気がします。

 シートが無いのであれば、その際は参照式を設定しないという処理は可能でしょう。
 これまでのコードはA列にある名前は「必ず実在している」という前提ですので、ここが
 保証できない限りは、直接確認は必要になると思います。

 取りあえずの案ですが、EXCEL が 2007 以降であれば、現在の式を
    Cells(r,c+1).Formula = "='D:\Data\[詳細ブック.xlsx]" & Cells(r,c).Value & "'!E5"
 から
    Cells(r,c+1).Formula = "=IFERROR('D:\Data\[詳細ブック.xlsx]" & Cells(r,c).Text & "'!E5,""シートがあるか確認してください。"")"
 に変更して

 Sample を
 Sub Sample()
     Application.DisplayAlerts = False
     処理
     Application.DisplayAlerts = True
 End Sub
 のようにしてどうでしょうか。

 後者は On Error Resume Next ではありませんが、EXCEL の警告ダイアログを停止
 させるものです。
(Mook) 2014/04/12(土) 13:30

Mookさま
 色々と本当にお世話になりました。
 教えていただいたマクロで確認メッセージが入るようになりあした。
 アクロは奥が深くて学ぶことだらけです。
 根気強く勉強しようと思っています。
(izumi) 2014/04/13(日) 08:57

コメント返信:

[ 一覧(最新更新順) ]


YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki. Modified by kazu.