Excel VBA質問箱 IV

当質問箱は、有志のボランティア精神のおかげで成り立っています。
問題が解決したら、必ずお礼をしましょうね。
本サイトの基本方針をまとめました。こちら をご一読ください。

投稿種別の選択が必要です。ご注意ください。
迷惑投稿防止のため、URLの入力を制限しています。ご了承ください。


6355 / 13644 ツリー ←次へ | 前へ→

【45675】リンク式の「値の更新」をトラップしたいのですが おやじ 07/1/10(水) 10:05 質問[未読]
【45679】Re:リンク式の「値の更新」をトラップした... ハチ 07/1/10(水) 13:53 回答[未読]
【45681】Re:リンク式の「値の更新」をトラップした... おやじ 07/1/10(水) 16:27 お礼[未読]
【45682】Re:リンク式の「値の更新」をトラップした... ハチ 07/1/10(水) 17:26 発言[未読]
【45684】Re:リンク式の「値の更新」をトラップした... おやじ 07/1/10(水) 18:09 お礼[未読]
【45687】Re:リンク式の「値の更新」をトラップした... ハチ 07/1/10(水) 18:32 発言[未読]
【45691】Re:リンク式の「値の更新」をトラップした... おやじ 07/1/11(木) 8:18 発言[未読]

【45675】リンク式の「値の更新」をトラップしたい...
質問  おやじ  - 07/1/10(水) 10:05 -

引用なし
パスワード
   こちらのサイトには大変お世話になっており、拝見させていただいております。
このたび、エクセルで作られた様式の原紙をネットワーク上のファイルサーバに置いて、版数管理を行いたいと思っています。
使用者が常にサーバから原紙を出して使ってくれると、こんなことはしなくて良いのですが・・・。
様式が変更になっていることを知らずに(忘れて?)、以前記入して自分で保管している様式の一部の文言を変更
して提出されることが往々にしてあるのです。
そこで、版数のチェック機構を持たせた原紙を作り、それを利用して作成した物を各人で取り込んだ場合でも、
取り込んだ物を起動すると、ネットワークサーバの原紙を参照させようと考えました。

具体的には、初版のサーバ保管原紙のA1セルに版数をゼロと記入します。
その初版をクライアトPCに取り込んでもらいます。
サーバ保管原紙の様式は変更される毎にA1セルの版数を変更した物を保管しておきます。
その後、クライアントPCに保管しているその様式を起動した時に、B1セルにサーバ保管原紙のA1セルを参照する
リンク式を入力させ、自分のA1セルにすでに入っている版数とB1セルで得たサーバ保管原紙の版数を
比較させ、様式変更の有無を判断させようと思っています。
様式自体の運用が中止された場合は、サーバ保管原紙をフォルダーから削除します。

PCがネットワークにつながっていない時の検知と処理はこちらのサイトを参考にして出来たのですが、
運用を中止してフォルダーから削除されている古い様式をクライアントPCで起動すると、参照先が見つからないため
「値の更新」ダイアログが表示されリンク先を指示するように通知が出てしまいます。

ご相談の内容は、この値の更新ダイアログが発される前に感知し、値の更新ダイアログを表示させないで、運用
が中止されている事をオペレータに知らせたいのです。
エクセル2003限定で使えそうな物は見えたのですが、2000でも運用出来る、値の更新ダイアログを制御できそうなものは、
こちらの過去ログやヘルプで調べた範囲では無い様に思えました。
ローバージョンエクセルで運用できる方法を、どなたかご教示頂けませんでしょうか。

運用概略を申しますと、
1.クライアントPCは二十数台有り、エクセルは97から2003まで色々なバージョンが有ります。OSも色々です。
オペレータに相当する者は数十人です。PCに詳しい者もいれば、クリックがままならない者もいます。
2.サーバは1台づつ部署別に有り、それぞれでパス名が異なっています。
3.サーバ保管原紙のブックは、クライアントPCでは読み取り専用で開かれます。
4.1行目は「(行を)表示しない」にしています。
5.B1セルに入力されるリンク式は、起動と同時にリンク更新の画面が出ることを嫌ったので、起動後に式を入力し、
上書き保存する前に削除するようにしています。
6.方法のわからない今は、事前に「注意」と称し、値の更新ダイアログが出たらキャンセルボタンをクリック
するように指示し、その後のエラーをトラップする、ON ERROR で抜けています。
7.マクロは ThisWorkbookに書いており、Const文は Private Sub Workbook_Open()より上方に記入しています。
8.この原紙を管理する方はマクロをほとんど知らないので、あまりややこしくすると、原紙の各ブック毎に
ブック名とシート名などを変数で指定する時に間違う可能性がありますから、長文となってもブック毎に変更する
部分は出来るだけ簡素に、さらに一箇所にまとめて置きたいと思っています。
(このために、変数設定をConstで行い、最上部に書いています。私自身も原紙フォルダーへのパスチェック文は、
ほとんど理解できていないレベルです)
また、このモジュール内だけの記述にしたいと思っています。
9.ブック名やシート名を、nameプロパティーで自動取得すると都合が悪い時があるため、使っていません。
10.エラー発生時にフローが理解できるように、API何たらは使いたくありません。

実際より少し簡素にしていますが以下がチェック機構の現行文です。チェック機構自体にもっと良い方法があれば
それに越したことはございません。よろしくお願いいたします。


'---------------------------------
  Const WbName As String = "62_2"   'ブックの名前
  Const WsName As String = "管理表"  'シートの名前
  
  Const FuPa As String = "\\SV\原紙"   'ファイルサーバの原紙フォルダ
'---------------------------------


Private Sub Workbook_Open()

'ここから↓ 原紙フォルダにあるファイルを開いた場合は、検知マクロを抜ける ----------
  
  If ActiveWorkbook.Path = FuPa Then
    Exit Sub
  End If

'ここから↓ 一般注意 --------------------------
  MsgBox "この後に 「値の更新」 と題した、ファイル検索の画面が表示された場合は" & vbCrLf & _
      "[キャンセル]をクリックして下さい。", vbOKOnly + vbInformation, "ご注意"


'ここから↓ サーバ原紙フォルダにパス(?)が通らなかったら、ネット不良と判断 ----------
  If CreateObject("Scripting.FileSystemObject").FolderExists(FuPa) = False Then
      MsgBox "ネットワークが不良です。"
      ActiveWorkbook.Saved = True
      Application.Quit
    Else
    On Error GoTo エラー
      Worksheets(WsName).Range("B1").Value = "='" & FuPa & "\[" & WbName & ".xls]" & WsName & "'!$A$1"

'*****この場所にリンク更新のダイアログが出た場合、その旨表示してブックを閉じる処理をしたい******
    
    If Worksheets(WsName).Range("A1").Value <> Worksheets(WsName).Range("B1").Value Then
      MsgBox "この様式は旧版。使用できない。", vbOKOnly + vbCritical
      ActiveWorkbook.Saved = True
      Application.Quit
    End If
  End If
Exit Sub
エラー:
  MsgBox "この様式は運用を中止している。"
  ActiveWorkbook.Saved = True
  Application.Quit
End Sub

【45679】Re:リンク式の「値の更新」をトラップし...
回答  ハチ  - 07/1/10(水) 13:53 -

引用なし
パスワード
   ▼おやじ さん:

ザッと眺めてみた感じで書きますので
間違ってたらスイマセン。

>  If CreateObject("Scripting.FileSystemObject").FolderExists(FuPa) = False Then
>      MsgBox "ネットワークが不良です。"
>      ActiveWorkbook.Saved = True
>      Application.Quit
>    Else

ここでファイルがあるか
If Dir(フルパス) = "" Then でチェックして
ファイルがあるならリンク式を入れるようにしたら良いのでは?
ファイルがなければ、

>  MsgBox "この様式は運用を中止している。"
>  ActiveWorkbook.Saved = True
>  Application.Quit
>End Sub
でどうでしょう?

リンク式を削除するとこは書かれてないようですが、
閉じる前に書かれるリンク式を削除しているならいけると思います。

【45681】Re:リンク式の「値の更新」をトラップし...
お礼  おやじ  - 07/1/10(水) 16:27 -

引用なし
パスワード
   ▼ハチ さん:

ご回答ありがとうございます。
間違っていたらなんて、とんでございません。

  If CreateObject("Scripting.FileSystemObject").FolderExists(FuPa) = False Then
      MsgBox "ネットワークが不良です"
      ActiveWorkbook.Saved = True
      Application.Quit
    ElseIf Dir(FuPa & "\" & WbName & ".xls") = "" Then
      MsgBox "この様式は運用を中止している"
      ActiveWorkbook.Saved = True
      Application.Quit
    Else
      Worksheets(WsName).Range("B1").Value = "='" & FuPa & "\[" & WbName & ".xls]" & WsName & "'!$A$1"
        If Worksheets(WsName).Range("A1").Value <> Worksheets(WsName).Range("B1").Value Then
          MsgBox "この様式は旧版。使用できない。", vbOKOnly + vbCritical
          ActiveWorkbook.Saved = True
          Application.Quit
        End If
  End If

以上でバッチリ行きました。
(リンク式の削除は、Workbook_BeforeSave()でB1=""としています。)
エクセル本体の検知能力に任せた後で対応するのではなく、対象物がある事を確認して、参照式を入れるわけですね。
Dir関数のヘルプ(2000)を見ると、「該当するファイルが見つからないとき、Dir関数は長さ0の文字列を返します」と、ありました。
やりたい事から、命令文が引ける逆引きがあると便利ですね。素人だとヘルプからヒットさせる文言が浮かびません。

作動確認は直ちに取れたのですがご返事が遅れたのは、もう一つ壁に当たってしまったからです。
試行錯誤中に見つけたのですが、参照するシート名が該当するファイルに無いと、「シートの選択」画面が表示されます。
これもトラップして、例えば「原紙のシート名に誤りがある、管理者に問い合わせしろ」的な案内を出したいと思いました。

そこで、上記のElse以降に以下を入れてみたのですが、最初の「c = Workbooks(・・・」でインデックスが有効範囲に無いとエラーが出ました。
対象ファイルを開いていないとだめかと、やってみたのですが同じでした。
変数を使わずにストレートに「\\SV\原紙\62_2」としてみたのですが、同じです。

やっていることは、原紙のシート数を数え、インデックス番号を利用して、同じシート名が有れば自分のシートにリンク式を入力。
無ければ、B1セルが空欄のままなので、それを拾って、同じシート名が無いです。と表示させ抜けようとしています。
シート数は1ブックに原則1シート、多くても3シート以内です。
For Each・・NextやDo Loopを使えばスマートでしょうが、そこまで行かない所でこけてしまっています。(これだと、シートのインデックス番号が変わったり、飛び番号になったら、またエラーですものね。・・・今気が付きました)

ついでと言っては大変無礼で申し訳ないのですが、お分かりになりませでしょうか?

      c = Workbooks(FuPa & "\" & WbName).Worksheets.Count
      For i = 1 To c
        If Workbooks(FuPa & "\" & WbName).Worksheets(c).Name =WsName Then
          Worksheets(WsName).Range("B1").Value = "='" & FuPa& "\[" & WbName & ".xls]" & WsName & "'!$A$1"
        End If
      Next i
      If Worksheets(WsName).Range("B1").Value = "" Then
          MsgBox "保管されている原紙のシート名が間違っている"
          ActiveWorkbook.Saved = True
          Application.Quit
        Else
          If Worksheets(WsName).Range("A1").Value <> Worksheets(WsName).Range("B1").Value Then
            MsgBox "この様式は旧版。使用できない。", vbOKOnly+ vbCritical
            ActiveWorkbook.Saved = True
            Application.Quit
          End If
      End If

【45682】Re:リンク式の「値の更新」をトラップし...
発言  ハチ  - 07/1/10(水) 17:26 -

引用なし
パスワード
   ▼おやじ さん:

>試行錯誤中に見つけたのですが、参照するシート名が該当するファイルに無いと、「シートの選択」画面が表示されます。
>これもトラップして、例えば「原紙のシート名に誤りがある、管理者に問い合わせしろ」的な案内を出したいと思いました。

このメッセージの発生を掴めるかと言われると・・
自分にはやり方がわかりません。
有識者のResをお待ちください。
このダイアログで"キャンセル"を押した場合には、
On Error Goto で掴めそうですけど。

下記の構文なら「該当のBookを開く」必要があります。
開かずにSheet名を取得するには
こちらの「Office TANAKA」のページに詳しく書いてありますが、
自分は面倒くさいのでやったことはありません。
://www.officetanaka.net/excel/vba/tips/tips29.htm

おとなしく開くの良いと思います。
開くならわざわさリンク式を駆使する必要もないですけどね。

>そこで、上記のElse以降に以下を入れてみたのですが、最初の「c = Workbooks(・・・」でインデックスが有効範囲に無いとエラーが出ました。
>対象ファイルを開いていないとだめかと、やってみたのですが同じでした。
>変数を使わずにストレートに「\\SV\原紙\62_2」としてみたのですが、同じです。
>
>やっていることは、原紙のシート数を数え、インデックス番号を利用して、同じシート名が有れば自分のシートにリンク式を入力。
>無ければ、B1セルが空欄のままなので、それを拾って、同じシート名が無いです。と表示させ抜けようとしています。
>シート数は1ブックに原則1シート、多くても3シート以内です。
>For Each・・NextやDo Loopを使えばスマートでしょうが、そこまで行かない所でこけてしまっています。(これだと、シートのインデックス番号が変わったり、飛び番号になったら、またエラーですものね。・・・今気が付きました)
>
>ついでと言っては大変無礼で申し訳ないのですが、お分かりになりませでしょうか?
>
>      c = Workbooks(FuPa & "\" & WbName).Worksheets.Count
>      For i = 1 To c
>        If Workbooks(FuPa & "\" & WbName).Worksheets(c).Name =WsName Then
>          Worksheets(WsName).Range("B1").Value = "='" & FuPa& "\[" & WbName & ".xls]" & WsName & "'!$A$1"
>        End If
>      Next i
>      If Worksheets(WsName).Range("B1").Value = "" Then
>          MsgBox "保管されている原紙のシート名が間違っている"
>          ActiveWorkbook.Saved = True
>          Application.Quit
>        Else
>          If Worksheets(WsName).Range("A1").Value <> Worksheets(WsName).Range("B1").Value Then
>            MsgBox "この様式は旧版。使用できない。", vbOKOnly+ vbCritical
>            ActiveWorkbook.Saved = True
>            Application.Quit
>          End If
>      End If

【45684】Re:リンク式の「値の更新」をトラップし...
お礼  おやじ  - 07/1/10(水) 18:09 -

引用なし
パスワード
   ▼ハチ さん:

早速のご回答ありがとうございます。

>自分は面倒くさいのでやったことはありません。
>://www.officetanaka.net/excel/vba/tips/tips29.htm
・・・た、たしかに面倒くさそう。意味わからないし。

>おとなしく開くの良いと思います。
最後はそうですね。しかしそれがなかなか、数十人いると操作パターンを覚えてくれなくて・・・面倒だと言われるし。
しかしハチさんのおかげで、主目的である、対象のブックが無かった時の処理が出来上がりましたので、 on error で抜け、マクロがストップすることだけは回避しようと思います。
後は作成時のヒューマンエラーだけが問題のようですから、原紙の管理者に懇々と説明しておきます。
ご教示頂き大変助かりました。ありがとうございます。

【45687】Re:リンク式の「値の更新」をトラップし...
発言  ハチ  - 07/1/10(水) 18:32 -

引用なし
パスワード
   ▼おやじ さん:
>最後はそうですね。しかしそれがなかなか、数十人いると操作パターンを覚えてくれなくて・・・面倒だと言われるし。

開いてチェックするのはもちろん「マクロで」ですので
操作云々は、関係ないと思いますが。

別案としては、
「版数管理」用のWorkSheetを作って
Visible = xlSheetHidden に設定して見えなくします。
その上でブックの保護 でシート構成をロックしてパスワードをかければ、
そう簡単には該当のWorkSheetは消せなくなると思います。
版数の更新はマクロから行えば、管理用のシートを再表示させる必要もないです。

【45691】Re:リンク式の「値の更新」をトラップし...
発言  おやじ  - 07/1/11(木) 8:18 -

引用なし
パスワード
   ▼ハチ さん:

>別案としては、
最初に申し上げていなかったので申し訳なく思いますが、せっかくのご提案は私の能力ではちょっと無理です。
様式の更新は、例えば何かの記入欄を2行から3行に変更するなど、版数0を更新するばかりでは有りません。
ほとんどの更新が、様式の使用目的や表題名、ブック名、シート名は同じですが、まったく異なった内容・・・極端に言うと版数0を完全に破棄し、新たな様式に作り変えることが有るからです。
つまり、版数1にはマクロが入っていない。
その場合、原紙管理者に新たな様式に、既存のマクロを手動でコピーし、変数値を変更する方法を教えていますので、出来るだけ簡素な変更で対応したかったわけです。

共通で使用する様式が、そうコロコロ替わる様では、組織の運営自体が揺らいでいると思われますが、新たなプロジェクトを立ち上げたばかりで、云わば産声を上げたばかりの組織で運用するため、最良を求めて進化中なのです。
変更だけではなく、新たな目的の様式が作られることも有ると思います。
そのうち、固定された様式に落ち着くと思うのですが、それまで私が居られない?。(定年、リストラ?)
開発者が居なくなっても、淡々と組織で動くものを残す・・・カッコイイと思います。
ガチガチに秘密としておいて、「変更があったら、アルバイトで直してやる」・・・も一つの案ですね。(オッ、これは良い)
何か話しが変な方向に行ってしまいましたが、ガソリンで水車を回し、発電してモーターで車を動かすような、もどかしい方法を採っても、カッコイイ方向で行くつもりです。
今後も、壁に当たったらこちらにお伺いすると思いますので、その時もよろしくお願いします。
もちろん、このResも見ておりますので、どなたかが別回答頂ければ幸いです。
増してハチさん、色々とご提案ありがとうございます。

6355 / 13644 ツリー ←次へ | 前へ→
ページ:  ┃  記事番号:
2610219
(SS)C-BOARD v3.8 is Free