Forum Discussion

Emalong94's avatar
Emalong94
Copper Contributor
Aug 01, 2023

Refresh queries on Excel web

Hello Everybody!


I have an Excel file in a SharePoint folder meant as a template for users that are required to run some tasks and update a modified version of the file in a dedicated folder.

To validate the template drop-down lists, the file gets data from another file in the same SharePoint folder which gets refreshed overnight through another integration.

The connection command is web.content and the query is set to get refreshed when opening the template file.

Here's the problem: because of licence limitations, final users don't have the desktop app of Excel, but only Excel for web, which do not support the web connector.

I am wondering if I have alternative solutions to refresh the template data based on the data source and making final users work on Excel for web at the same time.

Thank you!
Emanuele

 

BI & Data Analysis
excel
Macros and VBA

1 Reply

  • NikolinoDE's avatar
    NikolinoDE
    Platinum Contributor
    Sep 03, 2023

    Emalong94 

    You have run into a common challenge when dealing with Excel files stored in SharePoint and using data connections that rely on the Excel desktop features not supported in Excel for the web.

    Here are some alternative solutions you can consider to make the template work with Excel for the web:

    1. Use Power Query for SharePoint Lists: Instead of using web.content, consider using Power Query to connect to SharePoint lists. Power Query is more compatible with Excel for the web, and it can be used to refresh data from SharePoint. You can set up your data source as a SharePoint list, and users can access and interact with this data in Excel for the web.
    2. Scheduled Data Refresh: Since you mentioned that the source data gets refreshed overnight through another integration, you could schedule the data refresh directly in SharePoint or the data source itself (if supported). This way, the data is up-to-date when users open the template in Excel for the web, eliminating the need for them to refresh the data manually.
    3. Use Excel Online Add-Ins: You can explore Excel Online add-ins or Office Add-ins that provide additional functionality. Some of these add-ins allow you to pull data from external sources and work with it directly in Excel for the web. Depending on your specific requirements, you might find an add-in that suits your needs.
    4. SharePoint Workflow: If your organization uses SharePoint workflows, you can create a workflow that updates the template file when changes are made to the source data. This way, the template file remains updated without manual intervention.
    5. Custom Web Application: If your organization has development capabilities, you can consider building a custom web application that interacts with the SharePoint data and provides an interface for users to work with the template in a web browser. This application can handle data synchronization and user interactions.
    6. Hybrid Approach: If possible, you could adopt a hybrid approach where users perform data-related tasks in Excel desktop (if available to them) and then save the file back to SharePoint, which can be accessed and viewed in Excel for the web by other users.

    The choice of solution depends on your specific requirements, technical capabilities, and the tools available within your organization. Consider consulting with your IT department or SharePoint administrators for the best approach tailored to your environment.

    The text and the steps are the result of various AI's put together.

     

    My answers are voluntary and without guarantee!

     

    Hope this will help you.

    Was the answer useful? Mark them as helpful!

    This will help all forum participants.

Resources