Excel VBA質問箱 IV

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

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


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

【38514】Excelファイルを誰かが開いているときは開けないようにする件。 kouka 06/6/5(月) 16:12 質問[未読]
【38518】Re:Excelファイルを誰かが開いているときは... Kein 06/6/5(月) 17:12 回答[未読]
【38534】Re:Excelファイルを誰かが開いているときは... kouka 06/6/6(火) 9:19 発言[未読]
【38585】Re:Excelファイルを誰かが開いているときは... kouka 06/6/6(火) 18:27 発言[未読]
【38597】Re:Excelファイルを誰かが開いているとき... Ned 06/6/7(水) 0:09 発言[未読]
【38598】Re:Excelファイルを誰かが開いているとき... Ned 06/6/7(水) 0:41 発言[未読]
【38610】Re:Excelファイルを誰かが開いているとき... Ned 06/6/7(水) 10:50 発言[未読]
【38653】Re:Excelファイルを誰かが開いているとき... 漂流民 06/6/7(水) 23:47 発言[未読]
【38671】Re:Excelファイルを誰かが開いているとき... Ned 06/6/8(木) 11:09 発言[未読]
【38662】Re:Excelファイルを誰かが開いているとき... kouka 06/6/8(木) 9:44 質問[未読]
【38665】Re:Excelファイルを誰かが開いているとき... Ned 06/6/8(木) 10:08 発言[未読]
【38685】Re:Excelファイルを誰かが開いているとき... Ned 06/6/8(木) 14:52 発言[未読]
【38740】Re:Excelファイルを誰かが開いているとき... kouka 06/6/9(金) 11:03 お礼[未読]

【38514】Excelファイルを誰かが開いているときは...
質問  kouka  - 06/6/5(月) 16:12 -

引用なし
パスワード
   お久しぶりです、こんにちは。
koukaです。

いろいろ調べてみましたが、わからなかったので教えてください。
現在ある共有端末にExcelファイルがあり、
それをみんなが見に行っているのですが、
誰かが開いているときは、開けないようにしたいのです。
(読取専用時は開けないように)

Private Sub Workbook_Open()
  If ThisWorkbook.ReadOnly Then
    MsgBox "他の方が作業中ですので開けません。"
    ThisWorkbook.Close
  End If
End Sub

単純に開けないようにするなら、上記のようなマクロでいいのですが、
これだと、最初に『〜は編集の為ロックされています。使用者は〜です。』と、
出てきてしまいますので、まずこれを出さないようにする手段がありますでしょうか?
出来ればこのファイル一つでやりたいです。
それとメッセージボックスで"他の方が作業中ですので開けません。"と、
出した時ににそのExcelに設定してあるユーザー名を、
(ツール→オプション→全般のタブの中にあるユーザー名)
表示することは可能でしょうか?
それによって誰が開いているかを特定したいです。

以上ですが、よろしくお願いします。

【38518】Re:Excelファイルを誰かが開いているとき...
回答  Kein  - 06/6/5(月) 17:12 -

引用なし
パスワード
   未テストですので、うまくいくかどうか分かりません。

Private Sub Workbook_Open()
  Dim users As Variant
  Dim i As Integer
  Dim St As String

  St = "他の方が作業中ですので開けません。" & _
  vbLf & "[現在の使用者]" & vbLf
  Application.DisplayAlerts = False
  With ThisWorkbook
    If .ReadOnly Then
     users = .UserStatus
     For i = 1 To UBound(users, 1)
       St = St & users(i, 1) & vbLf
     Next i
     St = Left$(St, Len(St) - 1)
     MsgBox St
     Application.DisplayAlerts = True
     .Close False
    End If
  End With
  Application.DisplayAlerts = True
End Sub

【38534】Re:Excelファイルを誰かが開いているとき...
発言  kouka  - 06/6/6(火) 9:19 -

引用なし
パスワード
   Keinさん、お世話になります。

提示されたマクロを試してみましたが、
『(ファイル名)は読取専用です。アクセスできません。』
と出てきて、デバックしてみると・・・
>Private Sub Workbook_Open()
>  Dim users As Variant
>  Dim i As Integer
>  Dim St As String
>
>  St = "他の方が作業中ですので開けません。" & _
>  vbLf & "[現在の使用者]" & vbLf
>  Application.DisplayAlerts = False
>  With ThisWorkbook
>    If .ReadOnly Then
     →users = .UserStatus
>     For i = 1 To UBound(users, 1)
>       St = St & users(i, 1) & vbLf
>     Next i
>     St = Left$(St, Len(St) - 1)
>     MsgBox St
>     Application.DisplayAlerts = True
>     .Close False
>    End If
>  End With
>  Application.DisplayAlerts = True
>End Sub
矢印の部分でエラーになってしまいます。

それと、やはりこれだと最初に『〜は編集の為ロックされています。使用者は〜です。』
が、出てきてしまいます。
これを回避する方法は他にありますでしょうか?
お手数ですが、よろしくお願いします。

【38585】Re:Excelファイルを誰かが開いているとき...
発言  kouka  - 06/6/6(火) 18:27 -

引用なし
パスワード
   お世話になります。

とりあえず、ユーザー名を取得する方法は、
意外に簡単に解決してしまいました。
Private Sub Workbook_Open()
  Dim users As Variant
  users = Application.UserName
  If ThisWorkbook.ReadOnly Then
    MsgBox "他の方が作業中ですので開けません。" & Chr(13) & _
        "[現在の使用者: " & users & " ]"
    ThisWorkbook.Close
  End If
End Sub

『Application.UserName』で取得できるのですね。。。
しかし、最初に出てくる『〜は編集の為ロックされています。使用者は〜です。』は、
回避の方法がまったくわかりません(T_T)
どなたか解られる方いらっしゃいますでしょうか?
お手数ですが、よろしくお願いします。

【38597】Re:Excelファイルを誰かが開いているとき...
発言  Ned  - 06/6/7(水) 0:09 -

引用なし
パスワード
   こんにちは。
駄案に近いので、十二分にテストして、不具合あったら即捨ててください^ ^;

■標準モジュール
Sub Auto_Open()
  '最初に開いた時に、ファイル属性を読み取り専用にする _
   &使用中のユーザーを書き込みor読み込み(ThisWorkbook.Pathのuse.txt)
  Dim st As String
  Dim fn As Long
  fn = FreeFile
  With ThisWorkbook
    If .ReadOnly Then
      Open .Path & "\use.txt" For Input As #fn
      Input #fn, st
      Close #fn
      MsgBox st & " Open"
      .Close savechanges:=False
    Else
      Open .Path & "\use.txt" For Output As #fn
      Print #fn, Application.UserName
      Close #fn
      Call Set_ReadOn
    End If
  End With
End Sub

Sub Auto_Close()
  '閉じる時、読み取り専用で開いていなければ、 _
   最初に開いている事になるので属性をNormalに戻す
  If Not ThisWorkbook.ReadOnly Then Call Set_Normal
End Sub

Sub Set_ReadOn()
  'ファイル属性を読み取り専用にする
  Dim MyNm As String
  Dim MyAt As VbFileAttribute
  MyNm = ThisWorkbook.FullName
  MyAt = GetAttr(MyNm)
  If Not MyAt = vbReadOnly Then SetAttr MyNm, vbReadOnly
End Sub

Sub Set_Normal()
  'ファイル属性の読み取り専用を解除
  Dim MyNm As String
  Dim MyAt As VbFileAttribute
  MyNm = ThisWorkbook.FullName
  MyAt = GetAttr(MyNm)
  If Not MyAt = vbNormal Then SetAttr MyNm, vbNormal
End Sub

■ThisWorkbookモジュール
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
  '保存時、読み取り専用を解除して保存→読み取り専用に戻す
  Cancel = True
  Call Set_Normal
  Application.EnableEvents = False
  ThisWorkbook.Save
  Application.EnableEvents = True
  Call Set_ReadOn
End Sub

つまり、ファイルを最初に開いた後、必ず読み取り専用になるように、ファイル属性を変更します。
アーカイブ属性など設定されていたら、少しやっかいですのでこのコードは使えません。
読み取り専用のままだと保存時に困るので、読み取り解除→保存→読み取り設定にします。
また、読み取り専用だと使用中ユーザーが(たぶん)取得できませんのでテキストファイルを使います。

>出来ればこのファイル一つでやりたいです。
がね...相当やっかいだと思いますけど。
安全策をとるとすれば、ダミーファイルを用意して、
そちらから開くようにしたほうが良いと思うんですけどね...
その場合は
Application.DisplayAlerts = False
が効くと思うので。

【38598】Re:Excelファイルを誰かが開いているとき...
発言  Ned  - 06/6/7(水) 0:41 -

引用なし
パスワード
   ...やっぱり駄案でしたね^ ^;
名前をつけて保存できないですから工夫必要になっちゃいます。
別途用意しないと...かな...

Sub NewName_Save()
  Dim NewNm As Variant
  With ThisWorkbook
    NewNm = Application.GetSaveAsFilename(.Name, "Microsoft Excelブック,*.xls")
    If VarType(NewNm) = vbBoolean Then Exit Sub
    Call Set_Normal
    Application.EnableEvents = False
    .SaveAs NewNm
    Application.EnableEvents = True
    Call Set_ReadOn
  End With
End Sub

【38610】Re:Excelファイルを誰かが開いているとき...
発言  Ned  - 06/6/7(水) 10:50 -

引用なし
パスワード
   こんにちは。一夜明けて、よく見たら変な事してましたね^ ^;

■標準モジュール
Sub Auto_Open()
  Dim st As String
  Dim fn As Long
  fn = FreeFile
  With ThisWorkbook
    If .ReadOnly Then
      Open .Path & "\use.txt" For Input As #fn
      Input #fn, st
      Close #fn
      MsgBox st & " Open"
      .Close False
    Else
      Open .Path & "\use.txt" For Output As #fn
      Print #fn, Application.UserName
      Close #fn
      SetAttr .FullName, vbReadOnly
    End If
  End With
End Sub

Sub Auto_Close()
  With ThisWorkbook
    If Not .ReadOnly Then SetAttr .FullName, vbNormal
  End With
End Sub

Sub Cstm_Save()
  With ThisWorkbook
    SetAttr .FullName, vbNormal
    Application.EnableEvents = False
    .Save
    Application.EnableEvents = True
    SetAttr .FullName, vbReadOnly
  End With
End Sub

Sub NewName_Save()
  Dim NewNm As Variant
  With ThisWorkbook
    Do
      NewNm = Application.GetSaveAsFilename _
        (.Name, "Microsoft Excelブック,*.xls")
      If VarType(NewNm) = vbBoolean Then Exit Sub
      If Len(Dir(NewNm)) = 0 Then Exit Do
      If MsgBox("同名ファイル在り。置き換えしますか?", vbYesNo) _
        = vbYes Then Exit Do
    Loop
    SetAttr .FullName, vbNormal
    Application.EnableEvents = False
    Application.DisplayAlerts = False
    .SaveAs NewNm
  End With
  Application.DisplayAlerts = True
  Application.EnableEvents = True
  SetAttr NewNm, vbReadOnly
End Sub

■ThisWorkbookモジュール
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
  Cancel = True
  If SaveAsUI Then
    Call NewName_Save
  Else
    Call Cstm_Save
  End If
End Sub

これくらいでいいかと思います。
ただ、OPEN中ファイルの属性変更に少し不安があるので、別案として
最初に開いた時に元ファイルを別名保存してテンポラリーファイル的な使い方をする。
その時元ファイルは読み取り専用に属性変更をする。
とすれば保存の度に属性変更する必要はなく、OpenとCloseの時だけ処理をすればいいですね^ ^

【38653】Re:Excelファイルを誰かが開いているとき...
発言  漂流民  - 06/6/7(水) 23:47 -

引用なし
パスワード
   ▼Ned さん:
こんばんわ

私も気になって拝見していたのですが、感動しました。

私の知る限り、エクセル起動時VBAで取得できるイベントの発生順は
Workbook_Open → Workbook_Activate → Workbook_WindowActivate → auto_open
なのでWorkbook_Openイベント以前に発生しているものは制御しようがない。
しかもマクロを無効にされたらお手上げ、と思っていたのですが、
確かにファイル属性が「読取専用」になっていれば、
>これだと、最初に『〜は編集の為ロックされています。使用者は〜です。』と、
このメッセージは出ません。
使用中のファイルは属性を「読取専用」にすることで回避するという
まさに「コロンブスの卵」的発想に驚きました。
(そもそも使用中にファイルの属性を変えられる事も知りませんでした)

この手法を使えば
・作業シートをxlSheetVeryHiddenにして「読取専用」時は何もさせない
・指定した絶対パス上にファイルがなければ使用できない
・使用できるユーザーを制限する
などいろいろ応用範囲も広そうです。

勉強になりました。


>ただ、OPEN中ファイルの属性変更に少し不安があるので、別案として
>最初に開いた時に元ファイルを別名保存してテンポラリーファイル的な使い方をする。
>その時元ファイルは読み取り専用に属性変更をする。
>とすれば保存の度に属性変更する必要はなく、OpenとCloseの時だけ処理をすればいいですね^ ^
これはOSやエクセルがおちた時、話がややこしくなるような気がするのですが?

【38662】Re:Excelファイルを誰かが開いているとき...
質問  kouka  - 06/6/8(木) 9:44 -

引用なし
パスワード
   こんにちは、Nedさん。
お世話になります。

すばらしいです!
『〜は編集の為ロックされています。使用者は〜です。』が、
出なくなりました。
ありがとうございます!

しかし、もう一点だけ質問させてもらえますか?
ファイルの中身に変更をかけて、その時点では保存をせず、
右上の『×』を押して消そうとすると、
『変更されています。保存しますか?』とメッセージが出てきますよね?
これで『はい』を押すと上書き保存されますが、
その直後また、『変更されています。保存しますか?』が出てきてしまいます。
何度『はい』を押しても出てきてしまうので、
『いいえ』や『キャンセル』を選ぶと、
ファイル属性が読取専用のままになってしまい、
次回から開けなくなってしまいます。
ファイル属性自体を変更すれば開けますが、
こうなった時に毎回変更するのはいやなので、
修正しようとがんばってみたのですが、
出来ませんでした(;;)

この回避方法がありましたら、教えていただけますか?
あつかましい質問で恐縮です。
よろしくお願いします。

【38665】Re:Excelファイルを誰かが開いているとき...
発言  Ned  - 06/6/8(木) 10:08 -

引用なし
パスワード
   ▼kouka さん:
こんにちは。
>何度『はい』を押しても出てきてしまうので
・・・ですよね。失礼致しました。

Sub Auto_Close()
  With ThisWorkbook
    If Not .ReadOnly Then
      SetAttr .FullName, vbNormal
      If MsgBox("保存しますか?", vbYesNo) = vbYes Then
        Application.EnableEvents = False
        .Save
        Application.EnableEvents = True
      Else
        .Saved = True
      End If
    End If
  End With
End Sub

これでどうでしょう。

でも、
>最初に開いた時に元ファイルを別名保存してテンポラリーファイル的な使い方をする。
な案。
'-------------------------------------------------------------------------------
Option Explicit
Private MyName As String 'これはセルに書込み、記録しておいたほうが良いかも
Private TmpName As String '〃(作業用ファイルで、どうせKillするので固定でいいかも)
'-------------------------------------------------------------------------------
Sub Auto_Open()
  Dim st As String
  Dim fn As Long
  fn = FreeFile
  With ThisWorkbook
    If .ReadOnly Then
      Open .Path & "\use.txt" For Input As #fn
      Input #fn, st
      Close #fn
      MsgBox st & " Open"
      .Close False
    Else
      Open .Path & "\use.txt" For Output As #fn
      Print #fn, Application.UserName
      Close #fn
      MyName = .FullName
      TmpName = .Path & "\$" & .Name
      Application.DisplayAlerts = False
      .SaveAs TmpName
      Application.DisplayAlerts = True
      SetAttr MyName, vbReadOnly
'      .Sheets("sheet1").Range("A1").Value = MyName
'      .Sheets("sheet1").Range("A2").Value = TmpName
    End If
  End With
End Sub
'-------------------------------------------------------------------------------
Sub Auto_Close()
  With ThisWorkbook
    If Not .ReadOnly Then
'      If IsEmpty(MyName) Then .Sheets("sheet1").Range("A1").Value
'      If IsEmpty(TmpName) Then .Sheets("sheet1").Range("A2").Value
      SetAttr MyName, vbNormal
      If MsgBox("元ファイルへ反映しますか?", vbYesNo) = vbYes Then
        Application.DisplayAlerts = False
        .SaveAs MyName
        Application.DisplayAlerts = True
      Else
        .Saved = True
        '現在開いているファイルを読み取り専用開いている事にする
        .ChangeFileAccess xlReadOnly
      End If
      On Error GoTo errLine
      Kill TmpName
    End If
  End With
  Exit Sub
'念の為
errLine:
  MsgBox "TEMPファイル:" & TmpName & "が削除できませんでした。確認してください。"
End Sub
'-------------------------------------------------------------------------------

【38671】Re:Excelファイルを誰かが開いているとき...
発言  Ned  - 06/6/8(木) 11:09 -

引用なし
パスワード
   ▼漂流民 さん:
こんにちは。
恐れ入ります。
ひねくれ者の発想でしたので、かなり恥かしい思いです^ ^;
(編集中であっても、手作業でファイル属性を簡単に変えられてしまうので、駄案には違い無いですし^ ^)
>使用中にファイルの属性を変えられる事
SetAttrステートメントのヘルプには
『開いているファイルの属性を変更しようとすると、実行時エラーが発生します。』
とありますから推奨はされていないと思います。
というより、上書き保存を繰り返す、メイン作業に使うファイルに対しては変えないほうが良いですし、
『使用中...』メッセージを出さないため『だけ』の処理としては、手間がかかります。

>これはOSやエクセルがおちた時、話がややこしくなるような
おっしゃる通りです。
ただ、『テンポラリーファイル的』とはいっても、作業中に別名保存したファイルを使うだけなので、
OS or XLが落ちた場合、元ファイルは読み取り専用のまま。TEMPファイルも残ってしまう。
という事になります。読み取り専用のままなのは【38597】のコードでも一緒です。
【38665】にコードをアップしてみましたのでご参考になれば幸いです。

また、蛇足ですが、今回ネックになっているのは
『出来ればこのファイル一つでやりたい』点です。
ここに拘らなければ、メッセージ制御やマクロ無効化対策などについては、
アドインや経過ファイルを利用したほうが応用範囲も広く、実用的だと思います。
(SetAttrにあまり惑わされないほうが良いと思いますヨ^ ^)

【38685】Re:Excelファイルを誰かが開いているとき...
発言  Ned  - 06/6/8(木) 14:52 -

引用なし
パスワード
   すみません。間違い訂正。
コメントアウトしている部分が不完全でした。
>'If IsEmpty(MyName) Then .Sheets("sheet1").Range("A1").Value
>'If IsEmpty(TmpName) Then .Sheets("sheet1").Range("A2").Value
'If Len(MyName) = 0 Then MyName = .Sheets("sheet1").Range("A1").Value
'If Len(TmpName) = 0 Then TmpName = .Sheets("sheet1").Range("A2").Value
最初からセルに書き込む方式にすれば、書き方も変わってくるとは思いますが...

そして、また蛇足の提案ですが、
『出来ればこのファイル一つでやりたい』点に拘られない場合の代案として、
経過ファイルを使う方法ですが、(仮にprebook.xls、メインファイル名はmainbook.xls)

prebookのThisWorkbookモジュールに

Private Sub Workbook_Open()
  Const mainbk As String = "\\pc1\data\mainbook.xls" '仮
  Dim msg As String
  Application.ScreenUpdating = False
  Application.DisplayAlerts = False
    'Notify…編集可能通知が必要な場合、True
  With Workbooks.Open(Filename:=mainbk, Notify:=False)
    Application.DisplayAlerts = True
    If .ReadOnly Then
      msg = .Sheets("sheet1").Range("a1").Value
      .Close False
    Else
      .Sheets("sheet1").Range("a1").Value = Application.UserName
      .Save
    End If
  End With
  Application.ScreenUpdating = True
  If Len(msg) > 0 Then MsgBox msg & " is using"
  ThisWorkbook.Close False
End Sub

mainbookの標準モジュールに

Sub auto_open()
  With ThisWorkbook
    .Sheets("sheet1").Range("a1").Value = Application.UserName
    .Save
  End With
End Sub

現在使用中のユーザーをセルに書き込んでいるため、Open直後にSaveします。
レスポンスが悪ければ別途txtファイルを用意したほうがいいかもしれません。
prebook.xlsを非表示にし、各ユーザーにはprebookの名前のみ伝えておけば
ユーザーサイドでは2つ目のファイルを意識する事なく運用できる気がするのです。

#もっとも、既定のメッセージではなく、あえてオリジナルのメッセージに拘られる理由は、
#なんでしょうね。運用の詳細が不明なので何とも言えませんが、
#労多くして益少なしという事にならなければ良いのですが...

【38740】Re:Excelファイルを誰かが開いているとき...
お礼  kouka  - 06/6/9(金) 11:03 -

引用なし
パスワード
   こんにちは、Nedさん。
お世話になります。

大変助かりました!
ありがとうございます(^^)b
ファイル属性変更の方向で行きたいと思います。
ユーザーは使用中に属性変更の方法なども知らない方々なので、
大丈夫だと判断しました。
ただ、Sub Cstm_Save()を使っていると、
単純な『ActiveWorkbook.Save』って使えなくなるのですね。。。
他のマクロで上書保存したいときは、
Callを使ってSub Cstm_Save()を呼び出すようにしました。
また、一つ勉強になりました。

この度は長々と付き合って頂き、大変ありがとうございました。m(_ _)m

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