Refresh Microsoft Excel File with On Premises Data Connection on OneDrive Folder RRS feed

  • Question

  • Hi,

    I have an Microsoft Excel File (Excel 2019) stored on OneDrive.

    The Excel File has a Data Connection to an SQL Server Database.

    I stored the Excel File on OneDrive as some of the excel file users are not on the Network that the SQL Server is on.

    I want to refresh the excel spreadsheet on opening the file to get the latest data from the SQL database.

    However, unless the user is in the same network as the SQL Server Database, on refresh, an error message says that cannot get the data.

    Is there a way to make this work? without VBA etc

    Note: Remote users currently access the SQL Server Database Business Application via a VPN.

    I also know that if I use Power BI, and Gateway it would work, but the Company just wants Excel, and also the excel file is also a data input file.

    Thank you for any assistance.


    • Edited by Gerry RYAN Monday, August 31, 2020 4:57 AM
    Monday, August 31, 2020 4:49 AM