powershell

ImportExcel - PowerShell module ImportExcel - PowerShell module

ImportExcel is a powerful tool to manage MS Excel, however, the user is not limited to data stored in worksheets. We can import from and export to SQL Excel data using a combination of ImportExcel and dbatools modules. You can read how to start with dbatools in this blog series. Preparation Let’s prepare our environment…

Read More How to import/export data between SQL Server and Excel using PowerShell?

ImportExcel - PowerShell module ImportExcel - PowerShell module

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…

Read More How to protect Excel using PowerShell?

ImportExcel - PowerShell module ImportExcel - PowerShell module

So far we have been working on data or worksheets, but today we will look closer at some workbook-level properties and metadata. Workbook properties Open any Excel file and go to File > Info. You should see something like that on the right side: When you look at the bottom there is a link to…

Read More How to change Excel metadata using PowerShell?

ImportExcel - PowerShell module ImportExcel - PowerShell module

Do you have a long list of the Excel sheets in one file? How do you find what you need quickly? Moving around spreadsheets and making coloured tabs might help you. Moving worksheets around When we want to place the worksheet in a different place it is simple as drag&drop or using the context menu.…

Read More How to move Excel worksheets using PowerShell?

ImportExcel - PowerShell module ImportExcel - PowerShell module

The Microsoft Excel spreadsheet is very flexible, users can put whatever they want and it will accept it – even if it does not make much sense. Data Validation in Excel Luckily there is a feature called Data Validation (under Data > Data Tools). The feature helps to control the values in the spreadsheet, so…

Read More How to validate Excel using PowerShell?

ImportExcel - PowerShell module ImportExcel - PowerShell module

MS Excel offers many different functionalities and one of them is making things to disappear like hiding worksheets or columns and rows, even cells. This week’s post is for your eyes only 😉 Preparation Let’s prepare our environment now to make sure the file does not exist. As an example, we will use a workbook…

Read More How to hide Excel using PowerShell?

ImportExcel - PowerShell module ImportExcel - PowerShell module

Last week I have mentioned the new functions waiting to be included into the module. This week I would like to write about another set of functions: Add-ExcelHyperlink Get-ExcelHyperlink Remove-ExcelHyperlink Those are fresh too and were inspired by a query I have got from Garry Bargsley (blog|twitter) who needed adding hyperlinks to the existing cells…

Read More How to make Excel hyperlinks using PowerShell?

ImportExcel - PowerShell module ImportExcel - PowerShell module

The first week of the new shiny 2021 is a fact. While I was preparing for the series I spotted a pull request adding three more functions. Listing the functions with links to Michael Hokanson’s repo where you can grab to code as at the moment these functions are not part of the module yet.…

Read More How to manage notes in Excel with PowerShell?

ImportExcel - PowerShell module ImportExcel - PowerShell module

Another week and another tip for Excel using Powershell. This post was inspired by one of my readers (thank you Harsha). Conditional formatting is important, however by default we change the look of the cells that have specific values. What about changing the entire row if a specific cell meets certain conditions? Preparation Let’s prepare…

Read More How to format an entire Excel row based on the cell values with PowerShell?

This blog post is part of the Festive Tech Calendar. If you want to practice the whole thing I have prepared an interactive notebook for you that could be opened with Azure Data Studio for example (link to the notebook). For more things about the PowerShell module check this post out. I would like to…

Read More The MS Excel: An Unexpected Journey with PowerShell