SaveAs failing on 2nd run

namcap

New Member
Joined
May 30, 2013
Messages
4
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:

Code:
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
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

deanst

Board Regular
Joined
Nov 1, 2011
Messages
71
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

Well-known Member
Joined
Jul 3, 2012
Messages
6,374
Office Version
  1. 2019
Platform
  1. Windows
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.

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

New Member
Joined
May 30, 2013
Messages
4
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.

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

Board Regular
Joined
Nov 1, 2011
Messages
71

ADVERTISEMENT

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

Dean.
 

namcap

New Member
Joined
May 30, 2013
Messages
4
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:

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

New Member
Joined
May 30, 2013
Messages
4
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,491
Messages
5,596,477
Members
414,070
Latest member
DuncanLucas

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top