How to automate Excel with reusable Office Scripts | Excel Off The Grid
HTML-код
- Опубликовано: 28 май 2024
- ★ Want to automate Excel? Check out our training academy ★
exceloffthegrid.com/academy
★ Get the code ★
exceloffthegrid.com/reusable-...
★ About this video ★
Recently, Office Scripts gained the ability to run scripts using parameters. This means we can provide custom values to change the outcome of the Office Script.
This suddenly makes Office Scripts significantly more useful in Excel.
In this video, we work step-by-step, showing how to change a recorded code into a reusable script that leverages this new feature.
0:00 Introduction
0:30 Outline example
0:55 Recording the action
3:27 Handling errors
6:13 Apply to all
8:22 Apply to all
7:44 Prevent drag and drop
10:44 Sharing scripts
11:35 Quick access
13:24 Conclusion
★ Download 30 most useful Excel VBA Macros ebook for FREE ★
exceloffthegrid.com/
★ Where to find Excel Off The Grid ★
Blog: exceloffthegrid.com
Twitter: / exceloffthegrid
#MsExcel
Great Video Mark Proctor
Thank You 😁
I'm sure the possibilities are endless with input. Great tutorial with lots of extra tips. Thanks so much.
Excellent tutorial, Mark. Excellent that you have told this topic. Thank you for staying up to date with the news.
Most updates are discoverable - but this would be difficult to find. So glad could help.
Great video Mark!
Thanks Jon 😁
I clearly need to do more office scripts! Thanks for the inspiration.
It's still early days for Office Scripts. So it's a good time to be the leader of the pack. But the UI and IntelliSense is definitely more enjoyable than the Visual Basic Editor.
Is there an option for dynamic print area?
Thank You.
You're welcome 👍
Really usefull Mark
Glad you think so! 😁
Is there a way to get a password input for each sheet as it loops through?
I’m new to scripts. I’m glad the syntax and editor window is similar to Power Query Advanced Editor
You would need to provide all the passwords at the start. As a list or range of cells.
Thank You. Do you have a reusable script for protection of all the sheets as well?
Do I have one? Yes.
But you could follow the same process as shown in the video and create your own 👍
You have been so helpful with your amazingly well done tutorials. I am grateful for your well-thought out and executed videos. I wanted to send you a few dollars to show a bit of my appreciation. Unfortunately, I cannot see an icon upon which I can click to send a token of my appreciation.
Hi Donnie - if you like what we do, and you watch the videos then you've already given us the most valuable thing you have... your time.
If you like our content, all we ask is that you share it with others.
Also, feel free to join our insiders program (it's free). We have ebooks and the example files for you to enjoy. Sign up here: exceloffthegrid.com/insiders-signup/
Can a script be used to clear all slicers? I have a macro that does that, but my workplace blocks all macros (and we are disallowed to unblock them by saving the file, right clicking, select properties and select unblock)
Maybe a script can help with that ???
Clearing all slicers is the same as clearing all filters. You can find the menu button "Clear Filter" and add it to your Excel Quick Access Toolbar
I didn't know. So I tried it out.
Here you go...
function main(workbook: ExcelScript.Workbook){
let sliArr = workbook.getSlicers()
for (let i = 0; i < sliArr.length; i++){
sliArr[i].clearFilters()
}
}
@ivanmamchych5802 thank you very much. That was extremely helpful. I tried doing that on the data tab (which was my mistake) . It works perfectly from the pivot table analyze tab. Thank you again
Amazingly gelpful. Thank you so much!@@ExcelOffTheGrid
Hi Mark, I love your videos, both for their content and for your very engaging delivery! Just a question: I'm looking at the screen at 8:28 of the video and you have a line saying
for (let i = 0; i
Hi Peter.
Office Scripts is based on JavaScript. There are various was to increment an iterator with JavaScript.
i + 1 by itself won't do anything as it doesn't allocate the new value back to i.
It would need to be: i = i + 1
But there are other shorthand methods
i++ ( iterates by 1 )
i +=1 ( iterates by the number after the = )
I'm not sure about other languages, but that's how JavaScript works.
Automate tab is only available in certain versions of Excel.
Correct. But it is coming to more versions of 365 soon. 🤞
I can't seem to get the Automate tab to show on ribbon
Either you don't have Excel 365, or your licence does have Office Scripts included (check our your licence here: learn.microsoft.com/en-us/office/dev/scripts/testing/platform-limits?tabs=business)
Contact your admin. Automate need to be enabled by admin account in 365
Do these work in an Excel file on-line? Because macro's do not run.
Yes, they work in Excel Online (assuming the correct licence). That's where Office Scripts were originally released.
IT people crying in safety issues 3:01
It’s OK, Excel’s protection is so weak that any password can be hacked is seconds. So might as well pick an easy one. 🤣