Hi Guys,
I am trying to export a sheet created on run time to a new workbook (replace if already exist on location). Everything works like charm on the first run but when the macro is executed second time, it crashes excel without displaying any error.
I have tried to walk through using F8 but no error is displayed. I have tried removing the existing file at the location (so that it does not have to overwrite it), export to a different location but nothing works. Every time it crashes on second run.
I am using Windows 7 64 bit, Service Pack 1 and Excel 2016.
Please help in figuring this weird glitch. Thanks in advance.
Here is the Export sub. Excel crashes on .SaveAs line on second run.
I am trying to export a sheet created on run time to a new workbook (replace if already exist on location). Everything works like charm on the first run but when the macro is executed second time, it crashes excel without displaying any error.
I have tried to walk through using F8 but no error is displayed. I have tried removing the existing file at the location (so that it does not have to overwrite it), export to a different location but nothing works. Every time it crashes on second run.
I am using Windows 7 64 bit, Service Pack 1 and Excel 2016.
Please help in figuring this weird glitch. Thanks in advance.
Here is the Export sub. Excel crashes on .SaveAs line on second run.
Code:
Private Sub ExportFile()
Dim wSht As Worksheet
Dim newBook As Workbook
Dim newfileFormatNo As Long
On Error Resume Next
saveInFldr = CreateObject("WScript.Shell").SpecialFolders("Desktop") & Application.PathSeparator
strPath = vbNullString
saveInFldr = saveInFldr & "Reports"
MkDir saveInFldr
strPath = saveInFldr & Application.PathSeparator & "Dashboard"
Select Case SupplierID 'This is picked from Sheet 1 - cell A1 of original workbook
Case 1
For Each wSht In wb.Worksheets
If wSht.Name <> "Control Panel" And wSht.Name Like "t_*" = False Then
If newBook Is Nothing Then
wSht.Move
Set newBook = ActiveWorkbook
Else
wSht.Move after:=newBook.Sheets(newBook.Sheets.Count)
End If
End If
Next wSht
End Select
With newBook
newfileExt = ".xlsb"
newfileFormatNo = 50 ' File format number for Excel Binary format
.Sheets(1).Activate
.SaveAs strPath & newfileExt, FileFormat:=newfileFormatNo '<< Excel crash here on second run
.Close SaveChanges:=False
End With
End Sub