Be careful. This workbook contains one or more queries that might be unsafe

Question

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.

Answer 1

I resolved this pesky warning by doing the following:

1. Open Document in Microsoft Excel

2. Select Data > Get Data drop down > Query Options

3. Went to Security > changed Web Preview Warning Level to 'None'

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

Answer 2

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.

Answer 3

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.

Answer 4

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