[VBA] 2回目実行時に強制終了

Question

指定範囲をCSVで出力する処理を書き、希望通りの挙動になりました。
ただ一つ問題があり、ファイル起動後初回実行時は正常動作、しかしもう一度同じ処理を実施するとExcelがフリーズし止まったままになるか、Excelがダウンし強制終了します。
ファイル起動⇒処理実行⇒ファイル保存⇒ファイル閉じる⇒再度ファイル開く⇒実行。
このように一旦ファイルを閉じると問題がないことから、ファイル起動後2回目以降で問題が発生していると思われます。
オブジェクトの解放を疑い、調べてみましたが解決に至れず困っています。
何卒、解決策をご教示ください。

Sub generateCSV()
Dim sname As String
Dim sheet As Worksheet
Dim rng As Range
Dim lastRow As Long
Dim path As String
Dim fn As String

path = ThisWorkbook.path
sname = ActiveSheet.Name
Set sheet = ThisWorkbook.Worksheets(sname)
lastRow = 100

Set rng = sheet.Range(sheet.Cells(15, 1), sheet.Cells(lastRow, 10))

rng.Copy Destination:=Worksheets.Add.Range("A1")

ActiveSheet.Move

fn = path & "\" & Format(Date, "yyyymmdd") & " sales order data.csv"

Application.DisplayAlerts = False
If isFile(fn) Then Kill fn

ActiveWorkbook.SaveAs Filename:=fn, FileFormat:=xlCSV
ActiveWorkbook.Close savechanges:=False

Application.DisplayAlerts = True
sheet.Range("J9").Value = fn

Set sheet = Nothing
Set rng = Nothing

End Sub

Comments

[meg_]

・コードは「コードの挿入｣で記入してください。
・2回目実行時に1行ずつ実行して問題の処理を特定してはどうでしょうか？

[pfkbn172]

Teratailの使い方が分かっておらずすいません、またご指摘ありがとうございます。

2回目実行時、処理が止まる行は
ActiveWorkbook.SaveAs Filename:=fn, FileFormat:=xlCSV
です。

[sousuke]

ワークブックのパスは共有フォルダとかじゃない？

[pfkbn172]

保存場所はローカルのデスクトップです。

Answer 1

解決に至りましたので共有します。
関係のない行を削除していき問題箇所だけ残したコードが以下です。

問題のコード

Sub test()
  Dim ws As Worksheet
  Dim wb As Workbook
  Set ws = ThisWorkbook.Worksheets.Add
  ws.Move
  Set wb = ActiveWorkbook
  wb.SaveAs ThisWorkbook.path & "\test.csv"
  wb.Close
End Sub

修正済みのコード

Sub test2()
  Dim ws As Worksheet
  Dim wb As Workbook
  Set ws = ThisWorkbook.Worksheets.Add
  ws.Copy
  Set wb = ActiveWorkbook
  wb.SaveAs ThisWorkbook.path & "\test.csv"
  wb.Close
End Sub

4行目ws.moveをws.copyにするとエラーが発生しなくなりました。
（その後wsを削除することで目的の処理を実現できました。）
worksheet.moveを実行後に保存、初回実行時は発生せず2回目でエラーになります。

こちらで助言を下さった方の環境では再現されず、似た環境ですが別PCでは再現されたので環境も関連する可能性があります。

こちらで質問していなければ切り分けのヒントが得られず解決に至れなかったと思います。
ご助言下さった皆様本当にありがとうございました。

Answer 2

からのブックで試しましたが、再現しませんね。

ActiveSheet
ActiveWorkbook
など不確定要素を含むので出来るだけ使わないように。
あと、ファイルの削除も特にしなくても上書き保存したらいいと思います。

オブジェクト変数の解放は、
End subでされる「はず」なので、書いても書かなくてもいいかも。
不都合があるときには書かないといけないですが。

Sub test()
    Dim wsNew As Worksheet
    Dim mySheet As Worksheet
    Dim n As String
    
    With ThisWorkbook.Worksheets
        Set wsNew = .Add
        Set mySheet = .Item("Sheet1")
    End With
    mySheet.Range("A15").Resize(100, 10).Copy wsNew.Cells(1)
    wsNew.Move
    
    n = ThisWorkbook.path & "\" & Format(Date, "YYYYMMDD") & ".csv"
    With Workbooks(Workbooks.Count)
        Application.DisplayAlerts = False
        .SaveAs n
        Application.DisplayAlerts = True
        .Close False
    End With
    mySheet.Range("J9").Value = n
End Sub

Comments

[pfkbn172]

コードまで書いて試して頂きありがとうございます。
書いて頂いたテストコードを私の環境で実行したところ、同じようにフリーズしました。
2回目実行時に処理が止まる行は
.SaveAs n
です。
初回時は正常に動作します。

環境によるものかもしれませんので、遅ればせながら私の環境について追記させて頂きます。
Microsoft office standard 2016
Windows 10 Pro
VBA 16.0

[mattuwan]

>2回目実行時に処理が止まる行は
>.SaveAs n
>です。
>初回時は正常に動作します。
ならば、手動でやってみては？
文字で命令するか、操作で実行するかの違いで、
やることは同じです。

[pfkbn172]

手動で試してみました。
１．問題のブックを開く
２．メニューから新規ブックを開く
３．名前を付けて保存（ファイル名：20201027.csv、保存場所もデスクトップで同じです）
４．正常に保存に成功
1～４の操作は問題なく正常に完了、続けて2～４を実行しました。
結果は問題なしです、、、

PCの再起動なども試しましたが症状は変わりません、、、

[mattuwan]

1回目はマクロで、
2回目は手動でやってみました？

あと、
Application.DisplayAlerts = False
をコメントアウトし、
.SaveAs n
の行にブレークポイントを置いて、
実行し、止まったところで、Ｆ８キー押下で、
.SaveAs n
を実行したらどうなりますか？

[mattuwan]

あと、シート上には数式とか、複雑な書式設定とか、
なにか変わったことやってますか？
こちらでは再現できないので、、、、、、再現できる状況がわかれば。。。

[pfkbn172]

1回目はマクロで、
2回目は手動でやってみました？
↑
こちらを試したところ手動操作でも再現しました！
１．問題のファイルを開く
２．処理を実行（1度目）
３．正常に保存
ーーー以降、手動操作ーーー
４．問題のファイルから新規ブックを開く
５．名前を付けて保存（同名を手動で入力）
６．フリーズ

Application.DisplayAlerts = False
をコメントアウトし、
.SaveAs n
の行にブレークポイントを置いて、
実行し、止まったところで、Ｆ８キー押下で、
.SaveAs n
を実行したらどうなりますか？
↑
F8で1行ずつ処理を実行
1度目の処理の実行～2度目の。SaveAs nまでは問題ありません。
SaveAs nの行でF8押下⇒プログレスバーが延々と回り続ける
となりExcel自体が操作不能になります。（自動的に落ちて再起動になるパターンもあります。）
アラート等は何も出ません。

先程書いて頂いたTestコードは新規ファイルに入れましたので、数式や書式設定の可能性は否定できたと思います。

[mattuwan]

Moveで不具合ですかぁ。。。
シートを連続でコピーするとよくそうなりますが。。。。

Answer 3

あてずっぽうだけどkill周りじゃない？
これでエラー出るか調べてみたら？

fn = path & "\" & Format(Date, "yyyymmddhhnnss") & " sales order data.csv" 

と思ったけど閉じて開いたらオッケーなのね

ここら辺もみたほうがいいかも

ActiveWorkbook.SaveAs Filename:=fn, FileFormat:=xlCSV
ActiveWorkbook.Close savechanges:=False 

今思えばワークブッククローズしてもう一回実行っていうのが
どういう行為なのかちょっとわからない…見当違いだったら申し訳ない

Comments

[pfkbn172]

ご意見ありがとうございます。
ご指摘のとおり、問題の箇所は
ActiveWorkbook.SaveAs Filename:=fn, FileFormat:=xlCSV
でした。

[pfkbn172]

「ワークブッククローズしてもう一回実行」
↑
この動作は検証のために行ったものです。

想定利用シーンではファイルオープン後、続けてVBAを実行することもあるので、
ファイルを開く⇒VBA実行（1回目）⇒VBA実行（2回目）⇒強制終了
この状態を回避したいと思っております。