Script for Auto-Update of Excel Chart in SharePoint
ฝัง
- เผยแพร่เมื่อ 8 ก.ย. 2024
- Create a scheduled task that runs a script which refreshes an Excel chart based on SharePoint list data, so that all changes in the list are reflected in the chart: kalmstrom.com/T...
![ตีสิบเดย์ [FULL] | นางเอกหมอลำเงินล้าน "อุ๋งอิ๋ง เพชรบ้านแพง" & ไฮโซสาวสุดฮอต "ลิลลี่ เหงียน"](http://i.ytimg.com/vi/jPxQS_EgrJ4/mqdefault.jpg)
![[LIVE] MCHOICE & MINT AWARDS 2024 at TRUE ICON HALL, ICONSIAM](http://i.ytimg.com/vi/wMewUeT5nbY/mqdefault.jpg)
I love the script idea. My challenge (I think) is due to our company policy I can't get Excel out of read only mode. So I can see that the spreadsheet is pulling up, because I made it visible in the script, and it looks like it's doing the refresh all and I see the saving message but I noticed the spreadsheet never gets out of that read only mode. It only updates when I manually open and then click the button to edit. I've tried all kinds of tricks such as trying to change settings for SharePoint collaboration on the file, taking off read only on the file and library folder itself (which then seems to get set back to read only or won't allow me!) changing trust permissions on the file itself (Excel 2016 btw) with those settings under File -> Options -> Trust Center (both Protected View and Trusted Locations) and it seems like I get foiled every time! :-) So it feels like in short I'm hemmed in by our Corporate policies. :-) I did see the Pivot Explorer solution. Did you develop that? Anyway, hoping we can get that but in the mean time! ….
Sorry it didn't work out for you. It does sound like a company policy issue.
Yes, I did develop the Pivot Explorer solution.
You should also look at Power BI, that might be an option for you.
I have an excel sheet with data connected to a database. I have saved it on the SharePoint. I am using the script you shared to update the file. It's not working. Can you please help.
I would be delighted to help you. Please see the description page of my services:
www.kalmstrom.com/Services/default.htm
I am trying to get an Excel file that lives in SharePoint online to do this. It works fine until I configure the Task Manager settings to run when not logged in. I'd like this process to run even when I'm on vacation and wouldn't be logged in. Ideas?
I get an error from using your updated script that comes up when I run the script, and it says "Unknown runtime error: 'xl.DisplayAlerts' and the error code is 800AC472" I cannot seem to find a meaning or cause to this error but it could possibly be due to it opening the excel file in read-only mode by default? Anyone else have a solution? Thanks
I have the same error using his script!
Hi! Thanks for the Script. I have SharePoint Online and I have tried the same method but it's not updating the document in the SharePoint document library. I can see the task running in the task history and even i can see Excel opening, running and closing in the background processes in task manager. But it does not update the Excel file in the document library. I have tried a local folder to update the same Excel file and it worked perfectly. Any tips to get this working in SharePoint Online?
I have removed the "xl.DisplayAlerts = False" portion of the code and got an error. In line number 5, I have "wb.Save" and the error refers to that. This means reading the document works but saving doesn't . Help please!!
Try this:
Add this line after the set wb line
wb.LockServerFile
@@LifewithHappy Yes i did it on SharePoint online. You can use the script on an PC/Server having internet connectivity and use the scheduler to run it at a timely interval. Make sure that the account you are running the power shell script is having sufficient access to the document library. Also if you have multi factor authentication, make sure the app password is enabled in O365.
@@LifewithHappy you can have the script in any computer connected to internet. However, make sure the computer is up and running always otherwise the scheduler may not run if the computer is turned off.
Widura Weerasinghe thank you!
Any chance that the script could be embedded in the page? The task scheduler is tied to the computer in which it is scheduled. If I could get the script to be ran on the page every time it was open then it would run the script.
No, regretfully not. There are lots of ways to do this - but not without real scripting.
Talk about jumping through hoops! I wish Microsoft would make this easier since it kind of negates the idea of collaboration via SharePoint. Any idea if this works with Excel 2010 desktop client (license key:ed) in combination with 365? I have an old copy that wouldn't need the monthly log-on.
+oscillian Yes, you are right of course and I agree! Yes, this script would work with Excel 2010 also - good idea.
Hi!
I get the "How do you want to open this file?" -dialogue (every minute). Could you please support?
Hi - Did you ever find a solution for this? I have the same issue
I get the "Do you wish to overwrite file?"-dialogue every minute, lol! Any workaround?
+oscillian We have updated the script in the article to get rid of that problem. kalmstrom.com/Tips/SharePoint-Online-Course/HelpDesk-Update-Excel-Chart-Script.htm
+kalmstrom.com Perfect! I was going down the same road with OverwritePrompt, but this does the trick! Tack! :-)
+oscillian Varsågod! We are glad you pointed this out and gave us a chance to improve the script :-)
This doesn't work for me. Keeps saving the document in My documents! Not the SharePoint address:(
at least to skip the update promt,
1. On the Data tab, in the Connections group, click Edit Links
2. Click Startup Prompt.
3. Select the option that you want.
support.office.com/en-us/article/control-when-external-references-links-are-updated-21e995b5-bab1-4328-8ab3-dd357fe0e653
now its only the "cant connect to server"
Hi,
The file is opening by default in protected mode, so there is no update.
Any idea how to work around this?
Thanks
Set Excel to trust the path to your SharePoint location.
Add this line after the set wb line
wb.LockServerFile
I don't see where to obtain the "password"
Hi, thank you for sharing this. Any idea why my file is not saving in my sharepoint location?
No, but we do offer paid support to troubleshoot it for you if you want.
Mine is doing the same thing, saving in the same location as the VBS Script
Yes. I seem to have the same issue with this solution
+Fernando Marte : Map a Network Drive and give the excel location. Now open excel and update the default save location to the new mapped Network drive. It will work.
: Map a Network Drive and give the excel location. Now open excel and update the default saving location to the new mapped Network drive. It will work.