How to automatically update data when opening the Excel file?

[Anonymous]

Hi - This is for Power Query in Excel with SQL DB connection. Is there a way to configure just so that the data is automatically updated everytime the user opens up Excel file? Also I have a senario where the report viewer doesn't have an access to SQL database and we can only allow Windows authentication to the database (SQL authentication is not allowed). Is it possible to configure impersonation or anything that would allow user to refresh data? Please advice.

Thanks!

[mahoneypat · Accepted solution]

You can write a macro with VBA that triggers on open with the command Refresh.All

That will refresh all queries in the workbook.

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[mahoneypat · Accepted solution]

You can write a macro with VBA that triggers on open with the command Refresh.All

That will refresh all queries in the workbook.

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Anonymous]

@mahoneypat Is there a way to do impersonation in excel macro? Thanks!

[mahoneypat · Accepted solution]

I am not aware of that, and would probably advise against it.  If you had a shared read only account to your database that anyone that would open your file had access to, that should work.

Regards,

Pat