General scripting question: where to start (VBS or/and PowerShell)?

Question

Hello all,
and apologies if this is a battered question - the suggested "similar questions" did not yield the answers I expected.

I am an experienced developer (compiled languages), but I am a rookie in terms of script languages on Windows. I have limited time to learn, so I'm looking for the best way to invest this time in the right scripting language(s).
--> When would you advise to apply VBS vs PowerShell, or both?

Of course in terms of technical capabilities the answer depends on what I want to do, see details in P.S., but I am also wondering about the long-term viability of VBS.

• I know a bit about VBA macros, but my current corporate environment does not allow me to allow macros even from safe locations.
• Then I started learning VBS, and satisfactorily implemented some simple manipulations. However I hit a wall on some APIs, and while looking for answers, I discovered about Powershell.
• Looking (a bit) into Powershell, I can't tell yet if this is a replacement of VBS.

I now wonder if I should invest my time in learning more about VBS, or starting to learn PowerShell instead - I doubt I can afford both :( and I wouldn't want to invest too much in VBS if it's going to be phased out.
Where would you advise me to spend time? And what are the clues on the future of those technologies?

Thank you in advance.
J.

P.S.: details follow

A bit of context:
My short-term needs are to automate various moderately complex manipulations of Excel and Word files. Here are a few examples - I am NOT asking you to solve those for me, it is my job to solve them, and actually I enjoy learning new things:

• Extract data subsets from Excel multi-sheets documents, save as csv (so far, easy)
• Populate Excel documents from various sources, including, other csv data (moderately easy, I have some problems altering Excel tables)
• Copy Word files, with various manipulations on filenames, and generate reports on number of files, location,... (not started but seems relatively easy)
• Extract and consolidate specific sections of word documents (seems a bit hard so far, I still need to dig more into the objects model)

What I do and don't understand (yet):

• VBA and VBS are based on a common Visual Basic syntax.
• VBA has built-in access to Excel's and Word's object model. But executing macros is inconvenient in my enterprise-controlled configuration.
• VBS seems to access this object model too, though sometimes some methods and properties silently fail; that's the part where I have to investigate more (dedicated question thread will ensue if I persist in this direction)
• PowerShell seems to not natively be able to access those object models, and I need to install some packages which is inconvenient in a controlled enterprise configuration, with limited or no Administrator privileges,...

Answer 1

My short-term needs are to automate various moderately complex manipulations of Excel and Word files.

The usual way is with Office Automation, that can be done with PowerShell

You can find many samples on Google, like for example :
COM Automation of Office Applications via PowerShell
PowerShell: Automating Excel
Microsoft Excel Automation with Powershell
etc...

(VBScript is old now (VBScript is no longer supported in IE11 edge mode))

Answer 2

Edit: OK I seem to be able to edit the first post to format it properly; sorry for the hiccup, I am learning this forum's formatting syntax...

Answer 3

Thank you. Indeed I found one or two references about VB Script passing out.
I used one of the references you provided, and undertook to translate one of my VBS scripts (the "easy" one) into PowerShell, so far it's been tedious but successful... after all the objects model is the same, only the syntax changes.
I was surprised I could access the Excel objects, contrary to what I had read in my earlier research... Well reading is good but nothing beats trying :)

I welcome other opinions, but it seems I'm bound to invest my learning time in PowerShell then.

Thanks again.

Answer 4

Hi Jethrie,

Maybe you can refer to this document to learn powershell script: https://learn.microsoft.com/en-us/powershell/scripting/samples/sample-scripts-for-administration?view=powershell-7.1

Best Regards, Dylan

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our * *documentation* to enable e-mail notifications if you want to receive the related email notification for this thread.**