AccessからのExcel出力

質問

お世話になります。
Access2010

フォームに「Excel出力」ボタンを設置し、押すとクエリの内容を
Excelに出力するツールを作成しました。

具体的には、ボタンを押すと出力先を指定するダイアログを表示し、
フォルダが選択されたら、そのパスを取得し、そこにExcelファイルが
作成されます。
出力されるファイル名は「情報_yyyymmdd.xlsx」です。

DoCmd.TransferSpreadsheet acExport, 10, "Q_Dummy", strFolder & "情報_" & Format(Date, "yyyymmdd") & ".xlsx", True, ""

strFolderは、ダイアログで選択されたパス
Q_Dummyは出力するクエリ

＜質問1＞
上記にて問題無く出力はされますが、例えば今日だと「情報_20160704.xlsx」
というファイルをデスクトップに作成したとします。
で、再度デスクトップに作成しようとした場合「既に同じ名前のファイルが
あります、上書きしますか？」というメッセージは表示されず
そのまま上書きされてしまいます。

DoCmd.TransferSpreadsheet acExportを実行する前に、事前に出力先の
パスに同ファイル名が存在していないかチェックした上で、手動で
「上書きしますか？」のメッセージを表示させる必要があるのでしょうか。

＜質問2＞
「そのまま上書きされてしまいます。」と書きましたが、実際には
マージされてしまうようです。

例えば、2レコード抽出されたクエリの内容を出力し、そのExcelを開いて
表外に「ああああ」と入力します。
次に条件を変えて3レコード抽出されたクエリの内容を出力します。
そうすると、ちゃんと3レコードの内容が出力されていますが
表外に入力した「ああああ」は残ってます。
※画像1

また、表内のセルに「AAAA」と入力し、そのセルに色付けした状態で、
再度出力すると、「AAAA」は上書きされますが、色は付いたままです。
※画像2

という結果から、
　・ファイル自体が作成し直される訳ではない。
　・上書きされる部分はあくまでもクエリの内容（表内）の部分だけ
　　であり、書式や表外の部分については上書きされない。
ということになるかと思います。
その認識で問題ありませんでしょうか。

上書きされないように（そもそも同じファイル名にならないように）
ファイル名を「情報_yyyymmdd_hhmmss.xlsx」にすればいいかとは
思うのですが。。

ご教示の程、宜しくお願い致します。

回答No.2

chie65536（@chie65535） · ベストアンサー

Dim FSO As Object, Target As String
Set FSO = CreateObject("Scripting.FileSystemObject")
Target = strFolder & "情報_" & Format(Date, "yyyymmdd") & ".xlsx"
If FSO.FileExists(Target) Then
If MsgBox(Target & "が存在します。上書きしますか？",vbOKCancel,"上書き確認") = vbOK Then
Kill Target
Set FSO = Nothing
Else
Set FSO = Nothing
Exit Sub
End If
End If
DoCmd.TransferSpreadsheet acExport, 10, "Q_Dummy", Target, True, ""

お礼

chie65535さま
いつもご回答ありがとうございます。

結局、出力先のパスに同じファイル名のものが存在して
いないかチェックした上で「上書きしますか？」って
メッセージを表示させる必要があるんですね。
で、上書きOKとなったらいったん既存のファイルを削除
した上で出力すると。。

VBAの場合は、そこまで手動でやってあげないといけないん
ですね。
勉強になりました。ありがとうございました。

回答No.4

panacon

VBで同名のファイルがあったら、メッセージボックスで処理を分岐するようにすれば良いのでしょうが、VBのほうは浮かんできません。フォーム上で、出力先フォルダのパスを保存するテキストボックスと日付のテキストボックスを用意して、これを合成した式を貼り付けておくアイデアが浮かびましたが、なんだかなぁという思いです。
過去の経験では、同じファイルがいる場合は、日付時刻を付加する形でリネイムして、保存フォルダに移動してから、新しいエクセルを書き出していました。

お礼

panaconさま
ご回答ありがとうございます。
やはり手動で同ファイル名の有無をチェックして
メッセージを出すしかないんですかねぇ。。
で、OK押されたら既存のファイルを削除した上で
Excel出力って感じでしょうか。

・・ここまでやるんだったら、やはりファイル名に
時間を付加した方がよいように思えてきました。

とりあえずもう少し検討してみたいと思います。
ありがとうございました。

回答No.3

panacon

既存のマクロに変数を含むファイル名を設定する書き方を試してみました。
既存のマクロで、2013の場合ですが、「書式設定を保持したままエクスポート」を選択して、出力先の記述を「="C:\フォルダ名\ファイル名前半" & format(now(),"yy-mm-dd") & ".xlsx"」のように書けば良い事が分かりました。日付のスラッシュ記号は、ファイル名には使えないので、ハイフンにしてみました。これなら要求されたものが実現します。
2010でも同名のマクロか「出力」のマクロが選べると思います。

お礼

panaconさま
度々のご回答ありがとうございます。
>「="C:\フォルダ名\ファイル名前半" & format(now(),"yy-mm-dd") & ".xlsx"」のように書けば良い事が分かりました。
ということですが、質問にも書いている通り、出力先は
ユーザーに選択してもらう（ダイアログから出力先選択）
ようにしたいと思ってます。
この方法だと、出力先が固定になってしまうと思うのですが。。

回答No.1

panacon

VBで書くと上書きし、既存のマクロの組み合わせで書くと上書きするか聞いてくると思います。勿論、ご自身の解決策の通り、時間までファイル名にすれば問題無いと思います。

お礼

Panaconさま
ご回答ありがとうございます。

「Excel出力」ボタンにマクロを仕込んだところ、仰る通り
上書きするか聞かれました。
ただ、マクロには予め出力先のパスやファイル名を設定して
おかないといけないようです。

普段マクロを使っていないので、よく分かっておりませんが
VBAから出力先パスとファイル名をマクロに渡して実行する
ことも可能なのでしょうか。

補足

＜追記＞
実際には、出力される項目として日付のフィールドがいくつか
あります。
VBAで出力するとyyyy/mm/dd形式で出力されるのですが、
マクロで出力するとdd-mmm-yyで出力されてしまいます。
※例）1980/01/01 →　01-Jan-80

使用者にわざわざExcel上でyyyy/mm/dd形式に変換させるのは
手間なので、初めからyyyy/mm/dd形式で出力させたいところです。