エクセル2003VBA 新規ブックを作成、保存の時に

質問

いつも勉強させていただいております。
今回、VBAで以下の事をできるのかな？と思い質問させていただきました。
まず、VBAで以下の動作を作成しました。
Aファイル、データーベースCSVファイル
Bファイル、VBA記述ファイル
１）Bファイルから、Aファイルを開いて、検索、条件に合うデーター
を配列で抽出。
2)新規ブックを追加
3)新規ブックに抽出したデーターを書き出し
と言うVBAを作成しました。
で、質問なのですが、新規ブックは保存するか、しないかがその時々に
よって任意でするため、VBAで保存の所までは作成していないのですが
、保存する場合、ファイル名に規則性を持たせたいため、ファイル保存
をしたときに表示させる初期ファイル名をBファイルのVBAから変更
させる事はできるのでしょうか？
新規ブックを保存するとき、初期ファイル名は「Book1.xls」となって
いると思うのですが、この値を変更することってできますでしょうか？
もし可能であれば、ヒントや参考になるHPなど教えていただけないで
しょうか。

回答No.4

noname#52504 · ベストアンサー

ご質問の趣旨を取り違えていましたらすみません。

・自動保存はしない(保存するか否か、事前にはわからない）
・手動で[名前を付けて保存]しようとした場合に
　ダイアログで表示されるファイル名を指定したい。　

ということですよね。

●Ａ案：新規ブックを作成する際、まずテンプレを作り、それから作成する

　ちょっとトリッキーですが、手軽な近似案として。

　動作の概要
　　"ほにゃららX"という名前の新規ブックを作成する(Xは連番)

　Ｂファイル(VBA記述ファイル)上に記述して、
　新規ブック作成時に動作させることを想定しています。
　連番がついてしまう点に目をつぶれば、ご要望に近い動作かと思います。

　'----------↓ココカラ↓----------
　Sub Sample()
　　myStr = "ほにゃらら" & ".xlt"
　　Workbooks.Add.SaveAs myStr
　　Workbooks.Add myStr
　　Kill myStr
　End Sub
　'----------↑ココマデ↑----------

●Ｂ案：新規作成するブックにイベントドリブンマクロを仕込む

　「マクロでマクロをいじる」のはいろいろと微妙な問題が絡むので、、
　あらかじめマクロを記述したブックをテンプレとして用意しておき、
　そのテンプレから新規ブックを作成するのが簡単かと思います。

　マクロは
　・新規ブックを作成してファイル名を渡すマクロ：Ｂ１
　　※Bファイル(VBA記述ファイル)側
　・保存のタイミングを捉えてダイアログを制御するマクロ：Ｂ２
　　※テンプレ＝新規ブック側
　に分かれます。

　ファイル名の渡し方については、
　隠しセル，名前，DocumentProperty等いろいろ考えられますが、
　とりあえず例として、ブックのプロパティの[タイトル]を使いました。

　動作の概要（Ｂ１）
　　[C:\myBook.xlt]から、マクロ付き新規ブックを作成し、
　　ファイルのプロパティ[タイトル]にファイル名を記述する。

　↓Ｂファイル(VBA記述ファイル)上に記述して新規ブック作成時に動作させる
　'-------------------↓Ｂ１ココカラ↓-------------------
　Sub Sample2()
　　myStr = "ほにゃらら"
　　Set mybook = Workbooks.Add("C:\myBook.xlt")
　　mybook.BuiltinDocumentProperties("Title") = myStr
　End Sub
　'-------------------↑Ｂ１ココマデ↑-------------------

　動作の概要（Ｂ２）
　　[名前をつけて保存]しようとしたら、
　　[タイトルプロパティに記述されている文字列]をファイル名としてダイアログを開く。

　↓これをThisWorkbookモジュールに記述したテンプレートを、
　myBook.xlt(拡張子に注意)という名前でCドライブルート直下に用意しておく。
　'--------------------------↓Ｂ２ココカラ↓--------------------------
　Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
　　If SaveAsUI Then
　　　Cancel = True
　　　myStr = ThisWorkbook.BuiltinDocumentProperties("Title")
　　　With Application
　　　　.EnableEvents = False
　　　　.Dialogs(xlDialogSaveAs).Show myStr
　　　　.EnableEvents = True
　　　End With
　　End If
　End Sub
　'--------------------------↑Ｂ２ココマデ↑--------------------------

もっとスマートな方法がありそうな気もしますが、とりあえずご参考まで。

お礼

deus_ex_machina様、ご回答ありがとうございました。
今回のコードを試行錯誤している間に本職の業務の方が
多忙となってしまい、課題に取り組めていないのが現状で
あります。
ただ、サンプルコードをテストして、解決の糸口が見えて
来ている状態であります。
今後、更に皆様のコードを試行錯誤行いながら、望むもの
を作って行きたいと思っております。
ご回答頂き、誠にありがとうございます。

補足

deus_ex_machina様、ご回答ありがとうございます。
趣旨の点については、ご返答いただいた通りであります。
まだ、サンプルコードの方は現時点でテストできていませんので
この後、コードを試して行きたいと思います。
まずは、お礼申し上げます。
皆様のコードをこれから試して、理解していきたいと思いますの
で、少しお時間をいただきたく思います。
ありがとうございます

回答No.5

Wendy02

こんにちは。

#3の回答者です。たぶん、マクロは、これからも勉強されるのなら、一つ別の方法も紹介しておきます。

インスタンスを設ける方法です。
ただし、二重のインスタンスは、同じものがぶつからないようにしてください。
Application は、Application だけにしてください。

'-------------------------------------------
'Class1

Public WithEvents App As Application
'ベース名
Private Const BASENAME As String = "AAA"

Private Sub App_WorkbookBeforeSave(ByVal Wb As Workbook, ByVal SaveAsUI As Boolean, Cancel As Boolean)
　　Cancel = True
　　Application.EnableEvents = False
　　If Not Wb.Name Like "*.*" Then
　　　　Application.Dialogs(xlDialogSaveAs).Show BASENAME, 1
　　Else
　　　　Cancel = False
　　End If
　　Application.EnableEvents = True
End Sub
Private Sub Class_Terminate()
　Set App = Nothing
End Sub

'-------------------------------------------
'標準モジュール
Public myClass As New Class1
Sub Auto_Open()
　Set myClass.App = Application
End Sub

お礼

Wendy02様、ご回答ありがとうございます。
また、こちらのお返事が遅くなりまして、申し訳ありません。
現段階で、本職の業務が忙しくなってしまい、VBAの検討について
中途の状態で止まってしまっております。
まだ、現段階で課題をクリアできていませんが、サンプル
コードを基にスキルアップしていきたく思っております。
今回の質問については、一度これにて閉じさせていただきます。
毎回、質問のたびにご回答頂き、誠に感謝しております。
ありがとうございます。

回答No.3

Wendy02

こんにちは。

#1の回答者です。

>VBAで導入した新規エクセルbookに対して保存する場合のときに(Ctrl + S)　をしたときに表示されるファイル名の初期値を変更することが可能なのかな？と考えております。

もう一度、質問を最初から読み直してみました。正直なところ、どうも良く分かりません。

新規ファイル作成自体は、あくまでも、Book1,Book2,... という仮想の名前のはずです。「Ctrl + S 」は、上書き保存するときのショートカットですが、仮想の名前のときは、上書きを自動的にされずに、名前の変更を聞いてくるという仕組みになっているかと思います。

その時に、たとえば、日付ファイル名にするとか、CSV のベース名を拾うとか、具体性が必要です。また、同一のベースの名前があって、その後に、枝番を取っていくとか、それなりに別のテクニックが存在しています。

サンプルを一つ示しておきます。以下は、マクロブックを再読み込みするか、Auto_Open を実行すれば、キーが、設定されますから、現在の段階では、Ctrl + S に対して、CSV ファイルを開いているときだけに、別の名前を付けることが可能です。ActiveWorkbook は、新規のブックです。

こういうことではないというと、今度は、新規ブックに対してインスタンスを設けなくてはなりません。

'標準モジュールのみ

Sub Auto_Open()
　Application.OnKey "^s", "SaveNameChange"
End Sub
Sub Auto_Close()
　Application.OnKey "^s"
End Sub

Sub SaveNameChange()
　　Dim wb As Variant
　　Dim Fn As String
　　For Each wb In Workbooks
　　　　If StrConv(wb.Name, vbUpperCase) Like "*.CSV" Then
　　　　　　Fn = Mid$(wb.Name, 1, Len(wb.Name) - 4)
　　　　　　Exit For
　　　　End If
　　Next wb
　　If Fn <> "" And Not (StrConv(ActiveWorkbook.Name, vbUpperCase) Like "*.*") Then
　　　　Fn = Fn & ".xls"
　　　　Application.Dialogs(xlDialogSaveAs).Show Fn, 1

　　Else
　　　　Application.CommandBars.FindControl(, 3).Execute
　　End If
End Sub

補足

Wendy02様、いつもありがとうございます。
ご提示いただいたコードをテストしながら
再度、考えていきます。
今回、いろいろ現時点で理解できたことを少し
補足説明させていただきますと、
新規ブックを導入したときに得られる仮想ファイル名が、
その後、手動で保存を行おうとしたとき、この仮想ファイル
名が表示されるのであると言う事が理解できました。
このことから、この新規ブックを導入するときに、この
仮想ファイル名に対して処理、もしくは何らかの擬似的操作
を行っていく必要があるのかな？
と考えたりしております。
頂いたコードもまだ、現時点ではテストできていませんので、
この後、試行錯誤を行って行きたいと思います。
ご返答頂きありがとうございます。

回答No.2

fumufumu_2006

ANo.1さんが
>新規でブックを開いたときは、まだ、本当は、".xls" の拡張子はついていません。保存して初めて拡張子がつきます。
と説明した後にサンプルも書いていますが、若干操作をしているため、

捕捉で、
>を招いてしまいました。もしくはご回答していた
>だいた内容を自分が理解できていないのかも
>しれません。

となっているのではないでしょうか？

もっと直観的に、以下を試してみると、わかると思います。
特に、２回目以上実行すると、１回目との違いがわかると思います。（必ずしも「Book1.xls」ではない）
２回目以降は上書きするか聞いてくるので、「はい」を選んでください。
Sub test()
Dim 新規のブック As Workbook
Set 新規のブック = Workbooks.Add

MsgBox 新規のブック.Name '<-とりあえずつけられた名前
新規のブック.SaveAs Filename:="c:\名前付けのテスト" '"c:\サンプル.xls" でも同じ結果
MsgBox 新規のブック.Name '保存したので、名前が確定して、.xlsも付く
新規のブック.Close
End Sub

かえってわからなくなったらすみません。

補足

fumufumu_2006様、ご回答ありがとうございます。
もう一度、皆さんがご提示いただいたコードを
試しながら、自分の理解できていないところを
整理していきたいと思います。

回答No.1

Wendy02

こんばんは。

どちらでもよいかもしれませんが、ぱっと見た感じでは、

>１）Bファイルから、Aファイルを開いて、検索、条件に合うデーターを配列で抽出。
>2)新規ブックを追加

逆かなって思います。新規ブックを作って、そのオブジェクトを保持しながら、Aファイルを配列で抽出して掃出し、保存ですね。

>新規ブックを保存するとき、初期ファイル名は「Book1.xls」となって
>いると思うのですが、この値を変更することってできますでしょうか？
新規でブックを開いたときは、まだ、本当は、".xls" の拡張子はついていません。保存して初めて拡張子がつきます。

基礎レベルの話ですが、こんな感じですね。

fn = "aaa.csv"
BaseFn = Mid(fn, 1, Len(fn) - 4)
With Workbooks.Add
　'ここでCSVインポート（サブルーチンでも良い）
　.SaveAs BaseFn & ".xls" '本当は、.xls は要らない
End With

補足

wendy02様、いつもご回答ありがとうございます。
自分の質問の仕方が上手く出来ていないため誤解
を招いてしまいました。もしくはご回答していた
だいた内容を自分が理解できていないのかも
しれません。
今回、VBAでは保存の動作をさせない方向で考えております。
ただ、VBAで導入した新規エクセルbookに対して
保存する場合のときに(Ctrl + S)　を
したときに表示されるファイル名の初期値を
変更することが可能なのかな？と考えております。