Archive | December 2017

Open Excel file and run macro

I needed a way to open an Excel file and run one of the macro’s inside the file to do some calculations and import some data then save the file as a new file, with the name of the previous month and its year added to it, to differentiate it from the original file.

Make sure macros are enabled within Excel, otherwise they’ll be disabled when opening the Excel file which will result in the script not working.

When working with Excel and PowerShell, when you’re living outside of the US, make sure you set the CurrentCulture to EN-US, otherwise you’ll get several Excel errors.
I’ll first save the CurrentCulture setting in a variable, so I can change it back after the script finishes. So, in the beginning of the script I’ll have these lines:

$CurrentCulture = [System.Threading.Thread]::CurrentThread.CurrentCulture.Name

[System.Threading.Thread]::CurrentThread.CurrentCulture = "en-US"

And after the script finishes, I’ll set it back like this:

[System.Threading.Thread]::CurrentThread.CurrentCulture = $CurrentCulture

To get the name of the month and the year, use the following two lines. I like to have the month name start with a capital letter, thus I add the third line of code:

$dateTextMonth = Get-Date -Date (Get-Date).AddMonths(-1) -format MMMM
$dateTextYear = Get-Date -Date (Get-Date).AddMonths(-1) -format yyyy
$dateTextMonthString = $dateTextMonth.Substring(0,1).toUpper() + $dateTextMonth.Substring(1).ToLower()

Now we can open Excel, open the file and run the macro. I’ll set the Excel save format to an Excel file with macros enabled.

Add-Type -AssemblyName Microsoft.Office.Interop.Excel
$xlFixedFormat = [Microsoft.Office.Interop.Excel.XlFileFormat]::xlOpenXMLWorkbookMacroEnabled

$app = New-Object -comobject Excel.Application
$app.Visible = $True
$app.DisplayAlerts = $False
$wb = $App.Workbooks.Open("$folderName\$fileName.xlsm")


$app.ActiveWorkbook.SaveAs("$folderName\$fileName $dateTextMonthString $dateTextYear.xlsm", $xlFixedFormat)

And now it’ll open Excel, open the macro enabled workbook, run the macro, save the workbook with a new name and close Excel.

Sometimes the script would be to quick, thus I needed to add a start-sleep for 1 second, to make sure the Excel file is opened in Excel before I run the macro.

The complete script can be downloaded here.

Create a sharepoint (document) library

Creating a sharepoint library (works in both sharepoint foundation and sharepoint server) with powershell is a nice way to ensure all libraries within sharepoint are configured the same way.

What we’ll be doing here, is creating a library, disable anonymous access on it and only allow certain user groups to have access rights to this library. We will create a document library, with 2 user groups which have contribute access rights and one group which has full control access rights. We will enable the quicklaunch link for this library and enable versioning (52 versions).

First we’ll add the sharepoint powershell snap in:

Add-PsSnapin Microsoft.SharePoint.PowerShell

After this we’ll define our defaults. We’ll be adding a Help desk user group (contribute) and an administrators users group (full control) and a users group specifically created for access rights on this library (contribute). You’d need to edit this to work on your environment.

$WebUrl = ""
$HelpDeskUsers = "DOMAIN\HelpDeskUsersGroup"
$AdminsGroup = "DOMAIN\AdminsUsersGroup"
$LibraryUsers = "DOMAIN\LibraryUsersGroup"

After setting this, we’ll get the sharepoint web application:

$SpAssignment = Start-SPAssignment
$SpWeb = Get-SPWeb -Identity $WebUrl -AssignmentCollection $spAssignment

Normally I’ll have the libraryUsers group defined by the name of the library, thus in my code I’ll have a Foreach loop in case I’d like to create multiple document libraries.

In the script below I’ll be creating the sharepoint users group, set the library type to create to ‘document library’, remove the inheritance for the library, enable quick launch, enable versioning and remove anonymous access (as I have a publically available sharepoint, but its document libraries I don’t want to be available anonymous). Then I’ll add the user groups to their specific role definition (full control / contribute), I also set the description for the library. After that I’ll close the sharepoint connection and all is done. Here is the rest of the code:

#Create a list of users groups (needed for access rights to the library)
$AllUsers = $LibraryUsers,$HelpDeskUsers,$AdminsGroup # Contribute, Contribute, Full Control
#Create the sharepoint login for the library specific users group
New-SPUser -UserAlias $LibraryUsers -Web $WebUrl -DisplayName $LibraryUsers
#Set the library type to Document Library
$ListTemplate = [Microsoft.SharePoint.SPListTemplateType]::DocumentLibrary
#Add the document library to the web application
$SpWeb.Lists.Add("$Library","$Library Shared Documents",$ListTemplate)
#Get the created document library to set specific settings
$spDocumentLibrary = $spWeb.GetList("$Library")
#Remove inheritance and remove all access entries (bool copyRoleAssignments, bool clearSubscopes)
#Add the library to the quick launch (visible to peopl with access rights to the library)
$spDocumentLibrary.OnQuickLaunch = $true
#Enable versioning on the library (default setting: only keep major versions)
$spDocumentLibrary.EnableVersioning = $true
#Set a maximum of 52 versions (1 version for each weekly documentation, thus keeping 1 year of documentation and changes)
$spDocumentLibrary.MajorVersionLimit = 52
#Disable anonymous access to this library. (Can only set 1 EmptyMask, the other one with automatically be set)
$spDocumentLibrary.AnonymousPermMask = [Microsoft.Sharepoint.SPRights]::EmptyMask
#$spDocumentLibrary.AnonymousPermMask64 = [Microsoft.Sharepoint.SPBasePermissions]::EmptyMask
#Add user (group) permissions to the library
Foreach($Users in $AllUsers)
$User = Get-SPUser -Web $WebUrl | Where DisplayName -eq $Users
If($Users -eq $AdminsGroup) { $Role = $SpWeb.RoleDefinitions["Full Control"] }
Else { $Role = $SpWeb.RoleDefinitions["Contribute"] }
$Roleassignment = New-Object Microsoft.SharePoint.SPRoleAssignment($User)
#Update the library and dispose the web application psobject
$spFolder = $spDocumentLibrary.AddItem("",[Microsoft.SharePoint.SPFileSystemObjectType]::Folder,"Documentation")


The complete script can be downloaded here

In the script, there are several items which need to be changed so the script works on your environment. First you’d need to enter the location (URL) of your sharepoint environment.

$WebUrl = ""

After that, the 3 user groups need to be changes to fit your needs. The variables in which they are stored in the script are: $HelpDeskUsers, $AdminsGroup, $LibraryUsers.