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? Setup and Configuration Using our Endpoint 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.
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.
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).