0

I try to display data using Excel services in a web part. First of all, my Sharepoint version is Sharepoint 2013; In addition, all authentication processes are working properly and I can retrieve the data. My excel is set to refresh connection each time the page is opened. Where my problem starts is that when I open the page, I get a warning: "Be careful. This workbook contains one or more queries that might be unsafe". I have searched about it found some solutions from: https://docs.microsoft.com/en-us/sql/analysis-services/power-pivot-sharepoint/this-workbook-contains-one-or-more-queries-that-refresh-external-data?view=sql-analysis-services-2017 and from : http://dinushaonline.blogspot.com.tr/2016/01/disable-warning-query-and-refresh-data.html

  1. Creating a Trusted file location; I have done this, but did not solve my problem.

  2. To disable warnings ( "In External Data, clear the checkbox for Warn on data refresh." ). This solution removed the warning however it made a side effect. After disabling the warning, my Excel does not refresh data automatically when the page is opened. Now it refresh only when the user click to refresh manually. I would like to ask if there is any solution for that? Briefly, I want to refresh my data connection each time the page is opened, also do not want to get "Be careful. This workbook contains one or more queries that might be unsafe" error displaying to the user. User should see the most recent data without on the page load automatically without clicking anywhere.

    Thank you in advance.

0

It shouldn’t affect the automatically refresh data after untick “warning refresh enabled” option.

Whether there are other excel files with external data have this same issue?

Make sure you have configured refresh data when opening the file in excel file.

  • Yes any of the Excel files have the same issue. Also I have checked and made sure that my Excel file is configured as Refresh Connections when the page is load. I thought, maybe it is a security protection to protect users to run the Excel queries without accepting the risk. If I untick the "Warning Refresh Enabled" , the user does not see the warning. However, he does not get the recent data. He needs to right click, and click to refresh to get the most recent data. – vvvnn May 22 '18 at 8:43
0

Technically this is not a SharePoint question but an Excel question. Here is a solution.

Create a Visual Basic module called Workbook_Open()

Private Sub Workbook_Open()

'Refresh the data link to Sharepoint List View
ActiveWorkbook.RefreshAll
Range("A1").Select
ActiveWorkbook.Save

End Sub

Save the workbook as an Excel Macro-Enable Workbook. This will then run when you open the file.

  • Is this technically different than configuring my Excel connection to refresh when the page is load? – vvvnn May 22 '18 at 8:50
  • I'll be honest I don't think so, but it might fix the problem you are experiencing. Worth a try!? – unibod50 May 22 '18 at 10:34
0

Some of the files may contain unsupported diagrams or macros in that case it will pop up with the warning and it will be safe when we are using ok button also.

Refer the below URL for supported and unsupported scenarios. https://support.office.com/en-us/article/work-with-macros-in-excel-online-98784ad0-898c-43aa-a1da-4f0fb5014343

  • Thank you for answer, so it means Microsoft does this due to the safety and security reasons. As I understood , it is not possible to disable the prompted warning. – vvvnn Jun 7 '18 at 10:30
0

I resolved this pesky warning by doing the following:

  1. Open Document in Microsoft Excel
  2. Select Data > Get Data drop down > Query Options enter image description here

  3. Went to Security > changed Web Preview Warning Level to 'None' enter image description here

  4. I also updated Privacy to 'always ignore privacy level settings'

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.