Unanswered

Refresh Data Automatically every morning - VPN for connection

(0) Share

Report

Posted on by Community Power Pla...

Hi,

I have an excel automated report. In the next month most of the user will lose credentials to log onto the database, therefore they won't be able to refresh the report. Is there a way to set up the refresh automatically? In order to refresh the report a connection to database is required (vpn must be enabled).

I'm working on another solution as well. I want a database to export query to the csv and use that as a datasource, but the 1st option seems quicker and more approachable. The question is that even possible?

Categories:

General topics

I have the same question (0)

All responses (4)

Answers (0)

AWorley 414 Super User 2024 Season 1 on at

Like (1)

Report

Might be better to make a powershell script to open the file for 5 minutes and then close automatically. Then in the workbook do a VBScript to refresh the data. Then toss the powershell script on a task schedule to open the file.

VBA script for Excel:

Private Sub Workbook_Open()
Workbooks(ThisWorkbook.Name).RefreshAll
End Sub

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

But how do you overcome the fact that most of the users won't have access to database where the source data is? If a different user opens a file then it won't refresh as the credentials won't match the database. I am only user with access to the datasource.

Is it possible to make power automate connect somehow to vpn and refresh the report that way?

Was this reply helpful? Yes No
AWorley 414 Super User 2024 Season 1 on at

Like (1)

Report

(Sorry just saw this notification!)

Task scheduler has a credential field that you can use your credentials on. Typically its better to have users have read access to the ODBC connection.

Was this reply helpful? Yes No
A7DO 4 on at

Like (0)

Report

Use VB script to run Excel macro (youtube.com)

Something like this.

Was this reply helpful? Yes No