Press "Enter" to skip to content

Category: Powershell

Building VBA Macros for Excel in Powershell

Mikey Bronowski has a case of developer inception:

This is part of the How to Excel with PowerShell series. Links to all the tips can be found in this post.
If you would like to learn more about the module with an interactive notebook, check this post out.

In this last post of the series, I am going to mix Excel, PowerShell and VBA. If you weren’t using PowerShell to manage Excel files before, you might have used VBA (Visual Basic for Applications) to do so. Excel is a powerful tool and even this area can be improved a little bit with PowerShell.

It’s a shame to see this series wrap up, but Mikey has provided a huge amount of content around automating Excel spreadsheet creation. If you haven’t been reading these, I definitely recommend it.

Comments closed

Common Power BI Administration Scripts

Brett Powell continues a series:

Today’s post builds on top of Power BI Admin Scripts Part I by describing five additional PowerShell scripts that Power BI service administrators can utilize to address relatively common scenarios. Like Part I, the five new scripts have been added to my GitHub repository and I’ll only share context in the blog.

Please be sure to read through the prerequisites section of Part I and confirm you have the necessary permissions (e.g. Power BI Admin role) and software installed such as the latest Power BI Management PowerShell modules.

Check it out for five more scripts. Brett also teases a part 3, but you have to wait until after the credits sequence to see it.

Comments closed

Reading Extended Events Files with Powershell

Emanuele Meazzo shows how to work with Extended Event *.xel files:

However, I’ve found myself in a tricky spot, as I had multiple instances recording events, and those events had to be analyzed as a whole.
I could have simply written a script to get the data from each instance, querying the system function sys.fn_xe_file_target_read_file and then uploading it somewhere else, but this approach has the issue of adding additional load on the source instances, and I didn’t want to add additional load to the already overworking instances I was trying to monitor.

I then wondered, can I move the files over from the busy servers and read them from another machine? I surely didn’t want to open each file manually and exporting it to a table and/or CSV in order to mash them together, too

Read on to see how.

Comments closed

Adding Images to Excel using Powershell

Mikey Bronowski continues a series on working with Powershell:

This is part of the How to Excel with PowerShell series. Links to all the tips can be found in this post.
If you would like to learn more about the module with an interactive notebook, check this post out.

Spreadsheets’ main purpose is data: storing, manipulating and analyzing them. We can add some colours or charts to make the data more friendly, but sometimes we may want to add something else – like a logo or picture and all that can be achieved with PowerShell.

Read on to see how you can lay out an image or add shapes to a spreadsheet.

Comments closed

Combining SendTo and Powershell

Mark Wilkinson shares a script with us:

If you are not familiar, SendTo options are those available when you right click on a file/folder in file explorer and select the Send To option in the menu. When you use this option, the currently selected files/folders are passed to the SendTo shortcut as a space delimited list of files and folders. This is important to know so you better understand what needs to be done to read that list.

I can confirm that this works well for deploying script out, especially when they need to go to multiple servers or multiple databases on servers. That functionality takes a bit more effort to write, but combine Mark’s code with Jess’s and you are well on your way.

Comments closed

Executing a Folder of SQL Scripts against SQL Server

Jess Pomfret has a quick Powershell snippet for us:

Another week and another useful dbatools snippet for you today.  Last week at work I was given a folder of 1,500 scripts – each containing a create table statement. Can you imagine having to open each file in Management Studio to be able to execute it? Thank goodness we have PowerShell and dbatools on our side.

Click through for the command, as well as Jess’s explanation of how it works.

1 Comment

Protecting Excel with Powershell

Mikey Bronowski shows us a few techniques for protecting data in Excel files using Powershell:

Last month we have been hiding things in Excel, so this week we are going to make sure they are protected as well. Excel offers multiple levels of password protection and its options:

– locking file with a password, i.e. without key phrase opening file is not possible
– protecting workbook’s structure
– lastly, protecting individual worksheets from a handful of operations

Read on to see each of those in action.

Comments closed

Polling Loops in Powershell

Aaron Nelson has one method for creating a polling loop in Powershell:

Originally I had used the Start-Sleep command to wait 3 seconds ( Start-Sleep 3
). That worked fine on my machine, but when I deployed it to the server, I found I needed to bump it up to 6 seconds. At first that worked, but then a week later I needed to bump it up to 9 seconds. The problem here is obvious, if we force it to wait 9 seconds every time, even if the task was updated after 4 second, we’re wasting extra time. And those seconds are going to add up.

Read on for a smarter approach. Ideally we’d be able to use asynchronous event handling with awaits for all of this, but the real world is not always so nice.

Comments closed

SPN Registration and dbatools

Jess Pomfret takes us through some SPN pains:

But instead of getting a quick answer to my question, I just got the following error:

WARNING: [15:19:49][Get-DbaDatabase] Error occurred while establishing connection to dscsvr1 | The target principal name is incorrect. Cannot generate SSPI context.

Just reading the article brought back some bad troubleshooting memories for me… But as usual, I’m impressed that dbatools has a cmdlet or two to help with that troubleshooting.

Comments closed