Use PowerShell to Convert a Decimal to a Fraction

While working on a project, I had the need to convert a decimal to a fraction and while I know that I could do some math outside of PowerShell (or look it up online), I wanted to be able to do this in PowerShell. Unfortunately, I couldn’t find anything in the PowerShell world that did this. That being the case, I decided to take this on and see what I could do.

This was a nice challenge coming up with a way to take a decimal and then present it as a fraction (note that the fraction is just going to be a string). In the end, I went with iterating through numbers for both the numerator and the denominator until I either get close to a usable fraction or actually find the fraction itself. This may not be the most efficient way of accomplishing the task, but against smaller decimals it works just fine.

My parameters are:

  • Decimal
    • The value that we will  converting to a fraction
  • ClosesDenominator
    • Used to get the most precision for the fraction by getting as close to the accurate value as possible
  • ShowMixedFraction
    • Displays the value as a Mixed Fraction instead of an Improper Fraction
  • AsObject
    • Displays the value as an object instead of as a fraction
Param(      
    [parameter(ValueFromPipeline=$True,ParameterSetName='Object')] 
    [parameter(ValueFromPipeline=$True,Position=0,ParameterSetName='NonObject')] 
    [double]$Decimal = .5,
    [parameter(ParameterSetName='Object')] 
    [parameter(ParameterSetName='NonObject')] 
    [int]$ClosestDenominator = 100,
    [parameter(ParameterSetName='NonObject')]  
    [switch]$ShowMixedFraction,
    [parameter(ParameterSetName='Object')] 
    [switch]$AsObject
)

Up next is the piece of code that I use to set up to begin looking for a fraction:

$Break = $False
$Difference = 1
If ($Decimal -match '^(?<WholeNumber>\d*)?(?<DecimalValue>\.\d*)?$') {
    $WholeNumber = [int]$Matches.WholeNumber
    $DecimalValue = [double]$Matches.DecimalValue
}
$MaxDenominatorLength = ([string]$ClosestDenominator).Length
$DecimalLength = ([string]$Decimal).Split('.')[1].Length+1
$LengthDiff = $MaxDenominatorLength - $DecimalLength
If ($LengthDiff -lt 0) {
    Write-Warning @"
Decimal <$($Decimal)> is greater of length than expected Denomimator <$($ClosestDenominator)>.
Increase the size of ClosestDenomimator to <$(([string]$ClosestDenominator).PadRight($DecimalLength,'0'))> to produce more accurate results.
"@
} 

Here I am making sure that I can break when I need to (but just not yet) by setting $Break to $False. $Difference is used to help determine how close I am getting to finding that accurate fraction. In the case that I have a whole number with a decimal, I need to split those off to handle each one on its own. Lastly, I throw a friendly warning if the decimal length is larger than my ClosestDenominator length as this will affect the accuracy of the fractions. This is a little unique because if you want to deal with a repeating decimal, such as .33333…, then you know that it will be 1/3 but unless the ClosestDecimal is shorter than the decimal, then you will the accurate result of 3333/10000 in this case instead of 1/3. Just something to keep in mind and I will demo this later on.

Now onto the main part of the code that starts the recursive searching for the fraction:

If ($DecimalValue -ne 0) {
    #Denonimator - Needs to be 2 starting out
    For ($Denominator = 2; $Denominator -le $ClosestDenominator; $Denominator++) {
        #Numerator - Needs to be 1 starting out
        For ($Numerator = 1; $Numerator -lt $Denominator; $Numerator++) {
            Write-Verbose "Numerator:$($Numerator) Denominator:$($Denominator)"
            #Try to get as close to 0 as we can get
            $temp = [math]::Abs(($DecimalValue - ($Numerator / $Denominator)))
            Write-Verbose "Temp: $($Temp) / Difference: $($Difference)"
            If ($temp -lt $Difference) {                                                               
                Write-Verbose "Fraction: $($Numerator) / $($Denominator)"
                $Difference = $temp                         
                $Object = [pscustomobject]@{
                    WholeNumber = $WholeNumber
                    Numerator = $Numerator
                    Denominator = $Denominator
                }
                If ($Difference -eq 0) {
                    $Break = $True
                }                
            }
            If ($Break) {BREAK}
        }
        If ($Break) {BREAK}
    }
} Else {
    $Object = [pscustomobject]@{
        WholeNumber = $WholeNumber
        Numerator = 0
        Denominator = 1
    }    
}
If ($Object) {
    If ($PSBoundParameters.ContainsKey('AsObject')) {
        $Object
    } Else {
        If ($Object.WholeNumber -gt 0) {
            If ($PSBoundParameters.ContainsKey('ShowMixedFraction')) {
                "{0} {1}/{2}" -f $Object.WholeNumber, $Object.Numerator, $Object.Denominator               
            } Else {
                $Numerator = ($Object.Denominator * $Object.WholeNumber)+$Object.Numerator
                "{0}/{1}" -f $Numerator,$Object.Denominator                 
            }
        } Else {
            "{0}/{1}" -f $Object.Numerator, $Object.Denominator
        }
    }
}

A lot of things happening here, but nothing too crazy going on.  I start out by setting my starting Denominator to 2 (doing a 1 would treat anything greater than 0 as a whole number) and begin using that in a For() loop. This will continue to increment until I hit the ClosestDenominator that is defined by the parameter. Next, the numerator will begin working to increment by starting at 1 and will increment to the size of the Denominator before resetting for the next Denominator.

Now I take the decimal that I am trying to convert to a fraction and subtract it by the value of the division of the numerator and denominator to get as close or equal to zero. If I can get to zero, then I have the most accurate fraction, otherwise I continue going and go with the last closes value. An example is here showing how we get 3/4 from .75:

image

From there it is just a matter of taking the object that is created and formatting it to the type of fraction that we are expecting.

With all of that said, it is time for some examples of this in action.

Convert-DecimalToFraction –Decimal .5

image

Convert-DecimalToFraction –Decimal .33

image

Convert-DecimalToFraction –Decimal .333

image

Convert-DecimalToFraction –Decimal 1.15

image

Convert-DecimalToFraction –Decimal 1.15 –ShowMixedFraction

image

Convert-DecimalToFraction –Decimal 1.15 –AsObject

image

Convert-DecimalToFraction –Decimal .005 –ClosestDenominator 1000

image

Give the function a download from the link below and let me know what you think!

Download Convert-DecimalToFraction

https://gallery.technet.microsoft.com/scriptcenter/Convert-a-Decimal-to-a-7dc416be

Posted in powershell | Tagged , , , | 2 Comments

Another Way to List Files and Folders Beyond 260 Characters Using Pinvoke and PowerShell

I covered a pretty nice way to list all of files and folders in which the total number of characters extended beyond the 260 limitation (MAX_PATH limitation) using a mix of robocopy and PowerShell. In that article I show you how to harness the ability of using robocopy to go beyond the limitation and also using some regular expressions to parse out the data that we need.

That is great and all, but I wanted something else to take as a challenge and decided that using Pinvoke to utilize some Win32 functions would work nicely! Of course, there are other avenues to locating files and folders beyond this limit in 3rd party tools and PowerShell modules such as the File System Security Module and anything using AlphaFS. But what fun is that when we can dig into some pinvoke to make this happen? I’m always up for the challenge of digging into this and making my own tools just for the sake of learning more and being able to share my code.

With that, we can begin the task at hand of building out this code for my function, which I will call Get-ChildItem2 as I aim to make it match Get-ChildItem with the exception that it will be able to look beyond the character limitation.

I am going to need the help of three win32 functions to be able to safely traverse the filesystem without being stopped by limitations. Those are:

I won’t cover the process of using Reflection to create the necessary methods, structs, etc… that is neede for pinvoke here, but you can look at a previous blog post that I wrote to see how I built these out. That being said, here is my code to build up the reflection piece:

Try{
    [void][PoshFile]
} Catch {
    #region Module Builder
    $Domain = [AppDomain]::CurrentDomain
    $DynAssembly = New-Object System.Reflection.AssemblyName('SomeAssembly')
    $AssemblyBuilder = $Domain.DefineDynamicAssembly($DynAssembly, [System.Reflection.Emit.AssemblyBuilderAccess]::Run) # Only run in memory
    $ModuleBuilder = $AssemblyBuilder.DefineDynamicModule('SomeModule', $False)
    #endregion Module Builder
 
    #region Structs            
    $Attributes = 'AutoLayout,AnsiClass, Class, Public, SequentialLayout, Sealed, BeforeFieldInit'
    #region WIN32_FIND_DATA STRUCT
    $UNICODEAttributes = 'AutoLayout,AnsiClass, UnicodeClass, Class, Public, SequentialLayout, Sealed, BeforeFieldInit'
    $STRUCT_TypeBuilder = $ModuleBuilder.DefineType('WIN32_FIND_DATA', $UNICODEAttributes, [System.ValueType], [System.Reflection.Emit.PackingSize]::Size4)
    [void]$STRUCT_TypeBuilder.DefineField('dwFileAttributes', [int32], 'Public')
    [void]$STRUCT_TypeBuilder.DefineField('ftCreationTime', [long], 'Public')
    [void]$STRUCT_TypeBuilder.DefineField('ftLastAccessTime', [long], 'Public')
    [void]$STRUCT_TypeBuilder.DefineField('ftLastWriteTime', [long], 'Public')
    [void]$STRUCT_TypeBuilder.DefineField('nFileSizeHigh', [int32], 'Public')
    [void]$STRUCT_TypeBuilder.DefineField('nFileSizeLow', [int32], 'Public')
    [void]$STRUCT_TypeBuilder.DefineField('dwReserved0', [int32], 'Public')
    [void]$STRUCT_TypeBuilder.DefineField('dwReserved1', [int32], 'Public')
 
    $ctor = [System.Runtime.InteropServices.MarshalAsAttribute].GetConstructor(@([System.Runtime.InteropServices.UnmanagedType]))
    $CustomAttribute = [System.Runtime.InteropServices.UnmanagedType]::ByValTStr
    $SizeConstField = [System.Runtime.InteropServices.MarshalAsAttribute].GetField('SizeConst')
    $CustomAttributeBuilder = New-Object System.Reflection.Emit.CustomAttributeBuilder -ArgumentList $ctor, $CustomAttribute, $SizeConstField, @(260)
    $cFileNameField = $STRUCT_TypeBuilder.DefineField('cFileName', [string], 'Public')
    $cFileNameField.SetCustomAttribute($CustomAttributeBuilder)
 
    $CustomAttributeBuilder = New-Object System.Reflection.Emit.CustomAttributeBuilder -ArgumentList $ctor, $CustomAttribute, $SizeConstField, @(14)
    $cAlternateFileName = $STRUCT_TypeBuilder.DefineField('cAlternateFileName', [string], 'Public')
    $cAlternateFileName.SetCustomAttribute($CustomAttributeBuilder)
    [void]$STRUCT_TypeBuilder.CreateType()
    #endregion WIN32_FIND_DATA STRUCT
    #endregion Structs
 
    #region Initialize Type Builder
    $TypeBuilder = $ModuleBuilder.DefineType('PoshFile', 'Public, Class')
    #endregion Initialize Type Builder
 
    #region Methods
    #region FindFirstFile METHOD
    $PInvokeMethod = $TypeBuilder.DefineMethod(
        'FindFirstFile', #Method Name
        [Reflection.MethodAttributes] 'PrivateScope, Public, Static, HideBySig, PinvokeImpl', #Method Attributes
        [IntPtr], #Method Return Type
        [Type[]] @(
            [string],
            [WIN32_FIND_DATA].MakeByRefType()
        ) #Method Parameters
    )
    $DllImportConstructor = [Runtime.InteropServices.DllImportAttribute].GetConstructor(@([String]))
    $FieldArray = [Reflection.FieldInfo[]] @(
        [Runtime.InteropServices.DllImportAttribute].GetField('EntryPoint'),
        [Runtime.InteropServices.DllImportAttribute].GetField('SetLastError')
        [Runtime.InteropServices.DllImportAttribute].GetField('ExactSpelling')
        [Runtime.InteropServices.DllImportAttribute].GetField('CharSet')
    )
 
    $FieldValueArray = [Object[]] @(
        'FindFirstFile', #CASE SENSITIVE!!
        $True,
        $False,
        [System.Runtime.InteropServices.CharSet]::Unicode
    )
 
    $SetLastErrorCustomAttribute = New-Object Reflection.Emit.CustomAttributeBuilder(
        $DllImportConstructor,
        @('kernel32.dll'),
        $FieldArray,
        $FieldValueArray
    )
 
    $PInvokeMethod.SetCustomAttribute($SetLastErrorCustomAttribute)
    #endregion FindFirstFile METHOD
 
    #region FindNextFile METHOD
    $PInvokeMethod = $TypeBuilder.DefineMethod(
        'FindNextFile', #Method Name
        [Reflection.MethodAttributes] 'PrivateScope, Public, Static, HideBySig, PinvokeImpl', #Method Attributes
        [bool], #Method Return Type
        [Type[]] @(
            [IntPtr],
            [WIN32_FIND_DATA].MakeByRefType()
        ) #Method Parameters
    )
    $DllImportConstructor = [Runtime.InteropServices.DllImportAttribute].GetConstructor(@([String]))
    $FieldArray = [Reflection.FieldInfo[]] @(
        [Runtime.InteropServices.DllImportAttribute].GetField('EntryPoint'),
        [Runtime.InteropServices.DllImportAttribute].GetField('SetLastError')
        [Runtime.InteropServices.DllImportAttribute].GetField('ExactSpelling')
        [Runtime.InteropServices.DllImportAttribute].GetField('CharSet')
    )
 
    $FieldValueArray = [Object[]] @(
        'FindNextFile', #CASE SENSITIVE!!
        $True,
        $False,
        [System.Runtime.InteropServices.CharSet]::Unicode
    )
 
    $SetLastErrorCustomAttribute = New-Object Reflection.Emit.CustomAttributeBuilder(
        $DllImportConstructor,
        @('kernel32.dll'),
        $FieldArray,
        $FieldValueArray
    )
 
    $PInvokeMethod.SetCustomAttribute($SetLastErrorCustomAttribute)
    #endregion FindNextFile METHOD

    #region FindClose METHOD
    $PInvokeMethod = $TypeBuilder.DefineMethod(
        'FindClose', #Method Name
        [Reflection.MethodAttributes] 'PrivateScope, Public, Static, HideBySig, PinvokeImpl', #Method Attributes
        [bool], #Method Return Type
        [Type[]] @(
            [IntPtr]
        ) #Method Parameters
    )
    $DllImportConstructor = [Runtime.InteropServices.DllImportAttribute].GetConstructor(@([String]))
    $FieldArray = [Reflection.FieldInfo[]] @(
        [Runtime.InteropServices.DllImportAttribute].GetField('EntryPoint'),
        [Runtime.InteropServices.DllImportAttribute].GetField('SetLastError')
        [Runtime.InteropServices.DllImportAttribute].GetField('ExactSpelling')
    )
 
    $FieldValueArray = [Object[]] @(
        'FindClose', #CASE SENSITIVE!!
        $True,
        $True
    )
 
    $SetLastErrorCustomAttribute = New-Object Reflection.Emit.CustomAttributeBuilder(
        $DllImportConstructor,
        @('kernel32.dll'),
        $FieldArray,
        $FieldValueArray
    )
 
    $PInvokeMethod.SetCustomAttribute($SetLastErrorCustomAttribute)
    #endregion FindClose METHOD
    #endregion Methods
 
    #region Create Type
    [void]$TypeBuilder.CreateType()
    #endregion Create Type    
}

The pinvoke build is always the meatiest piece of code in my functions with the rest being fairly small.

One thing about this approach is that we have to use a UNC style when we supply the path, otherwise our attempts to go beyond the limit will fail. The problem is that we don’t want to have a user worrying about using a UNC path to run the command. Instead, I will accept the standard format of C:\ and then work to replace that with a UNC style such as \?\C:\ as shown in the code below.

If ($Path -notmatch '^[a-z]:|^\\\\') {
    $Path = Convert-Path $Path
}
If ($Path.Endswith('\')) {
    $SearchPath = "$($Path)*"
} ElseIf ($Path.EndsWith(':')) {
    $SearchPath = "$($Path)\*"
    $Path = "$($Path)\"
} ElseIf ($Path.Endswith('*')) {
    $SearchPath = $Path
} Else {
    $SearchPath = "$($Path)\*"
    $path = "$($Path)\"
}
If (-NOT $Path.StartsWith('\\')) {
    $SearchPath = "\\?\$($SearchPath)"
    $Path = \\?\$($Path)
}

image

Another thing to note is that you must append a wildcard (*) at the end of the path in order for it to accurately find files and folders using FindFirstFile() and FindNextFile().

I handle the depth by setting the value as the max integer if the parameter is not used.

If ($PSBoundParameters.ContainsKey('Recurse') -AND (-NOT $PSBoundParameters.ContainsKey('Depth'))) {
    $PSBoundParameters.Depth = [int]::MaxValue
    $Depth = [int]::MaxValue
}

Now we can attempt our first file/folder find using FindFirstFile.

#region Inititalize Data
$Found = $True    
$findData = New-Object WIN32_FIND_DATA 
#endregion Inititalize Data

$Handle = [poshfile]::FindFirstFile("$SearchPath",[ref]$findData)

The handle returned just helps us to determine whether this actually worked or not. Our actual data resides in our reference variable, $FindData which contains the Struct that we built.

image

This is the part where we start to translate our data into something a little more human readable. I will split up my next piece of code to better explain what is going on.

If ($Handle -ne -1) {
    While ($Found) {
        If ($findData.cFileName -notmatch '^(\.){1,2}$') {
            $IsDirectory =  [bool]($findData.dwFileAttributes -BAND 16)  
            $FullName = "$($Path)$($findData.cFileName)"
            $Mode = New-Object System.Text.StringBuilder                    
            If ($findData.dwFileAttributes -BAND [System.IO.FileAttributes]::Directory) {
                [void]$Mode.Append('d')
            } Else {
                [void]$Mode.Append('-')
            }
            If ($findData.dwFileAttributes -BAND [System.IO.FileAttributes]::Archive) {
                [void]$Mode.Append('a')
            } Else {
                [void]$Mode.Append('-')
            }
            If ($findData.dwFileAttributes -BAND [System.IO.FileAttributes]::ReadOnly) {
                [void]$Mode.Append('r')
            } Else {
                [void]$Mode.Append('-')
            }
            If ($findData.dwFileAttributes -BAND [System.IO.FileAttributes]::Hidden) {
                [void]$Mode.Append('h')
            } Else {
                [void]$Mode.Append('-')
            }
            If ($findData.dwFileAttributes -BAND [System.IO.FileAttributes]::System) {
                [void]$Mode.Append('s')
            } Else {
                [void]$Mode.Append('-')
            }
            If ($findData.dwFileAttributes -BAND [System.IO.FileAttributes]::ReparsePoint) {
                [void]$Mode.Append('l')
            } Else {
                [void]$Mode.Append('-')
            }
            $Object = New-Object PSObject -Property @{
                Name = [string]$findData.cFileName
                FullName = [string]($FullName).Replace('\\?\','')
                Length = $Null                       
                Attributes = [System.IO.FileAttributes]$findData.dwFileAttributes
                LastWriteTime = [datetime]::FromFileTime($findData.ftLastWriteTime)
                LastAccessTime = [datetime]::FromFileTime($findData.ftLastAccessTime)
                CreationTime = [datetime]::FromFileTime($findData.ftCreationTime)
                IsDirectory = [bool]$IsDirectory
                Mode = $Mode.ToString()
            }    
            If ($Object.IsDirectory) {
                $Object.pstypenames.insert(0,'System.Io.DirectoryInfo')
            } Else {
                $Object.Length = [int64]("0x{0:x}" -f $findData.nFileSizeLow)
                $Object.pstypenames.insert(0,'System.Io.FileInfo')
            }
            If ($PSBoundParameters.ContainsKey('Directory') -AND $Object.IsDirectory) {                            
                $ToOutPut = $Object
            } ElseIf ($PSBoundParameters.ContainsKey('File') -AND (-NOT $Object.IsDirectory)) {
                $ToOutPut = $Object
            }
            If (-Not ($PSBoundParameters.ContainsKey('Directory') -OR $PSBoundParameters.ContainsKey('File'))) {
                $ToOutPut = $Object
            } 
            If ($PSBoundParameters.ContainsKey('Filter')) {
                If (($ToOutPut.Name -like $Filter)) {
                    $ToOutPut
                }
            } Else {
                $ToOutPut
            }
            $ToOutPut = $Null

Here I am proceeding with taking the Struct and translating various parts into human readable data as well as adding a couple of other properties such as Mode to better duplicate how Get-ChildItem works. Also shown is where I make use of –Filter, –File and –Directory parameters where applicable.

            If ($Recurse -AND $IsDirectory -AND ($PSBoundParameters.ContainsKey('Depth') -AND [int]$Script:Count -lt $Depth)) {                        
                #Dive deeper
                Write-Verbose "Recursive"
                $Script:Count++
                $PSBoundParameters.Path = $FullName
                Get-ChildItemV2 @PSBoundParameters
                $Script:Count--
            }
        }
        $Found = [poshfile]::FindNextFile($Handle,[ref]$findData)
    }
    [void][PoshFile]::FindClose($Handle)
}

This portion will continue to process data as it comes in using FindNextFile() by referencing the Handle that we had from the previous command. If we are performing a recursive query, that is handles as well by calling the function again with our previously used parameters. If nothing is left to do, we will use FindClose to clean up after ourselves.

Here is an example of the function in use vs. using Get-ChildItem to go beyond the MAX_PATH limit.

Using Get-ChildItem

image

The image is a little small, but it basically says that it cannot look at the contents of my very long directory because it exceeds the maximum number of characters for a directory (248). Pretty much a wash if that happens, right? Now let’s try this using Get-ChildItem2 and see what happens.

Using Get-ChildItem2

image

Looks like it worked like a champ! I was able to easily avoid the long path issue and as you can see, we can see all of the folders and files (note that I used the –Recurse parameter here) and as an added bonus, I am displaying their character count here.

Bonus!

With this function, we can also take a look at the named pipes being used on our local system.

Get-ChildItem2 –Path \\.\pipe

image

That is it for building your own function to work around the MAX_PATH issue that we are used to seeing when working with long paths in the file system. While there are other ways to use this and other projects out there, it is always fun to build your own tool to accomplish a goal!

You can download this function from the link below.

Download Get-ChildItem2

https://gallery.technet.microsoft.com/scriptcenter/Get-ChildItemV2-to-list-29291aae

Posted in powershell | Tagged , , , , | 3 Comments

Updated Get-Certificate Function

It’s been a while since I first published my Get-PKICertificates function and talked about it on Hey, Scripting Guy!. In fact, that was my first guest blog that I had ever done which was an amazing opportunity that I have been fortunate to do multiple times since then. Back during that time I talked about and demoed my function to get certificates that were about to expire, which is fine and all, but that code is pretty old and was in need of a makeover.

After finally getting some free time, not only did I re-write the code, but I decided to take it a step further by removing the parameters for filtering for expiring certificates and instead just adding a ‘days until expiration’ property as well as adding a switch to include all certificates that have been archived.

Oh yea, I also changed the name from Get-PKICertificates to Get-Certificate because number 1: don’t use a plural in the noun and having PKI in the name really didn’t serve much purpose.

Because I didn’t want to worry about the user trying to filter the certificates after using the function, I went ahead and added parameters to filter the certificates by Subject, Issuer and Thumbprint. Using these parameters, I can build a filter string that allows the code to only display what the user is looking for.

Here is an example of using the function to see when the certificates will expire:

Get-Certificate | Select Subject, Thumbprint, ExpiresIn

SNAGHTML2cb4d457[5]

Filtering using the parameters:

Get-Certificate -Issuer *boe*|Select Issuer, Thumbprint

image

Get-Certificate -Subject *Soon*

image

Get-Certificate -Thumbprint 9463F15498*

image

The filtering is done using Where-Object internally in the function by building out an arraylist containing the filters and then concatenating them using a –Join keyword.

$WhereList = New-Object System.Collections.ArrayList
If ($PSBoundParameters.ContainsKey('Issuer')) {
    [void]$WhereList.Add('$_.Issuer -LIKE $Issuer')
}
If ($PSBoundParameters.ContainsKey('Subject')) {
    [void]$WhereList.Add('$_.Subject -LIKE $Subject')
}
If ($PSBoundParameters.ContainsKey('Thumbprint')) {
    [void]$WhereList.Add('$_.Thumbprint -LIKE $Thumbprint')
}
If ($WhereList.count -gt 0) {
        $Where = [scriptblock]::Create($WhereList -join ' -AND ')
        Write-Debug "WhereBlock: $($Where)"
}

You can also view any of the archived certificates using the –IncludeArchived parameter as well. Unfortunately, I did not have any archived at this time to demo.

You can download the updated function via the link below.

Download Get-Certificate

https://gallery.technet.microsoft.com/scriptcenter/a2a500e5-1dd2-4898-9721-ed677399679c

Posted in powershell | Tagged , , , | 1 Comment

Quick Hits: Adding a Hyperlink to Excel Spreadsheet

I had a need a while back to add a few hyperlinks to a couple different excel cells that could be clicked on to take the user either to another portion of the excel spreadsheet or to open up a link to another website.

While you may think that it can be set on the cell itself, it actually resides in a different location (but you still need to remember the cell that you want to put this on).

Rather than go over all of the concepts of working with Excel, I will instead point you to some older articles that I wrote: https://learn-powershell.net/tag/excel/

Let’s assume that we have a spreadsheet already with some data in it already and I need to add some hyperlinks.

image

Yea, it’s not exactly the most useful information but it will work just fine to show this off. My worksheet is saved to $ServerInfoSheet and with that I am going to dig into the Hyperlinks property and make use of the Add method.

$serverInfoSheet.Hyperlinks.Add

image

Now what exactly do these parameters mean? Great question! Let’s go ahead and answer this so we can move forward with adding our hyperlink.

image

Assuming that you want to keep the same text that is already in your cell, you will want to make sure that you supply the cell.text property so that doesn’t change.

$serverInfoSheet.Hyperlinks.Add(
    $serverInfoSheet.Cells.Item(1,1),
    "http://google.com",
    "?gws_rd=ssl#safe=off&q=PowerShell",
    "Give me a Google PowerShell Search!",
    $serverInfoSheet.Cells.Item(1,1).Text
) | Out-Null

I wanted to make full use of each parameter for the sake of demoing this. The first parameter is the cell that this will be adding the hyperlink to while the second parameter is the hyperlink. Because I wanted to show off everything, I added a sub-link (3rd parameter) which will show take us to the search results for PowerShell. The 4th parameter is a little tool tip window that displays a message when you hover over the hyperlink while the last parameter is the actual text which is in the cell (remember that we wanted to keep this to what it already is).

The result is that we now have a hyperlink in place of the plain text in the cell, which in this case will take us to the PowerShell search results in Google.

image

Maybe we want to reference another sheet in the workbook. All we need to do is adjust the hyperlink to point to that particular cell in another workbook.

$serverInfoSheet.Hyperlinks.Add(
    $serverInfoSheet.Cells.Item(2,1),
    "",
    "MoreInformation!A1",
    "Take me another worksheet!",
    $serverInfoSheet.Cells.Item(2,1).Text
) | Out-Null

Note what I used for the 3rd parameter that involves specifying the worksheet name followed by the cell number that the hyperlink will take you to. I also do not have a primary hyperlink for parameter 2. The result is a hyperlink to another worksheet.

imageimage

Looking at the hyperlinks property, we can see all of the hyperlinks that are available/created.

$serverInfoSheet.Hyperlinks

image

Bonus: Email Address

We can even have an email address added that will open up to your default mail client to send an email with a subject.

$serverInfoSheet.Hyperlinks.Add(
    $serverInfoSheet.Cells.Item(3,1),
    "mailto:[email protected]?subject=PowerShell Rocks",
    "",
    "Send an email to Boe",
    $serverInfoSheet.Cells.Item(2,1).Text
) | Out-Null

image

Now if we look at the hyperlinks again, we can see both the email address and the subject being display. The trick here is that you use the primary address (2nd parameter) which requires us to prepend mailto: before adding the email address, then following that with a question mark (?) and adding subject: followed by the subject and making sure that we specify the subject. After that, we just have to run the code!

image

So there you have it, a quick way to add some hyperlinks to your Excel spreadsheets using a little bit of PowerShell to hook into the Excel Com object.

Posted in powershell | Tagged , , | Leave a comment

Windows PowerShell MVP for Another Year!

image

I received a great email this morning with the news that I have been renewed for my 3rd MVP award in Windows PowerShell! This is a great honor and I never expect to get this as we go through the same type of evaluations as everyone else who is nominating for this and anything could happen (there are some amazing members of the community out there).

I always strive to put out some great content that I hope people find useful as well as working on my projects, notably PoshRSJob, to provide the community some great things to work with. I have other ideas for new things coming down the pipeline and am looking forward to doing some more speaking gigs, such as one in November with the Omaha user group that I am the co-lead for talking about Pester. I also hope to do a couple other speaking gigs during the course of this year and next year and looking forward to talking PowerShell with all of the great members of the community who not only teach me some amazing things with PowerShell and also allow me the opportunity to show some of the things that I do with PowerShell. It is because of the community that I enjoy talking about PowerShell and that makes you the reason why I received this award. Thank you!

Posted in powershell | Tagged , | 1 Comment