SaveAs failing on 2nd run

[namcap]

I have a Module that calls a sub that moves a sheet and saves the book as a txt file. When I run the code the first time, it works just fine. If I try to run it again without closing the book, excel crashes. I figure it has something to do with the way I am saving the content or moving the sheet, but I cannot seem to figure out why.

Here is the portion of the code it crashes on:

Sub output_to_txt()






Application.ScreenUpdating = False
Application.DisplayAlerts = False




Sheets(sheet_from).Select
Sheets(sheet_from).Move
ActiveWorkbook.SaveAs Filename:= _
    outputfpath & outfnameNX & ".txt", FileFormat:= _
    xlTextPrinter, CreateBackup:=False
ActiveWindow.Close


End Sub

Any help or guidance is greatly appreciated. If you need the rest of the code, I will be happy to post it as well.

Thanks in advance

 

[deanst]

Hi,

Could it be that after you run it once sheet_from is no longer in the workbook because you've moved it into a new workbook?

Hence running a second time throws an error because the sheet no longer exists?

Dean.

 

[dmt32]

As already mentioned, having moved the sheet from your workbook code will fail - you can manage this by adding some error handling in your code.

Sub output_to_txt()

    On Error GoTo myerror
    With Application
        .ScreenUpdating = False
        .DisplayAlerts = False
    End With

    Sheets(sheet_from).Move
    
    With ActiveWorkbook
        .SaveAs Filename:=outputfpath & outfnameNX & ".txt", _
                FileFormat:=xlTextPrinter, _
                CreateBackup:=False
        .Close False
    End With

myerror:
    With Application
        .ScreenUpdating = True
        .DisplayAlerts = True
    End With
    If Err > 0 Then MsgBox (Error(Err)), 16, "Error"
End Sub

Dave

 

[namcap]

As already mentioned, having moved the sheet from your workbook code will fail - you can manage this by adding some error handling in your code.

Sub output_to_txt()

    On Error GoTo myerror
    With Application
        .ScreenUpdating = False
        .DisplayAlerts = False
    End With

    Sheets(sheet_from).Move
    
    With ActiveWorkbook
        .SaveAs Filename:=outputfpath & outfnameNX & ".txt", _
                FileFormat:=xlTextPrinter, _
                CreateBackup:=False
        .Close False
    End With

myerror:
    With Application
        .ScreenUpdating = True
        .DisplayAlerts = True
    End With
    If Err > 0 Then MsgBox (Error(Err)), 16, "Error"
End Sub

Dave

Thanks for the tip.

I added the error handling, but excel still crashed. I'm still uncertain of what is causing it. On another book that I have, it will move multiple files while looping, but if I try to run it again once the code is finished, it also crashes excel. Today, I will try doing save copy as and then deleting the sheet to see if that will stop the crashes. Any other input is welcome. Thanks.

 

[deanst]

Hi - can you step through the code with F8 and tell us on which line exactly it fails?

Dean.

 

[namcap]

Hi - can you step through the code with F8 and tell us on which line exactly it fails?

Dean.

When I step through the code, it highlights this portion of the code:

.SaveAs Filename:=outputfpath & outfnameNX & ".txt", _
                FileFormat:=xlTextPrinter, _
                CreateBackup:=False

when I hit F8 to continue, excel crashes.

I can post the rest of the code if you would like to look at it (not sure if it would help or not).

 

[namcap]

So, as a workaround, instead of moving the sheet, and saving it as a txt file. I created a temp-sheet that never gets moved. It contains the same info as the one that is moved. I created a new workbook with the code, copied the data over, then saved that workbook as a txt file. That allows me to run the code as many times as I want. Still not sure why it fails on moving the sheet, but I had to create the workaround because I had to be able to run the code repeatedly without closing and opening the workbook. Thanks to all who tried. I still have a copy of the old code and I will continue to troubleshoot with any ideas that are thrown this way.

Thank you!