Convert VBA Macros To PowerShell for Microsoft Office Automation
There is a lot of documentation out there for interacting with Microsoft Office including Outlook, Excel, Word, etc with Visual Basic for Applications (VBA). A lot of time you may only be able to find VBA examples. VBA’s require template files to be sent to the desktop and are a real hassle when trying to automate across multiple machines.
There are not many A to B examples of translating VBA to PowerShell so I took a problem I had solved in the past and presented the before and after. Hopefully it will provide enough information to allow others to convert VBA code into PowerShell for their scenarios.
You can check out the full article on PowerShellBlogger.com.
Related Articles
Secure Your Powershell Session with JEA and Constrained Endpoints
Index
What is a constrained endpoint and why would I need one?
Powershell constrained endpoints are a means of interacting with powershell in a manner consistent with the principal of least privilege. In Powershell terms, this is referred to as Just-Enough-Administration, or JEA.
JEA is very well documented, so this won’t simply be repeating everything those references detail. Instead, we’ll go through a simple, real-world use-case of when and why you might need to deploy one.
Pitfalls of the Pipeline
Pipelining is an important concept in PowerShell. Though the idea did not originate with PowerShell (you can find it used decades earlier in Unix, for example), PowerShell does provide the unique advantage of being able to pipeline not just text, but first-class .NET objects.
Pipelining has several advantages:
- It helps to conserve memory resources. Say you want to modify text in a huge file. Without a pipeline you might read the huge file into memory, modify the appropriate lines, and write the file back out to disk. If it is large enough you might not even have enough memory to read the whole thing.
- It can substantially improve actual performance. Commands in a pipeline are run concurrently-even if you have only a single processor, because when one process blocks, for example, while reading a large chunk of your file, then another process in the pipeline can do a unit of work in the meantime.
- It can have a significant effect on your end-user experience, enhancing the perceived performance dramatically. If your end-user executes a sequence of commands that takes 60 seconds, then until 60 seconds has elapsed he/she would see nothing without pipelining, whereas output could start appearing almost immediately with pipelining.
PowerShell provides a variety of techniques for using pipelining but it is all to easy to do it wrong, so you think you are pipelining but in fact you are not. In my article Ins and Outs of the PowerShell Pipeline, I discuss the most common things that can trip you up with implementing pipelining and how to avoid them.
Practical PowerShell Unit-Testing
By the time you are using PowerShell to automate an increasing amount of your system administration, database maintenance, or application-lifecycle work, you will likely come to the realization that PowerShell is indeed a first-class programming language and, as such, you need to treat it as such. That is, you need to do development in PowerShell just as you would with other languages, and in particular to increase robustness and decrease maintenance cost with unit tests and–dare I say–test-driven development (TDD). I put together several articles on getting started with unit tests and TDD in PowerShell using Pester, the leading test framework for PowerShell. This series introduces you to Pester and provides what I like to call “tips from the trenches” on using it most effectively, along with a gentle prodding towards a TDD style.
Part 1: Getting Started with the Pester Framework
Starting with the ubiquitous “Hello, World”, this introduces Pester, showing how to execute tests, how to start writing tests, and the anatomy of a test.
Part 2: Mock Objects and Parameterized Test Cases
To be able to create true unit tests, you need to be able to isolate your functions and modules to be able to focus on the component under test; mocks provide great support for doing that. Another topic of “power” unit tests is making them parameterizable, i.e. being able to run several scenarios through a single test simply by providing different inputs.
Part 3: Validating Data and Call History
The final part of this series provides a “how-to” for several other key parts of Pester: how to validate data, how to determine if something was called appropriately, and how to address a particular challenge with Pester, validating arrays. I’ve included a library for array validation to supplement Pester.
For a more general treatment of unit tests, I refer you to Roy Osherove’s canonical text on the subject, The Art of Unit Testing.