0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.