[[20140210081306]]　『事務所内のＬＡＮ上でサーバーに保管した在庫管理』（Sammy）　ページの最後に飛ぶ

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

　

『事務所内のＬＡＮ上でサーバーに保管した在庫管理票と価格表のリンクをリアルタイムにしたい』（Sammy）

現在ＬＡＮ上には30台程度のPCがつながっていますが、
上記に特化したＰＣは各ブロック１台に限定し８台でやってます。
ファイルサーバーに在庫管理票ファイルと価格表ファイルを保管してあります。
日々入庫した製品と販売した製品の入力をＡ端末より在庫管理票にリアルタイムに
入力しています。入力したらすぐに上書き保存します。価格表の方もＡ端末にて開
いていますのですぐにこれも同時に上書き保存しています。在庫管理票の在庫数は
価格表内の在庫を表示するセルにリンクしてあります。Ｂ〜Ｇ端末では価格表のみ
開いて在庫管理票の在庫数をリンクさせたセルにて確認しています。
しかしＢ〜Ｇの端末ではＡ端末での更新した在庫数が既に開いているため再度開き
なおさないと個数が更新されません。
エクセル内ここまで出来ましたが、自動更新するように出来ればリア
ルタイムに個数を判断できるので間違いや不安がなくなります。
何か良い方法はあるのでしょうか？
更新ボタンという感じでも大変助かります。

＜ 使用 Excel：Excel2007、使用 OS：Windows7 ＞

データはExcelブックに置かず、DBサーバ上に置くべき内容です。ご検討を。

自動更新しても、結局見てから登録までの一瞬で他人が登録する可能性があるので、表示は参考にしかなりません。
DBでいうと、在庫確認から登録までを１つのトランザクションにまとめれば解決するでしょう。
（???） 2014/02/10(月) 09:54

現状の確認方法を変えたくはありません。お金もかかることですので経費御節減の折許可もなかなか
通りません。現状を少しでも良くする方法を考えているのです。また、入力は管理をしている担当者
一人で、見る人は多人数という感じです。在庫が現在何個あるか、その有る、無しにより仕事の内容
が変わります。確認したものを確保する、確保したらお客様に注文をいただく。確保できなければ手
配品の担当者に回すというように、その瞬間の在庫が確認できれば現状ではOKです。確保分は在庫か
ら差し引きます。その数量がすぐに反映される。こういうことを希望しているんですが、できないで
しょうか？

（Sammy） 2014/02/10(月) 12:44

ボタンで更新するならば、以下とか？ 
 Sub test()
    ActiveWorkbook.OpenLinks Name:="Book1.xlsm"
    ActiveWorkbook.Close
End Sub

なお、DBサーバは、フリーのものが多数存在します。規模的に無料で十分。Microsoftでも、
SQL Server Expressという無償版を公開してます。

無償といっても使用期限があるわけではなく、DBサイズ上限(10G)やメモリ量制限、CPU数制限が
ある事と、管理ツールが殆どない程度であり、Excelデータ管理の代わりくらい楽勝ですよ。

設備投資0、Excel VBAの改良のみで業務効率アップなのだから、有用だと思うのですけどねぇ。
（???） 2014/02/10(月) 14:59

（???）さん 有難うございます。マクロで実行できました。更新が簡単になりました。
このマクロの実行ボタンを表示しているシートの何処か右上の方に表示したいと思うのですが、
ＶＢＡもマクロも全く初心者でさっぱりわかりません。
どうすればボタンを貼り付けれるか教えてください。
ボタンの名前は「リンク更新」でお願いします。
無償DBサーバは難しそうなので勉強してから挑戦してみます。情報ありがとうございます。

（Sammy） 2014/02/10(月) 16:49

・まず、リボンのユーザー設定画面で、メインタブの「開発」を有効にします。
・「開発」−「挿入」−「ActiveXコントロール」のコマンドボタンをクリック。
　シートをクリック→ドラッグして、ボタンを貼ります。
・ボタンを右クリックし「プロパティ」で、Captionを変更。
・ボタンをダブルクリックするとPrivate Sub CommandButton1_Click()ができるので、
　ここに先のマクロ2行をコピペします。
・編集画面を閉じてから、「開発」−「デザインモード」をクリックし、無選択状態に。
・一旦ブックを保存してから、ボタンを押してみましょう。
（???） 2014/02/10(月) 17:27
（???）さん 有難うございました。
ボタンで価格表ファイルが再立ち上げが出来て在庫数が更新できました。
短いコードでこれだけのことができるなんて感激です。
質問ですが、ボタンを押した後「このファイルは既に開いています。
再度開くと現在編集中のデータが破棄されます」と出ますが、これを
出なくする方法はあるのでしょうか？
個人個人でエクセルの設定があると思いますので、このファイルに
限ってメッセージが出なくする方法があれば教えてください。
あちこち探しましたが、わかりませんでした。
何度も申し訳ありませんが宜しくお願いします。

（Sammy） 2014/02/12(水) 08:23

既に対象となるブックを開いていると、2重に開くことになります。
しかし、既に開いているならば、リンクは自動更新されているから、マクロを動かさなくて良いのでは？

念のため、開いていたら無視する例。 

 Sub test()
    Dim i As Long
    Dim iFlag As Long


    For i = 1 To Workbooks.Count
        If Workbooks(i).Name = "Book1.xlsm" Then
            iFlag = 1
            Exit For
        End If
    Next i


    If iFlag = 0 Then
        ActiveWorkbook.OpenLinks Name:="Book1.xlsm"
        ActiveWorkbook.Close
    End If
End Sub
（???） 2014/02/12(水) 09:28
（???） さん 有難うございました。
ご案内していただきましたプログラムを盛り込む前に問題が起きました。
ボタンを押して２重に開くことを了解すると在庫数は更新するのですが、
エクセル自体が次のようになってしまい終了してしまいます。

更新ボタンを押す → （個数更新後・エクセルが立ち上がって全体が霧がかかった
ような画面になってから）エクセルは動作を停止しましたのメッセージ → エクセ
ルにより情報を回復しています →プログラム終了 というボタンがでる
この動作が毎回ではなく、時々（3回に1回ぐらい）でます。
そのたびに終了してしまいますので具合悪いです。

ボタンを押すとこのような流れが発生しますがWin7自体が異常でしょうか？
エクセルが異常でしょうか？
ファイルは社内LAN上にあるため、Book1のところはPassを入れてあります。
また、価格表は20000行を超えるためか開くのに時間がかかっています。
（時間は5秒ぐらいで下のバーが左から右にスライドします）

また、昨日よりエクセルを2010に変更しましたが問題ありますか？

理由と解決策があるようでしたら教えていただければ幸いです。
やっかいな質問で申し訳ありません。

（Sammy） 2014/02/12(水) 15:22

リンク先もマクロ入りブックも、どちらも自PC上に置くとどうなりますか？

自PC上ならうまくいく場合、オプション関係のセキュリティ設定を見直しましょう。
リンク先がネットワーク上のサーバにあるブックならば、「セキュリティセンターの設定」で、
「信頼済みドキュメント」で信頼を許可してあるかどうか、等。

うまく動いていた2007の設定と2010の設定を見比べてみて、確認してみてください。
（???） 2014/02/12(水) 16:00

自PCにホルダを作りその中にリンクしているファイルを２つ入れてリンクを設定しなおして
動作確認しました。同じ現象が起きます。
また、エクセルのオプションより、「セキュリティセンターの設定」よりマクロとかＶＢＡ
とかの設定画面のところはすべて制限なしの設定にしましたが同じ現象です。
2007はもう消え去ってしまい確認出来ません。

PCスペックは WIN7 pro 32ビット CPU インテル2.4GHZメモリは4.0G です。
仕様が悪いのでしょうか？

今回のことで昔のwin98の時代に強制終了させられたことを思い出しました。
あれは不正アクセス違反でしたか？忘れてしまいましたが。

原因がわからないようであれば諦めます。
こんなにお世話になってこのようなことをこちらから申しましては失礼とは思いますが、
これ以上ご迷惑もかけたくありませんし・・・。

（Sammy） 2014/02/12(水) 17:26

2007ではできていたのですから、PCに問題はありませんよ。
自PC上でも動かないならば、マクロ関係なく、手作業でリンクを貼り直してください。
まずは、元と先の２つを同時に開いた状態で、元を変更すると自動的に先も変わること。

全データではなく、一部だけ別ブックにコピーして試してみましょう。
それでデータ量のせいなのかどうかを判断できます。
（???） 2014/02/12(水) 18:00

昨日やってみました。色々なパターンを試してみました。20000〜500行までのデータで
ファイルサイズ2.6〜0.3MBまで試しました。やはり同じ現象でした。リンクは20000行の
手作業のリンク貼りは大変なので小さいサイズで試してみました。今度はマクロのエラー
が出てしまいまたその確認で時間がかかり、結局何も解決できないままずるずる・・・。
夜遅くなり明日の仕事に影響が出るのでやめてしまいました。薮の中に入った感じです。
やはり俺には無理だ・・・と諦めそうですが、また仕事が暇なときに考えることに
します。仕事が溜まってしまいましたので一旦ここで作業を止めないと大変なことになって
しまいますので・・・。ありがとうございました。また後日記入します。

（Sammy） 2014/02/13(木) 08:24

時間が出来ましたので試してみました。
前に作った動作するファイルをバックアップしていましたのでそのファイルで
マクロは走りました。そこで元データはそのままで価格表の方を20行程度にして
再度リンクを貼りなおしてリンクはすべて正常な状態で走らせました。
現象は全く同じでやっぱり駄目でした。数行のデータでエラーばかり出ます。
エラーは毎回と言っていいくらいです。
試しに価格表を開いた状態で再度手動で価格表のファイルをダブルクリックで
開いてみました。そうしたら２回開くと破棄されますの注意の後に
「このブックには他のデータソースへのリンクが含まれています。」と注意が出てきました。
「更新する」を押してみるとエラーは３０回試して１回も起きませんでした。
この更新するというボタンを押すプログラムを入れたらエラーが起こらないのでは
と思ったところです。
思いつきのことで試させてくださいというのも申し訳ないのですが、
ボタンを押した後の中に組み入れるプログラムを教えてください。
もしかしたら薮の中から抜け出せるかもしれないと少し光が見えた気分です。
宜しくお願いします。
追伸：そういえば初めてファイルを開くときには「更新する」ボタンが出てきますので
押していました。しかし作っていただきました「ぼたん」を押した後はそれが出てきません。
ひょっとして更新する、しないのボタンが出ないようにエクセル内で設定できるのでしょうか？
いちいち尋ねられてうっとうしい気がしてたのですが・・・

（Sammy） 2014/02/13(木) 14:23

更新する、しないは、元と置き場所が変わらなければ表示されないはずですが…。
念のため、リンクのブック名変更ではなく、以下のように新たなリンクにしてみてください。

・実験のために元ブックと先ブック（マクロ入り）を自PCにコピー。
・元ブックのコピーは、好きな量にデータ数を減らす。ブックを新規作成後に範囲コピーでもOK。
・先ブックのコピーは、一旦リンクを全てDELETE。（ブックを新規作成でもOK）
・コピー２つを、1つのExcelで開く。
・先ブックでは、リンクしたいセルで「=」を入力後、元ブックのセルをクリック。
・リンクしたいセル参照の数式に変わったところでENTER。
・データ量があると思うので、作成した1セルのリンクを編集し、$を外す。
（='[Book1.xlsm]Sheet1'!$A$1 等のセル指定部分を、!A1 のようにする）
・リンクセルをドラッグし、必要なだけ範囲コピー。
・コピーしたセルのリンク先が、正しく連番のセルになっている事を確認。
・表示中の元ブックコピーの値を幾つか変更。先ブックに反映される事を確認。
・元ブックコピーを保存し、閉じる。
（???） 2014/02/13(木) 15:57

上記実験成功です。
同じPC内でも、LAN内のあちこちに在庫管理票を置いた場合でも、両方ともLAN内の
あちこちに置いた場合でも動作は正常にできます。
今までで違うところはSheet1'!$A$1 等のセル指定部分を、!A1 のようにする
の部分です。$$が付いているとおこるのでしょうか？
価格表の中はリンクを張った先はすべて$$がついています。
これはリンクを張る時にすべて手作業で１つ１つ指定していきますので気に
していませんでした。このために変な先を見に行ってたのでしょうか？
このために「更新する、しない」の質問が出ていたのでしょうか？
実験では全く出ませんでした。
今度はこの$$を実験用のコピーファイルですべて外して実験してみます。
結果はまた報告します。

何か注意点があれば教えてください。
実験はたぶん１４：００頃からになります。

（Sammy） 2014/02/14(金) 08:07

上記の実験の続きです。
元ファイルのコピーで実験。ファイル内の$$をはずしてボタンを押しました。
最初３回位うまくいきましたが、それ以後は今までと同じです。
データ量が多いとダメなんでしょうか？
何故うまくいったりいかなかったりなんでしょうか？
ファイルを開きなおすときにしばらく考えている様子。
データが大きいため自PC内にあっても時間が５行程度かかりますが
LAN上ですと７秒ぐらいですか・・・いずれにしても状況は同じです。
前述の「更新するというボタンを押すプログラム」はダメでしょうか？
これを可能にすると他に操作に影響が出るのであれば問題ですが・・・

（Sammy） 2014/02/14(金) 12:37

3万件くらいで試してみましたが、特に問題は出ませんね。
再現しないので、正しい対処か判りませんが、以下のように1行追加してみてください。 

    ActiveWorkbook.OpenLinks Name:="Book1.xlsm"
    DoEvents
    ActiveWorkbook.Close
（???） 2014/02/14(金) 14:04
1点確認です。
マクロ実行しているブック（リンクする数式のあるブック）上で、以下のマクロを
実行すると、1 になるでしょうか？ （リンク元のブックが1つなのかの確認です）

MsgBox UBound(ActiveWorkbook.LinkSources(xlExcelLinks))
（???） 2014/02/14(金) 14:18

    DoEvents
を追加しても同じでした。
また次のご質問の結果は2でした。

ブックの数も関連しているのですか？
リンクとブックの状況を書いてみます。

在庫管理しているブックはシートが現在10あります。
メインの行数は700行程度
それ以外は100行程度でテキストで情報表示に使っています。

そして価格表の方はシートが16あります。ほとんどが情報のみのページです。
リンクしているところは2シート位です。
メインの価格表のシート以外は画面に収まる程度の行数しかありません。
価格表からリンクを貼ってあるファイルは・・・在庫表のファイルだけですが
もう一つ価格表ファイルの他のシートからも在庫表の上記と同じページの
違うセルにリンクを貼っています。

もっと詳しくということであればじっくり調べてみます。
どこかにポカがあるかもしれません。

お伝えしなければいけない内容でしたか…申し訳ありません。
これでわかるでしょうか？

一回再構築した方がいいかもしれませんね。いい機会かもしれません。
追加追加でどんどん情報量が増えてしまいましたので・・・

（Sammy） 2014/02/14(金) 16:52

リンク先ブックは1つのはずですが、2になっていますね。

提示したマクロは、1つの転記元を想定しています。複数ブックにリンクしているなら、
ブックの数の分だけ、同様に記述してみてください。
（???） 2014/02/17(月) 08:51

本日出先でこのページを確認しました。あいにくＰＣの操作が本日はできませんの
で明日落ち着いた環境で記述してみます。ありがとうございました。

（Sammy） 2014/02/17(月) 12:21

上記試しましたが、やはりダメでした。試しに本当に簡単な数行の、
数品目の同じようなファイルを新たに作って試しましたが同じ現象です。
MsgBox UBound(ActiveWorkbook.LinkSources(xlExcelLinks))も「1」でした。
もしかして既にエクセルが壊れている？かも・・・2013にしなければ・・・
それと参考になるかもしれませんのでご連絡します。
最初の方で「SQL Server Express」という無償ファイルをご案内いただきましたが、
ある運送会社の送り状作成ソフトがSQL Server・・・なんとかというソフトを
インストールするように書いてありましたのでこのＰＣには上記が入っています。
これが影響しているのでしょうか？
原因追求も私にはこれ以上むりで思いつくこともありません。
どうしようもないのでしょうか・・・

（Sammy） 2014/02/19(水) 09:18

Excelブックはそう簡単には壊れませんが、同じブックを多人数で開くのは、壊れる可能性を含んでいますね。
しかし、何度も更新していると駄目になる原因は、別にありそうな？

再確認してリンクブックは1とのことですが、前回リンクが2になっていたあたりに問題があるかも？
（リンク先ブックが、更に別のブックをリンクしている、とか？ 在庫量を手入力ではなく、ローカルブックの
セル参照にしてしまい、他人のExcelでは追えないとか？）

なお、WINDOWS7は、OS自身がSQL Serverを利用しているので、動作していても気にせず。
必ず入っていますが、それとは別に、全員が見られるPCにDBサーバを構築するという改善案でした。

もともと、Excelシート共用の在庫管理には、数人同時に参照と更新を行った場合に問題が出やすいのです。
在庫量はあくまで参考値であり、注文時に在庫を減らしてもOKならば確定、という処理にすべきであり、
これを簡単に実現するため、データベースをよく使用するのです。
（在庫照会時に、未確定のものを「予約」登録しておく方法も、よく使われます）

ご自身でRDBについて調査されるのも良いでしょう。
もし、社内にソフトウェア作成できる人がいるならば、その部門に相談してみてください。
誰も居ないならば、プロに開発を依頼することです。
（???） 2014/02/19(水) 11:36

（???）さん 大変有難うございました。
弊社にはプログラマーという感じは全くいなくて、せいぜいエクセルが得意という程度です。
やはりお金をかけないとできないことと諦めます。
また他の件で問い合わせするかもしれませんのでその時は宜しくお願いします。
どうも有難うございました

（Sammy） 2014/02/19(水) 12:39

 本題と関係ないところですみませんが、
 >WINDOWS7は、OS自身がSQL Serverを利用しているので
 って本当ですか？


 手持ちの 7 を確認してみましたが、Program Files や サービスにそれらしき
 ものは見当たりませんでした。
 どういう形態で入っているのでしょうか？
 （アプリケーションが Express版などを使用していて、知らないところで入って
 いるというケースはありましたが。）
（Mook） 2014/02/19(水) 15:13
デフォルトで入っているのはCEであり、Expressでは無かったですね。私の勘違いでした。

ところで、私自身使ったことがないのですが、別案があります。
Google ドキュメントという、Office似のアプリがあり、共同編集機能を使うと、
他人の変更した内容が、ほぼリアルタイムに全員更新されるのだそうです。

同時更新には人数制限があるようですが、これなら何もコーディングせずとも在庫数が
変わるので、試してみてください。Excelシートの取り込みもできるようです。
（???） 2014/02/19(水) 17:32

 更新は、１台のＰＣで他の７台は、その更新内容の参照だけでよいなら、
 ７台は、対象ブックを読取専用で開いてもよいですね！！


 全部の投稿を読んでいませんが、この場合、UpdateFromFileメソッドが使えるかもしれませんよ！！

[[20130727075952]] 

 こんな投稿をしたことがありました。


 参考にしてみてください 
（ichinose） 2014/02/20(木) 08:38
コメント返信：

[ 一覧(最新更新順) ]

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