Automating Excel Files in SharePoint

[CCSlice]

Hi Mr. Excel Nation,
I know that there are automation tools like Power Automate and the like, but for the people whose organizations' budgets are too tight, has anyone automated files located in SharePoint using Office 365? I am thinking of approaching the list of files as an array first and based upon refresh specifications, perform the opening of the file; that is, Check-Out the file, refresh the file, and then Check-In the file and then save. I am looking for ideas at this moment and I have searched without any success. Thank you in advance for your suggestions.

 

[Micron]

Using older version, yes, 365 no. It was around 2015 so probably version 2010 or 2013. Shouldn't matter though as it's likely backwards compatible. My guess is that if you've searched and found nothing, it must be due to your search terms. I sure didn't know what I was doing at the time and couldn't have succeeded without Google!

What I did was read the CanCheckout property and if True, open the wb, use Automation (aka OLE Automation) to update underlying chart data and save and close it. IIRC, simply opening a wb that's stored on a SP server locks it so that other users can't open it. No idea if they had to set that up. The code was written in Access, which pushed the chart data into the wb into a dynamic named range. Perhaps that won't be needed by you. Here's a pic of the Access references - no longer sure which ones might be needed re: SP but I'm assuming Office and/or OLE Automation. Excel ref would be for running Excel vba inside of Access.

Here's where I still go for automation code:

EXCEL Import And Export

 

[Micron]

Correction: the code I was looking at was for dealing with a pdf file of the chart. I dug deeper into the related code and see that there is a Checkout method after all.

xlx.Workbooks.CheckOut filePathOrPathVariableGoesHere

and after code for pushing data and saving wb

xlw.CheckIn filePathOrPathVariableGoesHere

Don't know why I didn't use the syntax of the 2nd example both times but probably should have.

 

[CCSlice]

Correction: the code I was looking at was for dealing with a pdf file of the chart. I dug deeper into the related code and see that there is a Checkout method after all.

xlx.Workbooks.CheckOut filePathOrPathVariableGoesHere

and after code for pushing data and saving wb

xlw.CheckIn filePathOrPathVariableGoesHere

Don't know why I didn't use the syntax of the 2nd example both times but probably should have

 

[CCSlice]

Thanks Micron, this will help. I am just looking to loop through 40 files, open each, perform data refreshes on each, and save and then close the file. I could always start the code with the files to be refreshed as an array but it becomes tricky after that with the opening of each file, that is to open it so that can be edited and then perform the refresh. To do this is not difficult; however, how do I differentiate between a refresh that is complete and then troubleshoots if not done? This is probably splitting hairs, but I would like to timestamp the start of the data refreshing and then timestamp the end. Is there anything that can tell when a data refresh is complete?

 

[Micron]

Is there anything that can tell when a data refresh is complete?

There is a Timer function and a Time function. Not understanding what you want that for though. If your code executes a line that updates a file, that's a process that has to complete before the next line of code executes, which could be a split second or several minutes. Errors are easy to trap, silent failures not so much. If by "then troubleshoots if not done" you mean you want to record which update(s) failed and raised an error, your error handler could write to a log file and at the end, open that text file so it would be obvious something went wrong. At the start of each update you could set the value of a string that id's the update that's going to happen next, which would be what is written to the log file. You wouldn't use msgbox because that will halt everything.