Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

How to automatically update data when opening the Excel file?

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 ...
Data Protection
Need Help
  • mahoneypat's avatar
    mahoneypat
    6 years ago

    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's avatar
    mahoneypat
    6 years ago

    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

     

mahoneypat's avatar
mahoneypat
Microsoft Employee
6 years ago

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's avatar
    Anonymous
    Not applicable
    6 years ago

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

    • mahoneypat's avatar
      mahoneypat
      Microsoft Employee
      6 years ago

      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