スキルアップ・問題解決なら、モーグにおまかせ！

まだVBAの基本もあやふやな初心者です。
 
投書用のExcelファイル（投書作成テンプレート）VBA作成にトライしました。
（ここで一部コードではわからないとのご指摘があったので、一部加工した全コード投稿で質問します）
 
 
マクロの主な設計としては
・投書番号（カウンター）インクリメント　＋　特定セルの入力　→ファイル名にして保存
・テンプレートはカウンター部分を除いて入力リセットして上書き保存
・保存キャンセルした場合はカウンターも戻してテンプレート保存
 
以下、試作してみたコード（テンプレート.xlsm)です。
 
 
Sub 　カウンタ番号と特定セル入力をファイル名にして保存()
 
'カウンターD4を1インクリメント
  Sheets("Template").Range("D4").Value = Sheets("Template").Range("D4").Value + 1
 
'C4の文字列をファイル名に保存、Templateは保存時（D4以外）クリア
   
  Application.DisplayAlerts = False '確認ダイアログを表示しない
  Application.Dialogs(xlDialogSaveAs).Show Arg1:=Range("C4").Value, Arg2:=12
   
'保存キャンセルされたら カウンターを戻す
  Dim done As Boolean
  done = Application.Dialogs(xlDialogSaveAs).Show
  If done = False Then
  Sheets("Template").Range("D4").Value = Sheets("Template").Range("D4").Value - 1
    MsgBox "キャンセルされました。"
  End If
   
'テンプレートのカウンター以外（結合セル　C11〜C15、E11）入力クリア、上書き保存
  Range("C11").MergeArea.ClearContents
  Range("C12").MergeArea.ClearContents
  Range("C14").MergeArea.ClearContents
  Range("C15").MergeArea.ClearContents
  Range("E11").MergeArea.ClearContents
   
  Application.DisplayAlerts = False
 
  ActiveWorkbook.SaveAs Filename:="テンプレート.xlsm", _
        FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
 
'ブック（Template)を閉じる
  Workbooks("テンプレート.xlsm").Close
   
'Excelを閉じる
   
  Application.Quit
   
 
'保存完了のメッセージ
 
MsgBox "投書が保存されました"
ActiveWorkbook.Close
 
 
End Sub
 
１．実行したところ、一部動作がおかしいながら投書保存できていたのですが、ある時から
”名前を付けて保存”で保存を選ぶと再び元の”名前を付けて保存”に戻り、ここで保存を選ぶと
すでに同じ名前のファイルがあるが上書きするかと訊いてくるようになりました。
 
２．またExcelを最期に終了させようと意図しているのですが、ワークシートは閉じますがExcelは
終了しないで残ってしまっています。
 
 
１．２．の原因、コード誤りはどこにあるのでしょうか？
 
 
 

次のコードを修正してみましょう
 
> Workbooks("テンプレート.xlsm").Close
 
↓
 
 Workbooks("テンプレート.xlsm").Close False

SaveAsメソッドの後ろにはパスとファイル名を両方明示しましょう。
 
ActiveWorkbook.SaveAs Filename:="C:\Users\ユーザー名\フォルダ名\テンプレート.xlsm"
 
すでに同名のブックが存在して、強制的に上書きするなら
DisplayAlerts プロパティをFalseにして上書き保存しますか？のメッセージを出さないようにします。
 
Application.DisplayAlerts = False 'メッセージを出さないようにする
ActiveWorkbook.SaveAs 保存処理
Application.DisplayAlerts = True　'エラー表示も出なくなるのでTrueに戻しましょう
 

WinArrow さん、めんたん さん
 
アドバイスどうもありがとうございました。
 
これは部内の投書箱を創設する投書ツールのテンプレートです。
番号、ファイル名を自動付与することによってデータ管理しようとする目的です。
 
あいにく、本日夕刻からサーバーがメンテに入るということで、
明日お二人から頂いたアドバイス修正を試行って試したいと思います。
 
結果はまた上げます。
 
まずはお礼まで。
 

＞２．またExcelを最期に終了させようと意図しているのですが、ワークシートは閉じますがExcelは
＞終了しないで残ってしまっています。
 
Workbooks("テンプレート.xlsm").Close は、その場で実行され、次のステップは消滅してしまいます。
 
したがって、Application.Quit は実行されません。

ActiveWorkbook
と
Workbooks("テンプレート.xlsm")
は、同じブックですか？
フルパスで同じですか？・・・・と質問です
 
 
しかし、SAveAsでは、フォルダが指定されていないので
デフォルトフォルダーに保存されます。
 
一方、Closeは、開いたブックですから
 
 
マクロブックを閉じてしまうと、
それ以降に記述してあるコードは、実行することができません。
 
Application.Quit
を実行させたのであれば、
「ブックを閉じる」を止めればよいです。
 

業務都合で昨日お二人から頂いた回答の修正はまだ試みていません。
申し訳ありません。
、
その前にご質問のあった事項の回答、および幾つか追加質問をさせて下さい。
 
 
＞ActiveWorkbook と
＞Workbooks("テンプレート.xlsm")
＞は、同じブックですか？
＞フルパスで同じですか？・・・・と質問です
 
入力しているブック→ActveWorkbook
これを一部入力クリアしてテンプレートxlsm 保存
 →この保存されたxlsmファイルを閉じたい。
 
保存後は”Active”でないと思ったのですが、誤った理解だとしたら
ご質問の答えは同じブックです。
フルパスで同じか？＝同じものを指しているので当然パスは同じです。
 
  
   
>しかし、SAveAsでは、フォルダが指定されていないので
 >デフォルトフォルダーに保存されます。
 
�@テンプレートは保存は、保存先ファイルパスも記述、以下のように記述
SaveAs....=”\\.....（サーバー上のファイルパス）￥テンプレート.xlsm
 
�Aファイル名を自動付与させた投稿（xlsx形式で保存）
 Application.DisplayAlerts = False 　　'確認ダイアログを表示しない
 Application.Dialogs(xlDialogSaveAs).Show Arg1:=Range("C4").Value, Arg2:=12 　'File名、形式指定
 Application.DisplayAlerts = True 　　 'エラー表示が出るように設定を戻す
 
と書くと、投稿はデフォルトフォルダ（テンプレートと同じフォルダ）に保存されると思います。
これをもし保存先を指定するとすると、どうコードを書けば良いのでしょうか？
（xlDialogSaveAs　の後にファイルパスを書き加える？）
  
 
＞一方、Closeは、開いたブックですから
＞マクロブックを閉じてしまうと、
＞それ以降に記述してあるコードは、実行することができません。
  
＞Application.Quit
＞を実行させたのであれば、
 ＞「ブックを閉じる」を止めればよいです。
  
 
ActiveWorkbook.Close ですが、先のご質問にも関係しますが不要なのでは？
この行を削除、その後にApplication.Quit を置けば良いということでしょうか？
 
その上さらに”保存完了”のメッセージを表示させたいのであれば
Workbook("テンプレート.xlsm").Close の前の行にMsgBox”保存完了”を置く？
これは誤りでしょうか？
 
また
Workbooks("テンプレート.xlsm").Close False　とする　False　の効果が何か理解できないのですが
 
 
たくさん質問してしまい恐縮です。
 
よろしくお願いします。
 
 
 

>フルパスで同じか？＝同じものを指しているので当然パスは同じです。
 
この認識が間違っています。
SaveAs メソッドでしている
Filenameでは、「ファイル名」だけなので、Excelのデフォルトフォルダに「保存されます。
従って、WorkBooks("テンプレート.xlsm")とは、別物です。
 

Excelの新規ファイル保存のためのデフォルトフォルダは
Application.DefaultFilePath
で確認できます。
 
SaveAsでファイル名だけを指定した場合、
このデフォルトフォルダに保存されます。
 
Msgbox Dir(Application.DefaultFilePath & "\テスト.xlsm")
で、ファイル名が表示されれば、デフォルトフォルダにファイルが存在します。
貴方が、期待しているサーバーに保存しているはず・・・ならば、空白文字列が返ってきます。
 
確認してみてください。

>デフォルトフォルダー
と書いてしまいましたが、
殆どは、カレントフォルダ
と説明されています。

WinArrow さん
 
自分の書いたコメントで (←部分）
 
�@テンプレートは保存は、保存先ファイルパスも記述、以下のように記述
SaveAs....=”\\.....（サーバー上のファイルパス）￥テンプレート.xlsm 　　←　　指定先
   
 �Aファイル名を自動付与させた投稿（xlsx形式で保存）
 Application.DisplayAlerts = False 　　'確認ダイアログを表示しない
 Application.Dialogs(xlDialogSaveAs).Show Arg1:=Range("C4").Value, Arg2:=12 　'File名、形式指定
 Application.DisplayAlerts = True 　　 'エラー表示が出るように設定を戻す
  
と書くと、投稿はデフォルトフォルダ（テンプレートと同じフォルダ）に保存されると思います。 ←Default
これをもし保存先を指定するとすると、どうコードを書けば良いのでしょうか？ 　←←　？
 （xlDialogSaveAs　の後にファイルパスを書き加える？）
 
テンプレートと投稿は別物で、保存先もそれぞれ異なることは理解しています。
ご質問の、同じパスですか？　は　同じファイルですか？の意味だったのですね。
 
自分は、テンプレート上に入力した状態（Active)ではまだDefaultへ保存移動していない、
だから同じ場所にあるので同じファイルパスにある、この大きな理解の誤りから先のトンチンカンな
回答を差し上げてしまいました。
 
←←　部分　は
（xlDialogSaveAs￥保存先パス）.Show Arg1:=　...　なんて間違いだと思いますが、
　保存先をDefaut(=Current)から指定先にするにはどう書けば良いでしょうか？
 
因みに異常動作（名前を付けて保存：”保存”を選んでも再度　名前を付けて保存　の表示に）
をしていますが、保存できた投稿はテンプレートを置いたフォルダと同じところに保存されています。
 
再度名前をつけて保存を訊いてきたところで”保存”を選ぶと”
”同じ名前のファイルがある、上書きしますか？”　とダイアログ表示されるので
最初に”保存”押したときに投稿はDefaultに保存されている、しかし何故かもう一回
名前を付けて...と表示される。　というのが異常元のような気がします。
 
 
本日はまず、
 
・Workbooks("テンプレート.xlsm").Close False
 
・テンプレート（入力クリア）を保存する　Filename:= のところに保存先パスを記述
 
を試して動きその他を確認してみたいと思います。
 
 
 

素朴な疑問
 
(1)マクロブックを開いて
(2)〜〜〜処理
(3)ブックを保存
 
という概略な流れと思います。
(3)の時点で
なぜ「名前を付けて保存」(SaveAs）なんでしょう。
単純に、Saveメソッドではいけないのでしょうか？
その後にい、pplication.Quit すれば、
マクロブックは閉じられます。
 
ブックを閉じた後、ブックに記述したコードを実行できない・・・・自由は
実行すべきコードがメモリ上に存在しないから・・・

>(3)の時点で
> なぜ「名前を付けて保存」(SaveAs）なんでしょう。
> 単純に、Saveメソッドではいけないのでしょうか？
 
テンプレートはマクロ実行のxlsm形式
テンプレートを使って入力、保存する投稿ファイルはマクロ不可のxlsx形式
 
のためにSaveAs　FileFormat＝　としたのですが..。
 
 
＞その後にい、pplication.Quit すれば、
＞マクロブックは閉じられます。
 
この行は省略しても良いかなと思い始めています。Excelが残っても良い
→この時点で保存したファイルを再度開いて確認、修正したり...　その後ウインドウの×で終了。
  
＞ブックを閉じた後、ブックに記述したコードを実行できない・・・・自由は
＞実行すべきコードがメモリ上に存在しないから・・・
 
先のコメントでご指摘頂いたように、Workbook　Close　はコードが終了するためですね。
Excelも終了させたいとすると
 
Application.Quit　　　’コード処理が終わるまで保留する機能？
Windows("テンプレート.xlsm").Close 　’テンプレートのウインドウ＝Excel　を閉じる
 
とでも書けば可能なのでしょうか。
 
 
外出の都合上、コード修正、実行テストは夕刻〜になりそうです。
またご報告します。

引用:
先のコメントでご指摘頂いたように、Workbook　Close　はコードが終了するためですね。
Excelも終了させたいとすると
  
Application.Quit　　　’コード処理が終わるまで保留する機能？
Windows("テンプレート.xlsm").Close 　’テンプレートのウインドウ＝Excel　を閉じる
  
とでも書けば可能なのでしょうか。

今回の処理(プロシジャ）の中には
２つのファイルが存在すると思われます。
 
１つは、プロシジャを実行しているマクロブック(テンプレート.xlsm）
 
もう一つは、シート名不明の
Range("C4")セルに記述してあるファイル名(???.xlsx)
 
後者は、確かに新しいブックとして名前を付けて保存する太陽ですが、
> Application.Dialogs(xlDialogSaveAs).Show Arg1:=Range("C4").Value, Arg2:=12
で、保存しているから、ＶＢＡコードで「SaveAS」は不要です。
 
今回問題となっているのは、
前者の「テンプレート.lsm」です。
このファイルを、なぜ、SaveAS（名前を付けて保存）する必要があるか？
という質問です。
 
もう一度、貴方が記述したコードをじっくりと確認してみましょう。
 

コードの中で分からないところ
>Application.Dialogs(xlDialogSaveAs).Show Arg1:=Range("C4").Value, Arg2:=12
の時の
Range("C4").Value
には、どのような値が入っているのでしょうか？
 
ここで、
貴方が意図するフォルダに保存されているのでしたら、問題はないと思います。
 
それよりの、保存を「キャンセル」された後は、カウンタを元に戻しているだけで
よいのですか？
・・・・そうであるならば、マクロブックは上書き保存せずに終了したほうがよいのでは？

最初からコードを読み直してみました。
 
なぜ、SaveAsメソッドで、テンプレート.xlsmを上書きしているのか
わかりました。
 
こちらもつまみ食いをしていたので、
貴方の意図と違う回答をしてしまいました。
 
一つのブックを使い分けているため、
ストーリがわかりにくくなっています。
 
最初に「xlsx」で保存した時点で、
「テンプレート.xlsm」とは別名のブック名になってしまいます。
 
この処理を、必要なシートを選択して、新しいブックに複写。
(例)
Sheets("Template").Copy
と記述すると、Sheets("Template")だけに新しいブックが作成されます。
それを「xlsx」として保存するようにします。用済後は閉じます。
 
そうすれば、「テンプレート.xlsm」名は、最後まで変わらないので
説明通り、上書き保存（Saveでも対応）できます。
 
 
 
Excel終了処理
３通りのパターンで説明します。
第１パターン
    Application.Quit '�@
    MsgBox "テスト1終了" '�A
    ThisWorkbook.Close True '�B
　※素直に終了します、
第２パターン
    Application.Quit '�@
    ThisWorkbook.Close True '�B
    MsgBox "テスト1終了" '�A
　※�Aは実行されません。
第３パターン
    ThisWorkbook.Close True '�B
    MsgBox "テスト1終了" '�A
    Application.Quit '�@
　※�A�@は実行されません。
 
Application.Quitは、この時点で即実行されるわけでなく、End Sub後に実行されます。
 
 
 
 

引用:
Application.Dialogs(xlDialogSaveAs).Show Arg1:=Range("C4").Value, Arg2:=12
     
 '保存キャンセルされたら カウンターを戻す
  Dim done As Boolean
   done = Application.Dialogs(xlDialogSaveAs).Show
   If done = False Then
   Sheets("Template").Range("D4").Value = Sheets("Template").Range("D4").Value - 1
     MsgBox "キャンセルされました。"
   End If

WinArrow さん
 
たくさんのご丁寧なコメントどうもありがとうございました。
 
 
先に頂いていたコメント内容の修正（以下）を行って、マクロ実行してみました。
・Workbooks("テンプレート.xlsm").Close False
・テンプレート（入力クリア）を保存する　Filename:= のところに保存先パスを記述
 
 
保存パス（\\共有サーバー上のファイルパス）を記述したところ、デバッグ段階でエラーと
なってしまいました。（試しに元に戻すとデバッグエラーはなくなります）
 
あと、やはり名前を付けて保存を二回訊いてくるのですが、ステップデバッグで探っていて、
よくよくコードを眺めると、キャンセルしたときにカウンターを戻すの処理で
再度xDiaogSaveAsを書いているあたりに問題がある、IFで処理を切り分けるべきでは？
と思った次第。
→今回3番目のコメントで書いて頂いた提案のコードがその解決かと。
 
 
昨日はここまでした。
 
 
 
今朝新しいWinArrowさんのコメントを拝見して
 
 
＞コードの中で分からないところ
>Application.Dialogs(xlDialogSaveAs).Show Arg1:=Range("C4").Value, Arg2:=12
＞の時の Range("C4").Value には、どのような値が入っているのでしょうか？
 
文字列です（ファイル名に自動付与させる）
日付関数から自動取得した日付（TEXT変換）に層別用に別の文字列を頭に加えたものを
”C4”に入力されるようExcel（テンプレート）に組み込んでいます。
  
 
  
＞それよりの、保存を「キャンセル」された後は、カウンタを元に戻しているだけで
＞ よいのですか？
＞・・・・そうであるならば、マクロブックは上書き保存せずに終了したほうがよいのでは？
 
カウンターは投稿者が同じファイル名で保存してしまわないよう、後集計で都合よく連番になるように
と組み込みました。
投稿前（テンプレートを開いたとき）、カウンターは先に投稿した人の番号がそのまま残っていて
保存実行するとカウンターがインクリメントされて連番となるように、と意図しました。
 
キャンセルする
→カウンターを入力前（先の投稿者の保存番号）に戻す＋他セルの入力はクリア
→その戻したカウンター番号を上書き→テンプレート.xlsm を投稿前のテンプレート状態に
 
上書き保存は、次の投稿者がテンプレートを開いたとき、先の投稿者のカウンター番号が
（先の投稿保存ファイル名：”C4"付与したファイル名）が残っているようにとの思惑。
ここを上書きしないで保存するとここが実現されないのでは？と思ったんですが。
 
 
 
＞最初に「xlsx」で保存した時点で、
＞「テンプレート.xlsm」とは別名のブック名になってしまいます。
＞この処理を、必要なシートを選択して、新しいブックに複写。
 
すみません、ここが理解できていません。
 
別名のブック名になる、とは開いているブック（テンプレート）とは別ブックになる、ということ？
 
＞Sheets("Template").Copy
＞と記述すると、Sheets("Template")だけに新しいブックが作成されます。
＞それを「xlsx」として保存するようにします。用済後は閉じます。
 
ここでコピーする”Template”　とは、投稿者が入力がされた状態の テンプレート.xlsmを
指している→　それを　xlsx形式指定して（別ブック）保存する？
 
＞そうすれば、「テンプレート.xlsm」名は、最後まで変わらないので
＞説明通り、上書き保存（Saveでも対応）できます。
 
この前段階にファイル名自動付与した保存を訊く
　Dialogs(xlDialogSaveAs).Show Arg1:Range("C4"....　がありきだと思うのですが、
この後行（保存をクリックされた後処理）に？
 
Sheet("Template”).Copy ...のコードでは、xlsx保存の際のファイル名（および形式）指定
はDialogs(... のコード以外にどう記述するのか？
 
テンプレートはカウンター番号（投稿によってインクリメントされた、ファイル名に自動付与）を
維持、他はクリアして保存。ここは最後に処理しておいて上書きしないといけないが..。
 
すみません。自分のVBA知識で、ご提案コードの動作、記述が理解できていません。
 
 
今回頂いた回答全てを元に、コード修正検討（およびVBA勉強）、試行してみます。
 

私見ですが
 
>テンプレート
と表記しているので、違和感がありました、
>投書番号
の管理ファイルなのですね？
投書番号だけなのか？純粋なテンプレートを含んでいるのかなのか、よくわかっていません
 
投書番号に限っていうと
新しく作成するファイル名に持ち込むのであれば、
新しく作成するファイルの中に持ち込む必要はないと考えます。
 
また、新しいファイルを保存する場合、ダイアログを表示して、
保存するか否かを問うていますが、保存する場合だけ
投書番号を更新すればよいでしょう。
先に投書番号更新してしまうので、保存キャンセルで「元に戻す」なんてことをsる必要ができます。
ダイアログに表示する投書番号は更新しなくても
「現投書番号＋１」という形で指定すれば、表示できますよね？
 
次に、テンプレート部分について
雛形シート（またはファイル）を用意しておいて
単純な話、ひな形を複写（ファイルだったら、ファイルコピー）すれば、中身をクリアするなど
しなくても対応できると思います。
因みにテンプレートファイルは、xlsx形式でもよいと思います。
 

WinArrow さん
 
テンプレートと称しているのは、管理番号だけではありません。
Excelの入力画面を説明していなかったので、ですが各入力項目がある
入力シート＝投稿のテンプレートです。
（感想アンケート票のようなものと思って頂けば近いかと）
 
 
私見で思われたことは、全部ぼもっともだと思います。
 
全体の構成を考えないで、ここはどう書いたら、そうしたらこれとはどう繋いだら良いか...
の如く、脈絡なく構築していったので余計なコード処理だらけになっているのは否めません。
 
 
頂いたこれまでのコメントを元に、抜本的にコードを改良してみたいと思います。
（合理的に、無駄なく）
他業務の関係もあり、一旦ここでお礼とさせて頂き、改良の上また改めてとさせて下さい。
 
 
たくさんのインストラクション、アドバイス等のコメント
どうもありがとうございました。
 
、