i have excel file stored in sharepont. Excel has power query connection to remote db. My goal is to setup automatic refresh of query connction in order to have up to date date in excel.
Googling for solution gave me an idea of creating vbs script and setting up regular task with task scheduler. I found following code here
Set xl = CreateObject("Excel.Application") set wb = xl.WorkBooks.open("https://kalmstromdemo2.sharepoint.com/ sites/IT/Shared%20Documents/ITTicketsSummary.xlsx") xl.DisplayAlerts = False WScript.Sleep 1000 wb.RefreshAll wb.Save wb.Close xl.Quit
But file was not refreshed in my case. I supposed that i need to checkout file first, cause it required by my sharepoint.
Found this example for checking out in docs:
filePath = "https://test.com/sites/STS020170405181504/NFVF/NFVF%20root%20causes.xlsx" Set xl = CreateObject("Excel.Application") set wb = xl.WorkBooks.open(filePath) If xl.WorkBooks.CanCheckOut(filePath) = True Then xl.WorkBooks.CheckOut filePath MsgBox "CheckOut success!" Else MsgBox "Unable to check out this document at this time." End If
But got message "Unable to check out this document at this time." Please suggest steps to debug connection issue, or any alternatives to achieve my goal.
Also while looking for solution, found several similar cases implemented with powershell script. As i'm very new in sharepoint related scripting would be glad to hear if powershell is probably better choise for my task.