CODEWITHSUNDEEP
excelvba
TaKatie
TaKatie

Reputation: 13

Excel 2016/2013 crashes running SaveAs method 2 times

I'd like to create several new workbooks. The VBA code below runs fine with Excel 365 and 2010. BUT with Excel 2013 or 2016, it runs fine the first time (and create the files)... and on the second run, Excel crashes without any error message.

If I run it step by step, I see that it's the SaveAs line that causes the crash.

I tried to kill the file before saving, too. To use a timer...

I tried to repair Office, to rename a HKEY (Identities), I tried to run it on 2 different windows (7 or 10). Nothing helps :/

Sub ExtraireType()
Dim shVentes As Worksheet
Dim rngVentes As Range
Dim rngTypes As Range
Dim shNew As Worksheet
Dim wkbNew As Workbook
Dim strPath As String
Dim zaza As Range

Application.DisplayAlerts = False
Set shVentes = ThisWorkbook.Worksheets("Ventes")
Set rngVentes = shVentes.Range("A1").CurrentRegion
Set rngTypes = ThisWorkbook.Worksheets("Liste").Range("A2:A4")
strPath = ThisWorkbook.Path

For Each zaza In rngTypes
    rngVentes.AutoFilter
    rngVentes.AutoFilter field:=3, Criteria1:=zaza.Value
    rngVentes.Copy
    Set shNew = ThisWorkbook.Worksheets.Add
    shNew.Paste
    Application.CutCopyMode = False
    shNew.Move
    Set wkbNew = ActiveWorkbook
    wkbNew.SaveAs strPath & "\Type" & zaza.Value & Format(Date, "yyyymmdd")
    wkbNew.Close
    Set shNew = Nothing
    Set wkbNew = Nothing
Next zaza

Set rngVentes = Nothing
Set shVentes = Nothing
Set rngTypes = Nothing
Application.DisplayAlerts = False
End Sub

This code runs well with Excel 2010 or 2019/365. But I have to use it with 2013 or 2016 :(

What am I doing wrong? Thanks for any help !

Upvotes: 1

Views: 878

Answers (2)

TDCox
TDCox

Reputation: 16

I was having this problem as well and have found a workaround - use .SaveCopyAs instead.

In the below example, .SaveAs crashes Excel every second time if I've left the Excel spreadsheet open and deleted the resultant file, whilst .SaveCopyAs saves every time irrespective. The only difference between the two is that .SaveAs has more options for how to save whereas .SaveCopyAs's only option is the filename.

Private Sub SaveAsExcelFile(TempExcelFile As Workbook, _
     NewFullFileName as string, _
     Optional FileFormat As XlFileFormat = xlOpenXMLWorkbook, _
     Optional CreateBackup As Boolean = False)
'
' created & last edited 2020-03-06 by Timothy Daniel Cox
'
' For this example it is assumed the new file name is valid and in .xlsx format
'
    Dim NewFullFileName2 as string

    NewFullFileName2 = Replace(NewFullFileName, ".xlsx", "2.xlsx")
    Application.EnableEvents = False
    TempExcelFile.SaveCopyAs Filename:=NewFullFileName 'doesn't crash here on 2nd run
    TempExcelFile.SaveAs Filename:=NewFullFileName2, FileFormat:=FileFormat, _
                         CreateBackup:=False 'will crash here on 2nd run
    Application.EnableEvents = true
End Sub

I still think there is a bug in Excel regarding the .SaveAs however:

Upvotes: 0

Adrian Solarz
Adrian Solarz

Reputation: 1

The one of the reasons that your code crash (it crushed in my case, Excel 2016), might be because you didn't add file extension at the end of:
wkbNew.SaveAs strPath & "\Type" & zaza.Value & Format(Date, "yyyymmdd")
so it might be like:
wkbNew.SaveAs strPath & "\Type" & zaza.Value & Format(Date, "yyyymmdd") & ".xlsx"
Hope it helps.

Upvotes: 0

Related Questions