同時操作不具合解消マクロ

質問

コールセンター勤務のExcelマクロ初心者です。

時間帯ごとの迷惑電話リストデータを作成するにあたり、オペレーター約100名から数ヶ月間情報を徴収しようと思っています。
受信業務の妨げにならないよう、簡単に操作できるExcelでのマクロを作成し、そのExcelを100名に配信する予定です。
オペレーターがマクロボタンを操作したら集計用Excelが開いてデータが集まる、というイメージです。（以降便宜上オペレーターが操作するExcelを子Excel、集計用Excelを親Excelと表記）
迷惑電話業者リストは既に手元にあり、「何時何分に電話が掛かってきているのか」のデータが欲しいのです。

【親Excelイメージ】
a列・・・業者名
b列・・・業者電話番号
c列・・・掛かってきた時刻
1ヶ月あたりの累積で約500件（500行）ほどのデータを収集見込みです。

【子Excelイメージ】
a列・・・業者名
b列・・・業者電話番号
c列・・・時刻（関数now）
そしてd列にボタンを配置させ、行数（業者数）は10行程度になります。迷惑電話が掛かってきたら、該当行のボタンを押してもらう形です。

ここで質問です。
子Excelのセルd1にあるボタンをクリックすると、セルa1～c1をコピーし、親Excelを開いて最終行を探し、値のみを貼り付けて保存して親Excelを閉じる、というところまでのマクロは作れます。困っているのが、複数のオペレーターが同時刻に操作してしまった場合のバッティングです。

例えばオペレーターAが親Excelを開いていた場合に、オペレーターBは親Excelが閉じられるのを待ってから貼り付けにいく、という感じにしたいのです。
若しくは、親Excelをブックの共有としたときに、全く同時刻にオペレーターAとオペレーターBが最終行を探しにいってもエラーにならないようにしたいのです。

どちらの方法でも構いませんので、どなたかご教授頂けると幸いです。オペレーターは100名ほどおりますので、2名同時操作どころか3名同時操作もあり得ます。これ以外にも懸念される事項があれば教えてください。

回答No.2

nda23 · ベストアンサー

親Excelというより、データベースですですが、ExcelやAccessですと
開く→閉じる だけでも処理が重いので、CSVファイルにしたら如何ですか？
テキストファイルだから速いし、Excelでも開けますからね。

'1ミリ秒間スリープするAPI
Declare Sub Sleep Lib "kernel32" (ByVal ミリ秒 As Long)
'CSVファイルのパス ★実態に合わせて変えて下さいね
Const パス As String = "\\共通サーバ\集計\蓄積.csv"

'ボタンクリック時のイベント処理
Sub 記録(ByVal 行 As Long)
Dim A列 As String
Dim B列 As String
Dim C列 As String
'予め編集する
A列 = Cells(行, 1)
B列 = Cells(行, 2)
C列 = Format(Now, "yyyy/mm/dd hh:nn:ss")
'エラーハンドリングを開始する
On Error Resume Next
'ファイルが開けるまで繰り返す
Do
　　'ファイルを開く(他の書き込みは許さず、参照は許可する)
　　Open パス For Append Lock Write As #1
　　'成功したらループを抜ける
　　If Err.Number = vbNormal Then Exit Do
　　'エラーをリセットする
　　Err.Clear
　　'1ミリ秒待つ
　　Sleep 1
Loop
'エラーハンドリングを終了する
On Error GoTo 0
'ファイルに出力する
Print #1, A列; vbTab; B列; vbTab; C列
'ファイルを閉じる
Close #1
End Sub

仕組みはファイルを開いて書き込むだけですが、万一、書き込みが
同時に起きると、僅かでも遅い方が負けてファイルを開けません。
そこで、エラーで停止しないようハンドリングし、開けるまで
繰り返しリトライします。この間、CPUストレスを軽減するため、
Sleepを入れます。
ファイルを開いてから、書き込み→閉じるまで、数ミリ秒ですので、
書き込みがカブっても人間が感じる程のフリーズ状態にはなりません。

補足

素晴らしいご回答ありがとうございます。しかし、大変申し訳ないのですが当方は貴殿の想像以上にど素人です(汗)。
ByVal 行 as long のところで、具体例に「行」の部分は何を入れたら良いのでしょうか？1行目の場合は1、2行目の場合は2、と思いましたが単に数字を入れても駄目なようです。
ByVal ミリ秒 as long のところも「ミリ秒」のところには何かに置き換えるのでしょうか？
お手数お掛けして申し訳ございません。

回答No.3

nda23

ボタンを置いてクリックしたら動くマクロは作れるんでしょ？
D1のボタンなら1行目、D2のボタンなら2行目でしょ？
例えば下のようなイメージかな？

Private Sub CommandButton1_Click()
　　記録 1　'1行目のボタンクリック
End Sub
Private Sub CommandButton2_Click()
　　記録 2　'2行目のボタンクリック
End Sub

>Declare Sub Sleep Lib "kernel32" (ByVal ミリ秒 As Long)
これはWindowsのAPIの定義です。APIを使うという宣言
だけで、このまま記述しておくだけです。
これは(General)、つまり、モジュールの最初の部分でのみ
記述できます。
サンプルの中で、Sleep 1 という部分で実行しています。

因みに、イミディエイトウィンドウ(分からない時はCtrl+G)で
Sleep 1000と入力してEnterキーを押すと、1秒間、フリーズ
します。

尚、Declareの宣言は標準モジュールに記述してください。
ボタンクリック時のマクロはシートやブック等のオブジェクトの
中のマクロなので、ここに記述する時はPrivateが必要です。
しかし、Privateだと他のオブジェクトに属する処理の中では
使用できません。同じく、「記録」というプロシージャも
標準モジュールに置いてください。

「記録」にはPrivateを記述していないので、自動的にGlobalに
なり、他の手続きからも参照できます。オブジェクトに置くと、
Sheet1.記録 のようにオブジェクト修飾が必要になります。
まぁ、この辺の事情は分からなくてもイイですけど、ボタンの
イベント以外は「標準モジュールに書く」だけ覚えて下さい。

あと、CSVファイルを参照する時は直接開かないで、コピーを
とり、これをExcelで開きます。直接開くと書き込みロックが
かかるので、オペレータさんの方のExcelがフリーズします。

お礼

出来ました！ありがとうございました！

回答No.1

hitomura

……うーん、この規模と要求内容をみるに、処理をすべて Excel で行うのは無理があります。
データベースに現在親 Excel が保持しているデータを格納するテーブルを作り、子 Excel ではそのデータベースに接続して追加するデータを送信する、というように処理を変えた方がいいのではと思います。