エクセルVBAを保存時に消したい

質問

はじめて質問させて頂きます。
エクセルのVBAを覚え始めたばかりの物ですが、
見積書式を作成し、見積番号をVBAでファイルOPEN時に自動挿入し
名前を付けて保存する時はその見積番号が保存する時にファイル名に
なるようにVBAを作成しました。
見積番号の呼び出し方法は
指定フォルダにある（.xls）ファイルの数+1としています。

ここで質問なのですが現状だと保存したファイルにはVBAが存在するので
そのファイルの修正をする時マクロの実行の有無を聞いてきます。
実行しないを選べば見積番号は変わらないのですが
間違えて実行してしまうとそのファイルの見積番号が変わってしまいます。。
回避方法として知り合いからアドインファイルにすれば？と言われて
保存形式をxlaにしたのですがエラーが出てしまいました＞＜
Const FPath = "C:\指示書"
Sub Auto_Open()
'xlsファイル検索
With Application.FileSearch
.NewSearch
.Filename = "*.xls"
.FileType = msoFileTypeAllFiles
.LookIn = FPath
.SearchSubFolders = False
.Execute

Cells(1, 21).Value = .FoundFiles.Count + 1
Cells(1, 21).NumberFormat = "0000"
End With
End Sub

したから4行目のCells(1, 21).Value = .FoundFiles.Count + 1
でエラーが出てしまうようで。。原因がわかりません。
何が原因なのでしょうか？＞＜

回答No.10

onlyrom · ベストアンサー

またまたまたまた登場、onlyromです。

（条件と処理内容）
（１）フォルダーは　”C:\指示\記入済”
（２）保存ボタンで新見積書を保存する
　　　但し、マクロコードとボタンを削除したものを保存する
（３）新見積書の保存後はブック、エクセルともに終了する

なお、質問者のコードを書き換えた部分がありますので以下のコードは、そのままコピペして下さい。
フォルダー名は適宜変更のこと。

'--- Module1 ----変数FPathは、Publicで宣言しないといけません

Public Const FPath = "C:\指示\記入済"

Sub Auto_Open()
　With Application.FileSearch
　　.NewSearch
　　.Filename = "*.xls"
　　.FileType = msoFileTypeAllFiles
　　.LookIn = FPath
　　.SearchSubFolders = False
　　.Execute

　　Cells(1, 21).Value = .FoundFiles.Count + 1
　　Cells(1, 21).NumberFormat = "0000"
　End With
End Sub

'----- Module2 -----ボタンに登録されたマクロ-----

Sub ファイルに名前を付けて保存()
　Dim 既定ファイル名 As String
　Dim 保存ファイル名 As Variant

既定ファイル名 = FPath & "\" & Range("T1") & Format(Range("U1"), "0000") & Range("B1") & ".xls"

保存ファイル名 = Application.GetSaveAsFilename(既定ファイル名)

　　If 保存ファイル名 = False Then
　　　　MsgBox "保存は中止されました"
　　　　Exit Sub
　　End If

　ActiveWorkbook.SaveCopyAs 保存ファイル名

　Dim NewBook As Workbook
　Set NewBook = Workbooks.Open(保存ファイル名)

　Dim myVBA As Object
　For Each myVBA In NewBook.VBProject.VBComponents
　　　With myVBA
　　　　If .Type = 100 Then
　　　　　.CodeModule.DeleteLines 1, .CodeModule.CountOfLines
　　　　Else
　　　　　Application.VBE.activeVBProject.VBComponents.Remove myVBA
　　　　End If
　　　End With
　Next myVBA

　NewBook.ActiveSheet.Shapes(1).Delete
　NewBook.Close True

'●●●
　Set NewBook = Workbooks.Open(保存ファイル名)
　NewBook.Close True
'●●●

'ブックとエクセル終了
　Application.Quit
　ThisWorkbook.Close False

End Sub
'----------------------------------------------------

今度は、SaveAsではなく、SaveCopyAsメソッドを使用しなければいけません。
●●●の間の２行は、お呪い？ということで。。
これ新しい発見でした。（感謝）

■新しい補足の記述についての注意
家で新しい見積を作成するときは、会社のパソコンから”C:￥指示”をまるごと家のパソコンにも入れておかないと拙いですよね。
要するに、会社も家も”C:￥指示￥記入済”の中のファイルの数は常に同じものにしとかないといけないということです。
もちろん、家では会社で作成した見積書のメンテだけするというのであれば別ですが。　

コードの説明がいるときはお気軽にお尋ねください。
以上。

お礼

無事出来上がりました！本当にありがとうございました♪

回答No.2

kokorone

別な提案
１）見積書テンプレートシート＆ＶＢＡというブックを作成する。（マスタ）
２）ＶＢＡにて新規ブックを作成し、マスタ内の見積書テンプレートシートを新規ブックにコピーし、余分なシートは削除する。
３）作成したブックを見積書番号＋”.xls”という名前で保存する。

これならば、作成した見積書には、ＶＢＡは含まれないはずですが、
いかがでしょうか？
見積書を作成するのは、質問者さまではなく、実務担当者でも、一度
マスタを起動してもらい、見積書を作成後、あらためて、見積書編集
を行ってもらうことも可能だと思います。

お礼

お返事が遅くなってすみません。
自分のVBAの知識が無さすぎてできるかどうか。。。（泣

回答No.1

kokorone

質問者の主旨とは異なるのを承知で、

要するに、最初の1回だけＶＢＡが実行されればよい
というのであれば、
作業用に非表示のセルを用意します。
この非表示のセルが、ブランク（未設定）の場合のみＶＢＡの処理を実行するよう、条件式を追加します。
実行後、非表示セルに、任意の値（文字）を入れます。

このようにすれば、2回目以降、ＶＢＡは実行されません。
但し、ＶＢＡそのものは残っているため、ＶＢＡ実行の確認
ダイアログは、起動毎に表示されてしまいます。

お礼

お返事有難うございます。
やっぱりVBAの実行確認画面は修正時は出したくありません。
PCをあまり知らない人達が出るたびに呼ぶので（泣）

空書類をVBA実行しながら立ち上げそれを名前を付けて保存する。
その保存した書類は今後VBAを実行しないので（してはいけない）
VBAを取り除きたい。

最悪はkokoroneさんの方法でも。。。とは思うのですが、
はじめてのVBAで自分なりにココまでがんばれたので
なんとか納得できる物を作りたいと思います。