Excel web-query Unable to Connect

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

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! 

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, Robert

Hi, perhaps knowing the form might help, 52745887899988 

Robert

Thank you for additional information Robert.

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

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

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

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:

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!

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.

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 

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

Hi, a bit more info.

Robert

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:

Please uncheck all of these options 

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

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

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.