• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Worksheet Save As to new workbook crashes Excel on second run

Ajesh

Active Member
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.

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
 

Hui

Excel Ninja
Staff member
I'm not sure SupplierID or wb are defined or set to any values
Hence the second time around Excel is confused about what it is
 

Ajesh

Active Member
I'm not sure SupplierID or wb are defined or set to any values
Hence the second time around Excel is confused about what it is
Thanks Hui for your reply.

Supplier ID and wb are declared at Module level as they are used in other Subs as well and are assigned/set in initial Subs.

Code:
Dim wb As Workbook
Dim SupplierID As Long


Set wb = ThisWorkbook ' It is being set at the very beginning of Macro activities.
SupplierID = CtrlSht.Range("$C$8").Value ' It gets value from Cell which remains unchanged during the whole run.
Thanks/Ajesh
 

Ajesh

Active Member
Can you upload a sample file so we can see what else is going on
Thanks Hui.

Please see enclosed the required sample files.

SUP Dashboard Macro.xlsm >> As evident, is the macro file.
Resource Report.xlsb >> This is the file from which the macro pulls the source data.

I am still in initial phase of developing the macro so you may find it a little bit crude. When finished, I am targeting to cull multiple supplier data from multiple source files and consolidating it as a dashboard but yes it is not a matter of concern as of now.

Thanks again for all your help.
 

Attachments

Hui

Excel Ninja
Staff member
Can you please explain the issues as I see no errors

What happens ?
What is meant to happen ?
 

Ajesh

Active Member
Can you please explain the issues as I see no errors

What happens ?
What is meant to happen ?
The code runs first time with no errors at all but if it is executed second time excel hangs and crash at below line in ExportFile() sub.
Code:
.SaveAs strPath & newfileExt, FileFormat:=newfileFormatNo
thanks
 

Hui

Excel Ninja
Staff member
So you are trying to overwrite the existing file and it crashes
is that correct ?
 

Ajesh

Active Member
So you are trying to overwrite the existing file and it crashes
is that correct ?
It crashes anyway; overwrite or not. I have tried removing the first exported file from the location before second run so that it does not have to overwrite but it still crashes. I have even tried to export the second file to a new location but no avail.

thanks/Ajesh
 

vletm

Excel Ninja
Ajesh
Few questions ...
Is there any reason of next line?
Code:
IsFile = ((GetAttr(strPath & newfileExt) And vbDirectory) <> vbDirectory)
Anyway, have You verified that will always give valid answer (True or False)?
If ... that would make 'error' then ... there will be challenges!
( newfileFormatNo ... are You 100% sure that, it will be always 50? )
 

Ajesh

Active Member
Ajesh
Few questions ...
Is there any reason of next line?
Code:
IsFile = ((GetAttr(strPath & newfileExt) And vbDirectory) <> vbDirectory)
Anyway, have You verified that will always give valid answer (True or False)?
If ... that would make 'error' then ... there will be challenges!
( newfileFormatNo ... are You 100% sure that, it will be always 50? )
Hi vletm,

In that line (IsFile = ...) It was something I was trying to capture the file status while debugging but did not help as excel crashes anyways. You can remove it as it does not affect anywhere.

And yes, newFileFormatNo = 50 is hardcoded (for .xlsb format output) it will always be same.

thanks for your time.
 

vletm

Excel Ninja
Ajesh
IsFile ...
for me, is it there or not no matter,
because I cannot use those macros!
There're many features which won't work with me.
>> ... and You skipped my question ...
... as I wrote, .. if that line makes error, then ... not good!

newFileFormatNo = 50 ...
Of course, it could be fixed ... but eg for me, it cannot be 51, it won't work!
 

Ajesh

Active Member
Ajesh
IsFile ...
for me, is it there or not no matter,
because I cannot use those macros!
There're many features which won't work with me.
>> ... and You skipped my question ...
... as I wrote, .. if that line makes error, then ... not good!

newFileFormatNo = 50 ...
Of course, it could be fixed ... but eg for me, it cannot be 51, it won't work!
IsFile - As i mentioned, it's no use in the code. It was just something I was trying and forgot to remove in the sample file I prepared to upload here.

newFileFormatNo = 50: It is an Excel constant so it will be same for Excel 2007-Excel 2016. (For Mac it will be different but my target users are on Windows so it will always be 50)

51 = xlOpenXMLWorkbook (without macro's in 2007-2016, xlsx)
52 = xlOpenXMLWorkbookMacroEnabled (with or without macro's in 2007-2016, xlsm)
50 = xlExcel12 (Excel Binary Workbook in 2007-2016 with or without macro's, xlsb)
56 = xlExcel8 (97-2003 format in Excel 2007-2016, xls)

Source: https://www.rondebruin.nl/win/s5/win001.htm

Thanks/Ajesh
 

vletm

Excel Ninja
IsFile - As i mentioned ...
Yes, just remember...
All codes matters! Especially, if all codes cannot run.
Those 'somethings' would make interesting affects.
If I cannot test code, then this would be only guessing ... not for me.

newFileFormatNo = 50 ...
Okay... then this won't work here!
 

Ajesh

Active Member
IsFile - As i mentioned ...
Yes, just remember...
All codes matters! Especially, if all codes cannot run.
Those 'somethings' would make interesting affects.
If I cannot test code, then this would be only guessing ... not for me.

newFileFormatNo = 50 ...
Okay... then this won't work here!
If this is something which is holding you back, please remove it/amend to export in normal .xlsx format or the one you are comfortable with. I am sure the issue I am facing here is not related to file format so I will incorporate it later at my end. I just need the code to smoothly save the file on every run without crashing. I am open to all/any changes that are required to overcome the issue.

thanks/Ajesh
 

vletm

Excel Ninja
I've use xlsb as long time as it has been possible to use.

If newFileFormatNo is 'not useful' for all cases, so far,
I've noticed that it prevents to eg SaveAs file - not make more affect.

You wrote/named ( #10 Reply ) line which hangs and crash - okay?
If something near that, makes error
then after error ... something no work as smooth as possible.

If I try to 'clean'/modify lines,
which would make Your rest of codes run smoother for me,
it would be different code.
 

Ajesh

Active Member
I've use xlsb as long time as it has been possible to use.

If newFileFormatNo is 'not useful' for all cases, so far,
I've noticed that it prevents to eg SaveAs file - not make more affect.

You wrote/named ( #10 Reply ) line which hangs and crash - okay?
If something near that, makes error
then after error ... something no work as smooth as possible.

If I try to 'clean'/modify lines,
which would make Your rest of codes run smoother for me,
it would be different code.
Thanks for your time.

That would be fine with me. I have already provided the 'Source' and 'Macro' files (#6 Reply) which will give you complete run from start to end. If it requires 'cleaning', 'tweaking' or overhauling, I am open to it. I am all out of ideas on this one hence anything will be a great help.
 

vletm

Excel Ninja
Ajesh
I modified that file as less as I needed ...
It runs without crashes!
That code would give still some error-codes ... not tried to clean away!
You would try to compare to Your original code and
find, what will make Your code ... work other ways.
As I've written ... have You verified ... eg Your variables?
If so, then try to do that again.
Ps. There is also Your 'constant 50' ... for me, it need to be 50+1!
 

Attachments

Ajesh

Active Member
Ajesh
I modified that file as less as I needed ...
It runs without crashes!
That code would give still some error-codes ... not tried to clean away!
You would try to compare to Your original code and
find, what will make Your code ... work other ways.
As I've written ... have You verified ... eg Your variables?
If so, then try to do that again.
Ps. There is also Your 'constant 50' ... for me, it need to be 50+1!
Many thanks @vletm . I will try it on and let you know.

Thanks again for all your time and effort.
 
Top