1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. 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...

  3. 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

Discussion in 'VBA Macros' started by Ajesh, Nov 1, 2018.

  1. Ajesh

    Ajesh Active Member

    Messages:
    153
    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 (vb):

    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
     
  2. Hui

    Hui Excel Ninja Staff Member

    Messages:
    11,668
    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
  3. Ajesh

    Ajesh Active Member

    Messages:
    153
    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 (vb):

    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
  4. Ajesh

    Ajesh Active Member

    Messages:
    153
    Any help guys on this?

    Thanks/Ajesh
  5. Hui

    Hui Excel Ninja Staff Member

    Messages:
    11,668
    Can you upload a sample file so we can see what else is going on
  6. Ajesh

    Ajesh Active Member

    Messages:
    153
    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.

    Attached Files:

  7. Ajesh

    Ajesh Active Member

    Messages:
    153
    Hi @Hui, Did you get a chance to look into it. Thanks
  8. Ajesh

    Ajesh Active Member

    Messages:
    153
    Ok thanks. Will await your response.

    BR/Ajesh
  9. Hui

    Hui Excel Ninja Staff Member

    Messages:
    11,668
    Can you please explain the issues as I see no errors

    What happens ?
    What is meant to happen ?
  10. Ajesh

    Ajesh Active Member

    Messages:
    153
    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 (vb):

    .SaveAs strPath & newfileExt, FileFormat:=newfileFormatNo
     
    thanks
  11. Hui

    Hui Excel Ninja Staff Member

    Messages:
    11,668
    So you are trying to overwrite the existing file and it crashes
    is that correct ?
    Ajesh likes this.
  12. Ajesh

    Ajesh Active Member

    Messages:
    153
    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
  13. Ajesh

    Ajesh Active Member

    Messages:
    153
    Any luck on this one guys?

    Thanks for your help.
  14. Ajesh

    Ajesh Active Member

    Messages:
    153
    @Hui, Sorry to chase but did you get a chance to look into it.

    Any help is appreciated.

    Thanks/Ajesh
  15. vletm

    vletm Excel Ninja

    Messages:
    4,415
    Ajesh
    Few questions ...
    Is there any reason of next line?
    Code (vb):

    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 likes this.
  16. Ajesh

    Ajesh Active Member

    Messages:
    153
    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.
  17. vletm

    vletm Excel Ninja

    Messages:
    4,415
    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 likes this.
  18. Ajesh

    Ajesh Active Member

    Messages:
    153
    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
  19. vletm

    vletm Excel Ninja

    Messages:
    4,415
    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 likes this.
  20. Ajesh

    Ajesh Active Member

    Messages:
    153
    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
  21. vletm

    vletm Excel Ninja

    Messages:
    4,415
    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.
  22. Ajesh

    Ajesh Active Member

    Messages:
    153
    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.
  23. vletm

    vletm Excel Ninja

    Messages:
    4,415
    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!

    Attached Files:

    Ajesh likes this.
  24. Ajesh

    Ajesh Active Member

    Messages:
    153
    Many thanks @vletm . I will try it on and let you know.

    Thanks again for all your time and effort.
  25. Ajesh

    Ajesh Active Member

    Messages:
    153
    Please consider this resolved. Thanks to @vletm for all the help.

Share This Page