none
Getting issue while dumping data from Add-In to Excel 2016(Build 16.9029) RRS feed

  • Question

  • Hi,

    We have an Excel Add-in that fetches data from the server and dumps into excel. It was working fine until the Excel's new Build release (16.9029.*). After this update, All data is getting fetched correctly from the server but can't be dumped into excel sheet and It shows Error that "Microsoft Excel has stopped working"

    Expecting your positive reply.

    Saturday, March 10, 2018 1:26 PM

All replies

  • Hi Chiranjiv Pathak,

    Is that Excel Add in was developed by you or Is It a third party Add in?

    If It was developed by you then please try to provide more detail and provide a sample code to reproduce the issue.

    We will try to make a test with it on our side and try to check the issue.

    If It is third party Add in then you may try to contact the developer of that Add in.

    At present to avoid this issue, You can uninstall the current update and move to last working update.

    From only description, It is hard for us to find the issue in your code.

    So provide further detailed steps with sample code.

    Thanks for your understanding.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, March 12, 2018 1:35 AM
    Moderator
  • Hi Deepak,

    Thank you for looking into it.

    Yes, it was developed by you but for some security reasons, I am unable to share the code of Add-in. I am trying to fix it for my clients.

    I can give you some more information which I found when I tried to debug the issue. I found that after "QueryTables.BeforeRefresh" event occurs, Excel suddenly crashed & shows Error that "Microsoft Excel has stopped working".  

    Regards,

    Monday, March 12, 2018 9:53 AM
  • Hi Chiranjiv Pathak,

    You had mentioned that,"after "QueryTables.BeforeRefresh" event occurs, Excel suddenly crashed & shows Error that "Microsoft Excel has stopped working"."

    For testing purpose, You can try to comment the code in this event and check whether error get remove or not.

    If error get remove then there is something wrong with the code.

    Did you check the code reside in this event? What that code do?

    We don't know that what you are doing in this event so it is not possible for us to find the issue in it without checking the code.

    If possible then you can post the sample code.

    So that we can check and find the cause for the issue.

    Thanks for your understanding.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, March 13, 2018 12:46 AM
    Moderator
  • Hi Deepak,

    Thank you for looking into it.

    We have commented that code in that event but the issue still persists.

    That code will responsible to print data blocks in the excel sheet. Here is the sample code which we can provide to you. 

    Explanation of the code:

    We have some logic for our AddIn which fetches data from the server & print it into the Excel Sheet. 

    Once the data fetched it goes to the "QueryTable" function & in the end we have used Refresh method of Excel which executes the "BeforeRefresh" Event & after executing this method; Excel stopped working.

    We have put the debug point in that method for testing which executed in the end. 

    'Some logic here for our add-in
    Public Sub createQueryTable(URL As String, dest As String)
        Dim oBk As Workbook
        Dim x As New clsQueryTable
        Dim oQT As QueryTable
        Dim i As Integer
        Dim strFileName As String
        Dim posturl, postdata As String
        Dim instrstart, instrend As Integer
        
        If Not Mid(URL, Len(URL) - 12, Len(URL)) = "&format=.html" Then
        strFileName = URL & "&format=.html"
        Else
        strFileName = URL
        End If
    
    On Error Resume Next
        ActiveSheet.Range(dest).QueryTable.ResultRange.ClearContents
        ActiveSheet.Range(dest).QueryTable.Delete
    On Error GoTo errorhandler 
    With ActiveSheet
            Set oQT = .QueryTables.Add( _
                        Connection:="URL;" & strFileName, _
                        Destination:=Range(dest))
        End With
        Dim flagcheck As Boolean
        flagcheck = False
        With oQT
            
            'State that we're selecting a specific table
            .WebSelectionType = xlSpecifiedTables
            .RefreshStyle = xlOverwriteCells
            .EnableRefresh = True
            
            'Import the 1st table on the page
            .WebTables = "1"
            
            
            'Ignore the web page's formatting
            .WebFormatting = xlWebFormattingAll
            
            'Do not try to recognize dates
            .WebDisableDateRecognition = True
            
            'Don't automatically refresh the query each time the file is opened
            .RefreshOnFileOpen = False
            
            'Waiting for the query to complete before continuing
            .BackgroundQuery = True
            
            'Save the query data with the workbook
            .SaveData = True
        
            'Adjust column widths to autofit new data
            .AdjustColumnWidth = True
            .PreserveFormatting = True
        End With
        'Perform the query, waiting for it to complete
        Set x.qt = oQT
        Set outputCell = Range(dest)
        
        x.qt.Refresh BackgroundQuery:=False 'From this it calls qt_BeforeRefresh. 
        Set x = Nothing
        Exit Sub
       
    errorhandler:
    'On Error Resume Next
        If Err.Number = 1004 Then
          MsgBox "URL length exceeds the limit. Remove " & Len(x.qt.Connection) - 222 & " from URL"
          Err.Clear
          Else
          MsgBox Err.Number & vbCrLf & Err.Description
        End If
        Set x = Nothing
    End Sub
    
    Private Sub qt_BeforeRefresh(Cancel As Boolean)
        Debug.Print "hiii" 'It is printed Succesfully
    End Sub 'After this point of debug Excel Crashed So further execution also stops.

    Hope this will help you to understand the flow.

    Thanks & Regards,

    Thursday, March 15, 2018 9:36 AM
  • Hi Chiranjiv Pathak,

    I try to make a test with code and find that it is not generating any error, data get fetch successfully but it is also not executing the qt_BeforeRefresh event.

    I will again try to make a test try to inform you about the result.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, March 16, 2018 9:47 AM
    Moderator
  • Hi Deepak,

    Have you found anything further on this?

    Regards

    Tuesday, March 20, 2018 8:54 AM
  • Hi Chiranjiv Pathak,

    I again try to make a test and find that my Excel is not getting crashed.

    Can you inform us , Are you executing any important code in Before_Refresh event that can affect the execution?

    What if you remove the Before_Refresh event? Are you getting the same issue or it does not occur?

    Please test this on your side and let me know about the result.

    For narrow down the issue, I suggest you to execute the same code on any other machine.

    So that we can decide that whether the issue is machine specific or not.

    Regards

    Deepak 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, March 21, 2018 8:55 AM
    Moderator
  • Hi Deepak,

    Thank you for sharing outcomes

    We've debugged lot into the code and found that there is Request URL length issue in newer release(16.9029.*). 
    Earlier it was fetching up to 1800 characters in the request URL but now it's only 250 ( as per our testing). Request URL above then this character causes issues & stopped the execution of Excel.

    We haven't found any documentation for the newer build of Excel. Do you have any idea about this? Or can you please try the request URL more than 250 characters?

    Any help would be much appreciated. We are stuck at this point.

    Thanks & Regards,

    Chiranjiv

    Friday, March 23, 2018 2:31 PM
  • Hi,

    We have an Excel Add-in (created by us) that fetches data from the server and dumps into excel. It was working fine until the Excel's new Build release (16.9029.*). After this update, All data is getting fetched correctly from the server but can't be dumped into excel sheet and It shows Error that "Microsoft Excel has stopped working"

    When I tried to debug the issue. I found that after "QueryTables.BeforeRefresh" event occurs, Excel suddenly crashed & shows Error that "Microsoft Excel has stopped working".  

    Here is the sample code which we can provide to you. 

    Explanation of the code:

    We have some logic for our AddIn which fetches data from the server & print it into the Excel Sheet. 

    Once the data fetched it goes to the "QueryTable" function & in the end we have used Refresh method of Excel which executes the "BeforeRefresh" Event & after executing this method; Excel stopped working.

    We have put the debug point in that method for testing which executed in the end.

    'Some logic here for our add-in Webquery (URL more than 1000 character)
    Public Sub createQueryTable(URL As String, dest As String)
        Dim oBk As Workbook
        Dim x As New clsQueryTable
        Dim oQT As QueryTable
        Dim i As Integer
        Dim strFileName As String
        Dim posturl, postdata As String
        Dim instrstart, instrend As Integer
        
        If Not Mid(URL, Len(URL) - 12, Len(URL)) = "&format=.html" Then
        strFileName = URL & "&format=.html"
        Else
        strFileName = URL
        End If
    
    On Error Resume Next
        ActiveSheet.Range(dest).QueryTable.ResultRange.ClearContents
        ActiveSheet.Range(dest).QueryTable.Delete
    On Error GoTo errorhandler 
    With ActiveSheet
            Set oQT = .QueryTables.Add( _
                        Connection:="URL;" & strFileName, _
                        Destination:=Range(dest))
        End With
        Dim flagcheck As Boolean
        flagcheck = False
        With oQT
            
            'State that we're selecting a specific table
            .WebSelectionType = xlSpecifiedTables
            .RefreshStyle = xlOverwriteCells
            .EnableRefresh = True
            
            'Import the 1st table on the page
            .WebTables = "1"
            
            
            'Ignore the web page's formatting
            .WebFormatting = xlWebFormattingAll
            
            'Do not try to recognize dates
            .WebDisableDateRecognition = True
            
            'Don't automatically refresh the query each time the file is opened
            .RefreshOnFileOpen = False
            
            'Waiting for the query to complete before continuing
            .BackgroundQuery = True
            
            'Save the query data with the workbook
            .SaveData = True
        
            'Adjust column widths to autofit new data
            .AdjustColumnWidth = True
            .PreserveFormatting = True
        End With
        'Perform the query, waiting for it to complete
        Set x.qt = oQT
        Set outputCell = Range(dest)
        
        x.qt.Refresh BackgroundQuery:=False 'From this it calls qt_BeforeRefresh. 
        Set x = Nothing
        Exit Sub
       
    errorhandler:
    'On Error Resume Next
        If Err.Number = 1004 Then
          MsgBox "URL length exceeds the limit. Remove " & Len(x.qt.Connection) - 222 & " from URL"
          Err.Clear
          Else
          MsgBox Err.Number & vbCrLf & Err.Description
        End If
        Set x = Nothing
    End Sub
    
    Private Sub qt_BeforeRefresh(Cancel As Boolean)
        Debug.Print "hiii" 'It is printed Succesfully
    End Sub 'After this point of debug Excel Crashed So further execution also stops.

    One more thing we found in that, there might be an issue with Request URL length in newer release(16.9029.*). 

    Earlier it was fetching up to 1800 characters in the request URL (web query) but now it's only 250 ( as per our testing). Request URL above then this character causes issues & stopped the execution of Excel.

    We haven't found any documentation for the newer build of Excel. Does anyone have any idea about this? 

    Any help would be much appreciated. We are stuck at this point & our clients are also facing problems because of this.

    Thanks & Regards,



    Saturday, March 24, 2018 10:29 AM
  • I'm afraid your issue is more related with MSDN forum (software development).

    Ashidacchi -- http://hokusosha.com/

    Saturday, March 24, 2018 10:28 PM
  • Hi Chiranjiv Pathak,

    I try to make a test with the long URL below. I am not available with any other long URL then this.

    http://chart.apis.google.com/chart?chs=500x500&chma=0,0,100,100&cht=p&chco=FF0000%2CFFFF00%7CFF8000%2C00FF00%7C00FF00%2C0000FF&chd=t%3A122%2C42%2C17%2C10%2C8%2C7%2C7%2C7%2C7%2C6%2C6%2C6%2C6%2C5%2C5&chl=122%7C42%7C17%7C10%7C8%7C7%7C7%7C7%7C7%7C6%7C6%7C6%7C6%7C5%7C5&chdl=android%7Cjava%7Cstack-trace%7Cbroadcastreceiver%7Candroid-ndk%7Cuser-agent%7Candroid-webview%7Cwebview%7Cbackground%7Cmultithreading%7Candroid-source%7Csms%7Cadb%7Csollections%7Cactivity|Chart#

    Code:

    Function GetURL(cell As Range, _
    Optional default_value As Variant)
    
    If (cell.Range("A1").Hyperlinks.Count <> 1) Then
    GetURL = default_value
    Else
    GetURL = cell.Range("A1").Hyperlinks(1).Address & "#" & cell.Range("A1").Hyperlinks(1).SubAddress
    End If
    End Function
    
    Sub demo()
     
     ActiveSheet.Range("A2") = GetURL(ActiveSheet.Range("A5"), "demo")
    End Sub
    

    Output:

    It did not gave me any error.

    If there is any specific way to request URL you are using in your code then let us know about that.

    We will again try to make a test with it to check the result.

    As per MSDN Documentation, No limits are mentioned for the URL Length.

    Excel specifications and limits

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, March 26, 2018 1:53 AM
    Moderator
  • Hi,

    This is the forum to discuss questions and feedback for Microsoft Excel features, I'll move your question to the MSDN forum for Excel

    https://social.msdn.microsoft.com/Forums/en-US/home?forum=exceldev

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.

    Regards,

    Emi


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnsf@microsoft.com.


    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Teams.

    Monday, March 26, 2018 2:46 AM
  • Hi Deepak,

    We are not trying to print the request URL. We actually, hit the URL (which is a web query) & print the response (data) from that URL to the Excel sheet.

    Let me share you our testing:

    1. Create one ".php" file having some data or just echo statement (eg. Hello world).

    2. Keep the file name more than 250 characters. (eg. bfkbkdbkbgbgjbrswwf.......php)

    3. Execute that file & print the echo statement in the excel cell.

    It does not work for us (stopped execution of excel). If we keep the file name below 250 characters then it is working.

    This is the best what we can explain.

    Hope this will help you to understand it.

    Thanks & Regards,

    P.S: Is there any way to contact you personally via email or anything? We need to solve this ASAP for our clients

    Monday, March 26, 2018 6:44 AM
  • Hi Chiranjiv Pathak,

    Actually , You need to give file names less then 218 characters long.

    This is the limitation of Excel from very long time.

    I am wondering that you had mentioned that you was fetching 1800 characters.

    The only solution is to keep file name small so that it does not generate any error.

    Reference:

    Error message when you open or save a file in Microsoft Excel: "Filename is not valid"

    If you think that this limitation should be remove from Excel in future then you can provide your feedback to the Excel User Voice using link below.

    Increase the 218 character filename length limit to open files in excel

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, March 27, 2018 9:43 AM
    Moderator