How to automate Excel with reusable Office Scripts | Excel Off The Grid

[@danschroeder8008]

I'm sure the possibilities are endless with input. Great tutorial with lots of extra tips. Thanks so much.

[@ExcelCampus]

Great video Mark!

[@ExcelOffTheGrid]

Thanks Jon 😁

[@IvanCortinas_ES]

Excellent tutorial, Mark. Excellent that you have told this topic. Thank you for staying up to date with the news.

[@ExcelOffTheGrid]

Most updates are discoverable - but this would be difficult to find. So glad could help.

[@corinnemazaleigue1619]

Great Video Mark Proctor

[@ExcelOffTheGrid]

Thank You 😁

[@lotlaxbbc2787]

Thank You. Do you have a reusable script for protection of all the sheets as well?

[@ExcelOffTheGrid]

Do I have one? Yes. But you could follow the same process as shown in the video and create your own 👍

[@ramilaquino5350]

Great video! I was wondering if there is a way to automate file conversion, say .xls to .csv using office scripts? would really appreciate the help! TIA

[@ExcelOffTheGrid]

With Office Scripts - no chance… sorry.

[@rajeshmajumdar4999]

Thank You.

[@ExcelOffTheGrid]

You're welcome 👍

[@petercompton538]

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

[@ExcelOffTheGrid]

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.

[@mecorowa5642]

Is there an option for dynamic print area?

[@ziggle314]

I clearly need to do more office scripts! Thanks for the inspiration.

[@ExcelOffTheGrid]

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.

[@patrickschardt7724]

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

[@ExcelOffTheGrid]

You would need to provide all the passwords at the start. As a list or range of cells.

[@donniemcgee7523]

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.

[@ExcelOffTheGrid]

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/

[@noraiBI]

Really usefull Mark

[@ExcelOffTheGrid]

Glad you think so! 😁

[@donniemcgee7523]

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 ???

[@ivanmamchych5802]

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

[@ExcelOffTheGrid]

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() } }

[@donniemcgee7523]

@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

[@donniemcgee7523]

Amazingly gelpful. Thank you so much!​@@ExcelOffTheGrid

[@MichaelBrown-lw9kz]

Is there a book I can purchase on Office Scripts for Excel?

[@ExcelOffTheGrid]

Not to my knowledge - I’ve got a course if you’re interested: courses.exceloffthegrid.com/office-scripts-course

[@ExcelWithChris]

Do these work in an Excel file on-line? Because macro's do not run.

[@ExcelOffTheGrid]

Yes, they work in Excel Online (assuming the correct licence). That's where Office Scripts were originally released.

[@shadeburst]

Automate tab is only available in certain versions of Excel.

[@ExcelOffTheGrid]

Correct. But it is coming to more versions of 365 soon. 🤞

[@petervandewynkele5198]

When i aply this, the worksheets that have been protected "but without a password" do not open. What is needed to open these also

[@nikwak]

I can't seem to get the Automate tab to show on ribbon

[@ExcelOffTheGrid]

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)

[@PubuduPerera]

Contact your admin. Automate need to be enabled by admin account in 365

[@matiasgasperini]

IT people crying in safety issues 3:01

[@ExcelOffTheGrid]

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. 🤣