[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『見積書のテンプレートファイルを開くごとに見積番号をカウントアップしたい。』（Sammy）
初めてのお願いですがよろしくお願いします。
テンプレートを開くと初期設定番号（１０桁程度）がカウントアップする。
編集後別名保存する。保存後そのファイルを開いてもカウントアップはしない。
さらに希望は、別名保存した場合はマクロを削除して都度マクロ警告が出ないようにしたい。
元のテンプレートはカウントアップ後の番号を記録し、次に開いた時には次の番号を表示する。
間違って開いて閉じてカウントアップしてしまってもとび番号として扱います。
Auto_Open というものを使うと聞いたのですが使い方がさっぱりわかりません。
マクロ記録程度しかやったことがない初心者です。
＜ 使用 Excel：Excel2007、使用 OS：Windows7 ＞
やりたいことより、目的を伝えてもらった方がもっとよい案があるかもしれません。 見積書ということですが、保存時に通し番号を振るやり方が一般的かと思います。
自社で運用している方法は、「入力フォーマット」「見積一覧」「保存用ブック」の３構成にして、 入力フォーマットでマクロを実行すると、見積一覧の行数を読んで最終Noをフォーマットにつけ、 xlsx形式でブック毎決められたフォルダに保存させます。
一案ですが、いかがでしょう？ （稲葉） 2014/02/03(月) 08:46
ただカウントアップするだけでなく、誰が何のために採番したのか判るよう、Excelの
シートを決めるだけで良いのではないでしょうか。それをサーバに置いて、同時には
開けないようにする（デフォルト）だけです。最新番号+1なんて、セルのドラッグでも
できますよね？
同時に開けない事が問題になるならば、担当者別に採番ルール（先頭の文字を変える、等）
を決めて、ブックを担当者分作成することです。無理にマクロを使ってぐだぐだになる位なら、
単純なブックを、運用ルールでカバーするほうが確実です。
（???） 2014/02/03(月) 09:44
追伸：見積番号は別名保存するファイル名の中に表示してファイル名で探すときに判断
する材料にしています。
（???）さんへ
上記のような感じですが、マクロによる方法しかないようですのでこのような質問を
しているんです。なにか良案があれば教えてください。
（Sammy） 2014/02/03(月) 12:34
???さんの意見が最もだと思います。
もしマクロにしたとしても、保存した後のフォーマットをコピーして使われたら どうしますか？という問題が出てきます。 ルールが守れないからマクロ、ならやめたほうがいいと思いますよ。 ルールがあって、簡素化したいからマクロ、なら理にかなっていると思います。
それでも開いたときにカウントアップさせたいなら、
同じフォルダにCOUNT.txtを作り、中身を「10」にして、拡張子を.datに変更する
新しいブックを作り、以下のコードをThisworkbookモジュールに貼り付ける
但し、マクロを無効にしたまま開かれたら意味がありません。
Option Explicit
Private Sub Workbook_Open()
Dim OPEN_FN As String
Dim SAVE_FN As String
Dim objFSO As Object
Dim objTS As Object
Dim COUNTER As Long
'
'//////////////////////////////////////////////////
'//ファイルの作成か、管理用か
'//////////////////////////////////////////////////
If MsgBox("見積書を作成しますか？", vbYesNo + vbQuestion) = vbNo Then
If InputBox("管理パスワードを入力してください", "パスワードの入力") <> "1234" Then
MsgBox "パスワードが違います。": GoTo err
Else
Exit Sub
End If
End If
Set objFSO = CreateObject("Scripting.FileSystemObject")
'
'//////////////////////////////////////////////////
'//先に保存先を決めて、キャンセルならブックを閉じる
'//////////////////////////////////////////////////
SAVE_FN = Application.GetSaveAsFilename( _
Title:="見積書の保存先", _
InitialFileName:=ThisWorkbook.Path & "\見積書.xlsx", _
FileFilter:="Excelファイル,*.xlsx" _
)
If SAVE_FN = "False" Then
MsgBox "見積書の作成がキャンセルされました": GoTo err
End If
If objFSO.FileExists(SAVE_FN) Then
MsgBox "ファイル名が重複しています。別名で保存してください": GoTo err
End If
'
'//////////////////////////////////////////////////
'//カウンタをdatファイルから読み込み、存在しなければ終了する
'//////////////////////////////////////////////////
OPEN_FN = ThisWorkbook.Path & "\" & "COUNT.dat"
If Not objFSO.FileExists(OPEN_FN) Then
MsgBox ("COUNT.datが存在しません")
End If
'
'//////////////////////////////////////////////////
'//datファイルから通し番号を取得し、カウンタを+10する
'//////////////////////////////////////////////////
With objFSO.GetFile(OPEN_FN)
With .OpenAsTextStream(1)
COUNTER = .ReadAll
.Close
End With
With .OpenAsTextStream(2)
.Write COUNTER + 10
.Close
End With
End With
Set objFSO = Nothing
'
'//////////////////////////////////////////////////
'//指定の場所にカウンターの値を入れる
'//////////////////////////////////////////////////
Sheets("Sheet1").Range("A1") = COUNTER
'
'//////////////////////////////////////////////////
'//マクロ無しブックで保存する
'//////////////////////////////////////////////////
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=SAVE_FN, FileFormat:=xlOpenXMLWorkbook
Application.DisplayAlerts = True
Exit Sub
err:
ThisWorkbook.Close savechanges:=False
Exit Sub
End Sub
（稲葉） 2014/02/03(月) 12:56
Sub AUTO_OPEN()
Dim cw As String
If Sheets(1).Range("A1") = "" Then
Sheets(1).Range("A1") = "TEST0000JP"
Else
cw = Sheets(1).Range("A1")
Mid(cw, 5, 4) = Right(Mid(cw, 5, 4) + 10001, 4)
Sheets(1).Range("A1") = cw
End If
End Sub
コピーの作成については、雛型（テンプレート）となるマクロ無しブックを用意しておき、
雛型をコピー。コピーしたブックを開き、特定セルに新しい見積書番号を埋めて保存するとか。
（マクロ付きのまま丸ごとコピーし、後からマクロを削除するマクロを書く、という手もあり）
（???） 2014/02/03(月) 13:09
追伸です：
＞但し、マクロを無効にしたまま開かれたら意味がありません。
の部分で今のエクセルは開いてから「有効にする」ボタンをクリックするように
なっていますが問題ありますか？
下記、一番最初から書かれている内容です。
Sub ThisWorkbook()
'
' Thisworkbook Macro
'
Option Explicit
Private Sub Workbook_Open()
Dim OPEN_FN As String
Dim SAVE_FN As String
Dim objFSO As Object
Dim objTS As Object
Dim COUNTER As Long
'
'//////////////////////////////////////////////////
'//ファイルの作成か、管理用か
'//////////////////////////////////////// 以下同じと思います。
（Sammy） 2014/02/03(月) 14:27
初心者だから全て聞けばいいというわけではありません。
プロシージャとモジュールを区別してください。 貼り付けるのはThisworkbook「モジュール」です。
ヒントはこれだけです。 これで調べても分からないようなら、マクロによる管理は諦めた方が賢明です。 （稲葉） 2014/02/03(月) 15:09
稲葉さん 常日頃の稲葉さんからすると、今回はかなり厳しいですね。 虫の居所が悪いですか？
個人的には、質問者の当初の構想でそんなに問題ないと思っているのですけど。。 そんなに、番号一覧を作らないと具合が悪いですか？
Sammyさん まだ、お金掛けないでやろうとしているなら、私が続けてもいいですよー
（半平太） 2014/02/03(月) 17:22
半平太さんご親切に有難うございます。
今回はお金をかけずに完成できました。やればできると自身もできました。
次回何かのご縁がありましたら宜しくお願いします。
(???)さん、途中で親切にしていただきありがとうございます。AUTO_OPENの勉強もやってみたかったです
が、今回のことで頭がいっぱいになってしまいこれ以上入りません。またの機会にお願いしたいと思います。
皆さん、本当にありがとうございました。
（Sammy） 2014/02/03(月) 18:00
＞稲葉さん あれからじっくり考えてみてやっと動くようになりました。
それはよかったですね。
そうなると、稲葉さんの対応が正しかったと云うことですね。 大変失礼いたしました。 m(__)m
（半平太） 2014/02/03(月) 18:15
Sammyさん ＞あれからじっくり考えてみて 考えて欲しかったので大変よかったです。 が、半平太さんのコードのほうが絶対よいものが出来るので、ダマって半平太さんに作ってもらっても よかったと思います！ ご希望があればまた対応致します。
＞ 半平太さん ご指摘の通り、先日から咳が酷くて、丁寧に最後まで教えてあげられなかったのは私の失態です。 申し訳ございません。
番号一覧については、ファイルの保存場所をリンクで管理していく場合に必要かな？と思った程度で 運用に支障がなければ気にしていません。
参考までにお聞きしたいのですが、半平太さんが作るとしたらどのような形になりますか？ 後学の為にさわり程度教えていただけたら光栄です。
（稲葉） 2014/02/03(月) 18:32
あのー、稲葉さんのスキルの方が数段すぐれているので、 「後学」になるようなものは持ち合わせておりません。 m(__)m
構想にしても、お説の番号一覧の方が優れているのは、間違いないです。
・・・けど、私はニーズの大きさに合わせて対策を考える傾向にあります。
私が考えるフロー
テンプレートが開かれたら、見積書を作成するか尋ねる。 ↓ (Yesの場合) 保存先とブック名を決定させる。 ↓ 見積番号をカウントアップ後、一旦保存して次回の採番に備える。 ↓ 直後に、指定ブック名で「名前を変えて保存」も実行する。 その際、以下の項目を自動的に実施する （１）ファイルフォーマットをxlsx用にする (２）当該ブックに「見積番号変更」か「ブック名変更」があったら、 見積番号のセルが赤くなるように条件付き書式を設定する。
つまり、固有の「ブック名・見積番号」である事を条件とした条件付き書式を仕込む。
条件式はこんな感じです。
↓
=OR(見積番号のセルの値<>固有見積番号,LEFT(REPLACE(CELL("filename"),1,FIND("[",CELL("filename")),""),FIND("]",REPLACE(CELL("filename"),1,FIND("[",CELL("filename")),""))-1)<>固有ブック名.xlsx)
以上の手当で、 ・一人がテンプレートを開いている時間は、そんなに長くないので、複数人の同時的(？）な使用にも実用上耐えられる。 ・既存「xlsxファイル」を使いまわそうとすれば、ブック名か見積番号は変更することになるのが一般的と考えられますので、 その時点で見積番号のセルが赤く変わり、ユーザーにルール違反であることを認識させられる。
（半平太） 2014/02/03(月) 20:35
いやいやご謙遜されると、私が馬鹿みたいですが・・・ ありがたくお言葉頂戴いたします。
条件付書式いいですね。 テンプレートは空白以外、保存後は固有見積番号以外を設定すればユーザーに意識させられますもんね！
時間見つけてSammyさんから要望があれば付け加えたいと思います。
勉強になりました。ありがとうございます。 （稲葉） 2014/02/03(月) 21:07
そこで稲葉さんにお願いがあります。半平太さんの記述を見ていましたら
＞見積番号をカウントアップ後、一旦保存して次回の採番に備える。
↓ ＞直後に、指定ブック名で「名前を変えて保存」も実行する。 とありますが、このようにできると番号が発行されてから名前を付けて保存できるんですよね。 昨日も数枚見積書を作りましたが、保存するときにファイル名に見積番号が盛り込めず、一旦ファイルを 閉じてからファイルを探しに行き番号をファイル名の中に追加しなければいけません。２度手間になって います。出来れば教えていただきたく思います。 条件付き書式はルール違反防止になりますが、同僚から批難の声が上がりそうでとりあえずなしというこ とでお願いします。同僚の中で使うことが多いためあまり縛りが多いと逆に困ってしまいます。
宜しくお願いします。
（Sammy） 2014/02/04(火) 08:31
＞同僚の中で使うことが多いためあまり縛りが多いと逆に困ってしまいます。
今回の相談の趣旨から外れる様に感じますねぇ。
ダブり採番に悩んでいる人は、Sammyさんだけなんですか？
ダブり採番の弊害とその対策について、同僚が何を考えているのか知りたいのですけど。
例えば「縛られるくらいなら、ダブりなんて滅多にないし、 その後の対応にもそんなに困っていないから今まで通りの方がいい」 とか
（半平太） 2014/02/04(火) 11:20
（Sammy） 2014/02/04(火) 11:48
相手がやくざ(のような人たち）では、Sammyさんもお手上げですね ＾＾
＞保存するときにファイル名に見積番号が盛り込めず
どんなルールで盛り込みたいのですか？ 見積書番号とファイル名の関係をご説明ください。 (具体例も２，３つ挙げてください)
（半平太） 2014/02/04(火) 13:47
下記ファイルの例です。
例：○○会社 営業所名 電気部品01 123456789
という感じのファイルに統一されています。
123456789 の部分が見積番号です。
このパターンしかありません。
時々有力な見積書には各々ファイルの前に記号を付けて一覧の一番最初の方に表示するようにする
人もいます。（私が余計なことを教えてしまいました。）
こういう人のは無視していただいて結構です。
宜しくお願いします。
（Sammy） 2014/02/04(火) 14:56
稲葉さんの構想で対応可能です。 ___↓____ ＞InitialFileName:=ThisWorkbook.Path & "\見積書.xlsx",
やくざさんにファイル名を入力させるダイアログを出す直前に、 DATファイルから見積番号の採番を行い、上記「見積書」の所にそれを反映させればいいです。 _________ ＞例：○○会社 営業所名 電気部品01 123456789
もし、その場になってから、ユーザーがファイルの作成を中止した場合、 採番が空振りになり、「123456789」は永久に出番がなくなりますが、 当初のご説明では、見積番号は飛び飛びで構わないとあったので、 これは問題にならないと考えます。
ちょっと心配なのは、「○○会社 営業所名 電気部品01 」の部分です。 まだ、何もデータが入力されていない時点なので、プログラムで補う訳には行かず、 ユーザーが直接、手入力で補充する必要があります。
果たしてここは大丈夫なのかどうか？
この懸念が解消されるなら、私の出番はもうないので、そろそろ稲葉さんにバトンタッチいたします。
（半平太） 2014/02/04(火) 15:34
すみません、寝込んでて朝からみてませんでした。
「○○会社 営業所名 電気部品01 」この部分がある程度決まっているなら、ユーザーフォームのコンボボックス等で 会社名、営業署名、製品概要？みたいな形で選択させる方法もあります。 その場合、保存先を決めるだけなのでフォルダ選択ダイヤログに変更します。 通し番号については半平太さんがおっしゃっていた通りです。
○○会社 営業所名 電気部品01この部分だけSammyさんが仕様を決めていただければ対応いたします。 が、もう少し体調もどってからにさせてください。
お二人がよければ、半平太さんの構成で1から作っていただければありがたいです。
人任せですみません。 （稲葉） 2014/02/04(火) 16:07
稲葉さん 体調がすぐれず大変なところこんなお願いをしまして申し訳ありません。
回復されましたらまたお願いします。お待ちしています。お大事にしてください。
「○○会社 営業所名 電気部品01」の部分は営業所がなかったり、メモ的要素を
入力したりと仕様が決まっていませんので、見積番号がファイル内に表示されて
いれば大丈夫です。
半平太さん 稲葉さんがお二人さえよければとおっしゃってますが、よろしいですか？
半平太さんのプログラムも拝見したくなりました。
ほんの僅かですが、プログラムの言語がどのようなことを行っているのか、どのような
手順で動作しているのか、文字を解読？していくと面白いと思えるようになりました。
今までは拒絶反応でダメって遠ざけていたことがウソのようです。勉強の意味でも
違った内容を見たいと思います。是非教えてください。
（Sammy） 2014/02/05(水) 08:42
＞見積番号がファイル内に表示されていれば大丈夫です。 とのことですので、簡単に。
以下の３行を消してください。
If objFSO.FileExists(SAVE_FN) Then
MsgBox "ファイル名が重複しています。別名で保存してください": GoTo err
End If
次に、「指定の場所にカウンターの値を入れる」の下に
'//////////////////////////////////////////////////
'//指定の場所にカウンターの値を入れる
'//////////////////////////////////////////////////
Sheets("Sheet1").Range("A1") = COUNTER
'
以下のコードを追加してください。
'
'//////////////////////////////////////////////////
'//ファイル名に通し番号を入れる
'//////////////////////////////////////////////////
With CreateObject("VBScript.RegExp")
.Pattern = "(.*)(\.xlsx$)"
.Global = True
With .Execute(SAVE_FN)(0)
SAVE_FN = .submatches(0) & COUNTER & .submatches(1)
End With
End With
'
これでどんな名前でも通し番号が入ります。（たぶん。）
（稲葉） 2014/02/05(水) 09:42
（Sammy） 2014/02/05(水) 12:33
今回は本当に皆さんにお世話になって感激でどのようにお礼を言っていいのかわかりません。
感謝感激です。
またの機会がありましたら是非皆さんにお願いしたいと思います。
ありがとうございました。
途中挫けそうになりましたが頑張ってよかったです。勉強にもなりました。
稲葉さん、早く良くなってくださいね。どこに住んでいらっしゃる方かわかりませんが、
早く良くなることを祈っています。Sammyより
（Sammy） 2014/02/05(水) 16:58
(´・ω・`) （稲葉） 2014/02/05(水) 17:06
＞半平太さん バッチリです。完成です。
あのー、それ、私のレスじゃないんですけど。
すべて、稲葉さんです。
スーパーマン並みの回復力ですね ＾＾
（半平太） 2014/02/05(水) 17:08
回復はしてないのですが、会社休んだだけ仕事が溜まるので・・・
とった水分が全て鼻水に変換されます。
Const POCARI As OOTSUKA = 500
Const DAKARA As SUNTORY = 500
Const OIOCHA As ITOEN = 500
Sub HAMAMIZU
Dim WATER As H2O
Dim INABA As Old Man
Dim HANAMIZU As
WATER = Array(POCARI * 2 , DAKARA * 2 , OIOCHA * 1)
INABA = HAKUSHON(WATER)
End Sub
Function HAKUSHON(ParamArray MIZU()) As HANAMIZU
Dim M As Variant
For Each M In MIZU
HAKUSHON = HAKUSHON + M * 0.90
Next M
End Function
（稲葉） 2014/02/05(水) 17:25
（Sammy） 2014/02/06(木) 06:59
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.