Excel web-query Unable to Connect

  • Profile Image
    AgilityAssoc.Canada
    Asked on October 13, 2017 at 10:45 AM

    Hi,

    I am trying to create a custom report using Excel by using web-query to capture data from a grid report and I get an error message. 

    Details: "Web.Contents failed to get contents from 'https://www.jotform.com/table/72826043108048' (500): Internal Server Error"

    Is there some type of protection preventing this connection? 

    I do not want to create an Excel report using JotForm as it is not dynamic. 

    Thank you, Robert

  • Profile Image
    Nik_C
    Answered on October 13, 2017 at 11:35 AM

    Hello Robert, would you mind sharing the steps you did so we can recreate them and see what could be the problem?

    There might be a protection, but we will know more if we could test the same.

    We'll wait for your response.

    Thank you! 

  • Profile Image
    AgilityAssoc.Canada
    Answered on October 13, 2017 at 11:59 AM

    Hi Nic,

    OK very simple process to follow. I have used this on other site tables with great success.

    !. create a new Excel sheet, (I am using Excel 365)

    2. select DATA and From Web icon.

    3. Copy and paste the url into field.

    4. Excel attempts to make connection and fails with above statement.


    TNX, Robert1507910332Excel.png

  • Profile Image
    AgilityAssoc.Canada
    Answered on October 13, 2017 at 12:27 PM

    Hi, perhaps knowing the form might help, 52745887899988 

    Robert

  • Profile Image
    Nik_C
    Answered on October 13, 2017 at 01:03 PM

    Thank you for additional information Robert.

    I see what you're referring to, but, unfortunately, I don't have that option in my Office365.

    1507914189Screen Shot 2017-10-13 at 7.05

    You're referring to the online version of Office 365 right?

  • Profile Image
    AgilityAssoc.Canada
    Answered on October 13, 2017 at 01:27 PM

    I use both versions, and this feature is available in most excel versions. like 2010, 2013.

    Look here, Google search; Excel 2010 web query. There is info on 365 there also. You should be able to do it.

    Robert

  • Profile Image
    Nik_C
    Answered on October 13, 2017 at 01:46 PM

    Actually, for some reason, I wasn't able to see it on my Mac, but on Windows, I was able to.

    So I tested that and it worked with my table:

    1507916600test1234.gif

    I'm on office plus 2016 and I used HTML table listing report as well: https://eu.jotform.com/table/72855733953064

    Also, even with new submissions, data will appear, but you will have to Refresh it manually.

    Feel free to try with my table report and let us know how it worked.

    Thank you!

  • Profile Image
    AgilityAssoc.Canada
    Answered on October 13, 2017 at 03:07 PM

    Hi,

    Well very interesting I can see your table but not mine. I do notice a difference in the URL. You have //en.jotform.com/~ . I doubt that has any significance though. 

    I also notice a very different web-query window then I see in Excel, it looks like a web browser window.

     

    1507921545Excel.png

    There must be something were missing. Try connecting to mine.

    I even tried to change the report number in your query to mine and it fails.

    Robert 

  • Profile Image
    AgilityAssoc.Canada
    Answered on October 13, 2017 at 03:21 PM

    Here is an error message I get in Excel

    DataSource.Error: The remote server return an HTTP status code '500' when trying to access 'https://www.jotform.com/table/72826043108048'.

    Details:

        DataSourceKind=Web

        DataSourcePath=https://jotform.com/table/72826043108048

        Url=https://www.jotform.com/table/72826043108048

  • Profile Image
    AgilityAssoc.Canada
    Answered on October 13, 2017 at 03:49 PM

    Hi, a bit more info.

    1507924109Google Sheets.png

    Robert

  • Profile Image
    aubreybourke
    Answered on October 13, 2017 at 05:05 PM

    Its possible that your report is password protected. So you can try adding:

    ?{ID}={password}

    to your URL.


    Another possibility is you have restricted your submission data in your user profile settings:

    1507928708Microsoft Edge 13_10_2017 22_0

    Please uncheck all of these options 

  • Profile Image
    AgilityAssoc.Canada
    Answered on October 13, 2017 at 06:44 PM

    GREAT ... I had those two checked and now it works this is Great.....Also that password add-on is also very useful to me.

    Thank you very Much

    Best Regards, Robert

  • Profile Image
    AgilityAssoc.Canada
    Answered on October 14, 2017 at 10:37 PM

    Hi, 

    One more thing I notice, Excel query cannot read the Grid report because there is no table. To bad I like it better.

    Robert

  • Profile Image
    Nik_C
    Answered on October 15, 2017 at 03:22 AM

    Thank you for additional info.

    Well, yes, it might look better but it makes sense since there is no table as you noticed as well.

    So I'm afraid that the HTML Table Listing is the only option at this moment.