Update Hyperlink Field in SharePoint with PowerShell

Recently was asked to update the hyperlink field in a SharePoint list. The list had thousands of links pointing to a server we were moving, so the URL in the list needed to updated to reflect the new location. The only options I could think of, were to open SQL Server and do find/update against the content database(s). Well, Microsoft doesn’t like up playing with the SharePoint databases. So I cracked open PowerShell and went to town trying to figure this out.

#Add-PSSnapin Microsoft.SharePoint.PowerShell

$siteUrl = "http://sharepoint/SiteCollection/SiteName"
$webName = “SiteName”
$listName = "Name of your list"
$spSite = new-object Microsoft.SharePoint.SPSite($siteurl)
$spWeb = $spSite.OpenWeb($webName)
$spList = $spWeb.Lists[$listName]

			foreach($Item in $spList.Items )
			{
				$ofldurl= new-object Microsoft.SharePoint.SPFieldUrlValue($Item["URL"])

				$ofldurl.URL = $ofldurl.URL.Replace("Nacho", "Taco")
				$ofldurl.Description = $ofldurl.Description.Replace("Nacho", "Taco")

				$item["URL"] = $ofldurl
				$item.update()
			}

$spWeb.Dispose()

What I’m doing here is replacing part of the URL string with a new word. The script is looking for the string Nacho and replacing it with Taco.

**** update ****
After migrating from SharePoint / WSS 2007 to SharePoint 2010, we started to notice some of our URLs were messed up. Web parts ( Page Viewer) and internal hyperlinks were pointing to an incorrect location.

example:
http://sharepoint/crmsp/customers/sitename/_layouts/1033/mngsubwebs.aspx?view=sites

Notice the 1033, no bueno!

Using the script above, i was able to update the links.

#Add-PSSnapin Microsoft.SharePoint.PowerShell

$siteUrl = "http://sharepoint/SiteCollection/SiteName"
$webName = “SiteName”
$listName = "Name of your list"
$spSite = new-object Microsoft.SharePoint.SPSite($siteurl)
$spWeb = $spSite.OpenWeb($webName)
$spList = $spWeb.Lists[$listName]

			foreach($Item in $spList.Items )
			{
				$ofldurl= new-object Microsoft.SharePoint.SPFieldUrlValue($Item["URL"])

				$ofldurl.URL = $ofldurl.URL.Replace("1033/", "")
				$ofldurl.Description = $ofldurl.Description.Replace($ofldurl.Description, $ofldurl.Description)

				$item["URL"] = $ofldurl
				$item.update()
			}

$spWeb.Dispose()

In SharePoint 2016, the Description property appears broken?
This is the only way I could manage to set the URL and display text:

$newItem[“LinkField”] = "http://taco.com, taco2"

The page you selected contains a list that does not exist

Error_pagetitle
An error occurred while getting the items.
List does not exist.
The page you selected contains a list that does not exist. It may have been deleted by another user.
ErrorPageRequestGuid

This was one crazy mess!

First I tried digging around in SharePoint Designer with no luck. In Designer, when I clicked on Lists and Libraries, nothing would appear in the right window. Odd. When I clicked on All Files, all my items would appear. But, if I tried to right click on the masterpage folder I would receive and error. Then… I noticed when I tried to open the Documents library in my site it would say something like the webpage does not exist.

Onto the ULS Log Viewer.
Started up the Viewer, then made SharePoint toss that error again.
Looked in the Viewer and found that SharePoint error-ed out because of the Documents library on the site. No idea why this single library was the issue. I couldn’t open the library, so I tried to attack it with PowerShell.

 $site = new-object Microsoft.SharePoint.SPSite("http://sharepointed.com/")
 $web = $site.rootweb
 $list = $web.Lists["Documents"]
 $list.Delete
 #do a .Dispose()

Ran this code, then went back into SharePoint, and tried to create a new Document Library. Shaaazam, it worked!

It worked but my Documents library was still showing up in the _layouts/viewlsts.aspx view.

Still working on this issue.

*one note.
For some reason, if I tried to access the site from a PC without SilverLight, I was able to create new Libraries.

PowerShell SharePoint User Access

Let me start this off by saying I’m new to PowerShell AND I’m sure someone could sum up my code in one line of code.

I was asked to provide a list of all users in a SharePoint Site Collection / Web App. The person asking this, was wanting to see each user, the site and list they had access to. More or less I needed to iterate through all the sites, loop on the user, loop again on the site, then loop on the lists.

So the layout I could provide was:

Site Collection
User Name
Site
List

I’m outputting the results to a file name UserAccess.txt on the C:\ drive.

[System.Reflection.Assembly]::Load(“Microsoft.SharePoint, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c”)
[System.Reflection.Assembly]::Load(“Microsoft.SharePoint.Portal, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c”)

$spsite=[Microsoft.SharePoint.SPSite]("http://sharepoint/")

out-file c:\UserAccess.txt -append

foreach ($Site in $spsite.AllWebs)
{
    "Site Collection Name:  $Site" | out-file c:\UserAccess.txt -append
    foreach ($User in $Site.SiteUsers)
    {
        "--User Name: $User" | out-file c:\UserAccess.txt -append
        foreach ($Site in $spsite.AllWebs)
        {
					$SitePermissions = $Site.Permissions
					foreach($mySitePermission in $SitePermissions)
					{
						if($User.ID -eq $mySitePermission.Member.ID)
						{
                            "----Site Name: $Site" | out-file c:\UserAccess.txt -append
						}
					}

                    foreach($myList in $Site.lists)
        			{

        					$myListPermissions = $myList.Permissions
        					foreach($myListPermission in $myListPermissions)
        					{
        						if($User.ID -eq $myListPermission.Member.ID)
        						{
                                    "------List Name: $MyList" | out-file c:\UserAccess.txt -append
        						}
        					}
        			}

                 $Site.Dispose()
			}
            $Site.Dispose()
        }
    }

Let me know if you have any questions or need any help.

How to encode and to decode a file attachment Visual Basic

Microsoft was nice enough to post a code example in C#, but I’m lazy and haven’t moved away from Visual Basic.

How to encode and to decode a file attachment programmatically by using Visual C# Visual Basic in InfoPath 2010 or in InfoPath 2007

C# example:
http://support.microsoft.com/kb/2517906

VB examples:

decode.vb

Imports System
Imports System.IO
Imports System.Text

Namespace InfoPathAttachmentEncoding
    '''
<summary> ''' Decodes a file attachment and saves it to a specified path.
 ''' </summary>
    Public Class InfoPathAttachmentDecoder
        Private Const SP1Header_Size As Integer = 20
        Private Const FIXED_HEADER As Integer = 16

        Private fileSize As Integer
        Private attachmentNameLength As Integer
        Private attachmentName As String
        Private m_decodedAttachment As Byte()

        '''
<summary> ''' Accepts the Base64 encoded string
 ''' that is the attachment.
 ''' </summary>
        Public Sub New(ByVal theBase64EncodedString As String)
            Dim theData As Byte() = Convert.FromBase64String(theBase64EncodedString)
            Using ms As New MemoryStream(theData)
                Dim theReader As New BinaryReader(ms)
                DecodeAttachment(theReader)
            End Using
        End Sub

        Private Sub DecodeAttachment(ByVal theReader As BinaryReader)
            'Position the reader to obtain the file size.
            Dim headerData As Byte() = New Byte(FIXED_HEADER - 1) {}
            headerData = theReader.ReadBytes(headerData.Length)

            fileSize = CInt(theReader.ReadUInt32())
            attachmentNameLength = CInt(theReader.ReadUInt32()) * 2

            Dim fileNameBytes As Byte() = theReader.ReadBytes(attachmentNameLength)
            'InfoPath uses UTF8 encoding.
            Dim enc As Encoding = Encoding.Unicode
            attachmentName = enc.GetString(fileNameBytes, 0, attachmentNameLength - 2)
            m_decodedAttachment = theReader.ReadBytes(fileSize)
        End Sub

        Public Sub SaveAttachment(ByVal saveLocation As String)
            Dim fullFileName As String = saveLocation
            If Not fullFileName.EndsWith(Path.DirectorySeparatorChar.ToString()) Then
                fullFileName += Path.DirectorySeparatorChar
            End If

            fullFileName += attachmentName

            If File.Exists(fullFileName) Then
                File.Delete(fullFileName)
            End If

            Dim fs As New FileStream(fullFileName, FileMode.CreateNew)
            Dim bw As New BinaryWriter(fs)
            bw.Write(m_decodedAttachment)

            bw.Close()
            fs.Close()
        End Sub

        Public ReadOnly Property Filename() As String
            Get
                Return attachmentName
            End Get
        End Property

        Public ReadOnly Property DecodedAttachment() As Byte()
            Get
                Return m_decodedAttachment
            End Get
        End Property
    End Class
End Namespace

encode.vb

Imports System
Imports System.IO
Imports System.Text
Imports System.Security.Cryptography
Imports InfoPathAttachmentEncoding

Namespace InfoPathAttachmentEncoding
    '''
<summary> ''' InfoPathAttachment encodes file data into the format expected by InfoPath for use in file attachment nodes.
 ''' </summary>
    Public Class InfoPathAttachmentEncoder
        Private base64EncodedFile As String = String.Empty
        Private fullyQualifiedFileName As String

        '''
<summary> ''' Creates an encoder to create an InfoPath attachment string.
 ''' </summary>
        '''
        Public Sub New(ByVal fullyQualifiedFileName As String)
            If fullyQualifiedFileName = String.Empty Then
                Throw New ArgumentException("Must specify file name", "fullyQualifiedFileName")
            End If

            If Not File.Exists(fullyQualifiedFileName) Then
                Throw New FileNotFoundException("File does not exist: " + fullyQualifiedFileName, fullyQualifiedFileName)
            End If

            Me.fullyQualifiedFileName = fullyQualifiedFileName
        End Sub

        '''
<summary> ''' Returns a Base64 encoded string.
 ''' </summary>
        ''' String
        Public Function ToBase64String() As String
            If base64EncodedFile <> String.Empty Then
                Return base64EncodedFile
            End If

            ' This memory stream will hold the InfoPath file attachment buffer before Base64 encoding.
            Dim ms As New MemoryStream()

            ' Obtain the file information.
            Using br As New BinaryReader(File.Open(fullyQualifiedFileName, FileMode.Open, FileAccess.Read, FileShare.Read))
                Dim fileName As String = Path.GetFileName(fullyQualifiedFileName)

                Dim fileNameLength As UInteger = CUInt(fileName.Length) + 1

                Dim fileNameBytes As Byte() = Encoding.Unicode.GetBytes(fileName)

                Using bw As New BinaryWriter(ms)
                    ' Write the InfoPath attachment signature.
                    bw.Write(New Byte() {&HC7, &H49, &H46, &H41})

                    ' Write the default header information.
                    bw.Write(CUInt(&H14))
                    ' size
                    bw.Write(CUInt(&H1))
                    ' version
                    bw.Write(CUInt(&H0))
                    ' reserved
                    ' Write the file size.
                    bw.Write(CUInt(br.BaseStream.Length))

                    ' Write the size of the file name.
                    bw.Write(CUInt(fileNameLength))

                    ' Write the file name (Unicode encoded).
                    bw.Write(fileNameBytes)

                    ' Write the file name terminator. This is two nulls in Unicode.
                    bw.Write(New Byte() {0, 0})

                    ' Iterate through the file reading data and writing it to the outbuffer.
                    Dim data As Byte() = New Byte(64 * 1024 - 1) {}
                    Dim bytesRead As Integer = 1

                    While bytesRead > 0
                        bytesRead = br.Read(data, 0, data.Length)
                        bw.Write(data, 0, bytesRead)
                    End While
                End Using
            End Using

            ' This memorystream will hold the Base64 encoded InfoPath attachment.
            Dim msOut As New MemoryStream()

            Using br As New BinaryReader(New MemoryStream(ms.ToArray()))
                ' Create a Base64 transform to do the encoding.
                Dim tf As New ToBase64Transform()

                Dim data As Byte() = New Byte(tf.InputBlockSize - 1) {}
                Dim outData As Byte() = New Byte(tf.OutputBlockSize - 1) {}

                Dim bytesRead As Integer = 1

                While bytesRead > 0
                    bytesRead = br.Read(data, 0, data.Length)

                    If bytesRead = data.Length Then
                        tf.TransformBlock(data, 0, bytesRead, outData, 0)
                    Else
                        outData = tf.TransformFinalBlock(data, 0, bytesRead)
                    End If

                    msOut.Write(outData, 0, outData.Length)
                End While
            End Using

            msOut.Close()

            Return InlineAssignHelper(base64EncodedFile, Encoding.ASCII.GetString(msOut.ToArray()))
        End Function
        Private Shared Function InlineAssignHelper(Of T)(ByRef target As T, ByVal value As T) As T
            target = value
            Return value
        End Function
    End Class
End Namespace

Imports

Imports InfoPathAttachmentEncoding

btnAttach_Clicked

        Public Sub btnAttach_Clicked(ByVal sender As Object, ByVal e As ClickedEventArgs)

            Dim ns As XmlNamespaceManager = Me.NamespaceManager
            Dim xnMain As XPathNavigator = Me.MainDataSource.CreateNavigator()
            Dim xnAttNode As XPathNavigator = xnMain.SelectSingleNode("/my:myFields/my:theAttachmentField", ns)
            Dim xnFileName As XPathNavigator = xnMain.SelectSingleNode("/my:myFields/my:theAttachmentName", ns)

            'Obtain the text of the filename node.
            Dim fileName As String = xnFileName.Value
            If fileName.Length > 0 Then
                'Encode the file and assign it to the attachment node.
                Dim myEncoder As New InfoPathAttachmentEncoder(fileName)

                'Check for the "xsi:nil" attribute on the file attachment node and remove it
                'before setting the value to attach the filerRemove the "nil" attribute
                If xnAttNode.MoveToAttribute("nil", "http://www.w3.org/2001/XMLSchema-instance") Then
                    xnAttNode.DeleteSelf()
                End If

                'Attach the file
                xnAttNode.SetValue(myEncoder.ToBase64String())
            End If
        End Sub

btnSave_Clicked

        Public Sub btnSave_Clicked(ByVal sender As Object, ByVal e As ClickedEventArgs)
            'Create an XmlNamespaceManager
            Dim ns As XmlNamespaceManager = Me.NamespaceManager

            'Create an XPathNavigator object for the Main data source
            Dim xnMain As XPathNavigator = Me.MainDataSource.CreateNavigator()

            'Create an XPathNavigator object for the attachment node
            Dim xnAttNode As XPathNavigator = xnMain.SelectSingleNode("/my:myFields/my:theAttachmentField", ns)

            'Obtain the text of the node.
            Dim theAttachment As String = xnAttNode.Value
            If theAttachment.Length > 0 Then
                Dim myDecoder As New InfoPathAttachmentDecoder(theAttachment)
                myDecoder.SaveAttachment("----------ENTER THE PATH HERE-----------")
            End If

        End Sub

Calculating Age or Anniversary Years

Ever wanted to calculate someones age or how long they have been with a company?

1. Create a new List.

2. Create a new column, set the type to Date and Time, with the Date and Time Format set to Date Only.

 

3. Create a column and name it Today, data type of Single line of text.

 

4. Create another column and name it Anniversary, type is Calculated (calculation based on other columns), set the data type to Number, and set the decimal place to 0.

The formula is:

=YEAR(Today)-YEAR(HireDate)-IF(OR(MONTH(Today)<MONTH(HireDate),AND(MONTH(Today)=MONTH(HireDate),DAY(Today)<DAY(HireDate))),1,0)

5. Now you want to delete your Today column that you just created (trust me, just try it).

6. Navigate back to your list and try entering a few values.  In my example, Today is equal to 8/18/2011.

Unable to load workflow actions from the server

Roll into work and a coworker alerts me to the fact that our SharePoint 2010 workflows are not flowing.  When I tried to open any workflow, I was receiving this error:
Unable to load workflow actions from the server….

unable to load workflow actions from the server

I cracked open Fiddler, opened SharePoint Designer, then tried to open a workflow.

On the left side of Fiddler, you will see a bunch of actions, look for one line that turns red and click on it.  In the right window, you should see the error.

example:
soap:ServerException of type ‘Microsoft.SharePoint.SoapServer.SoapServerException’ was thrown.Failed to find the XML file at location ‘14\Template\Features\MissingFeature\feature.xml‘0x80131600……..

So I opened one of my WFE servers and navigated to the folder:
C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\TEMPLATE\FEATURES\MissingFeature

Sure enough, the feature folder in question was missing. To get the missing feature folder, I opened my app server and copied over the missing folder to my WFE servers. No iisreset needed.

Search Web Part Missing

After setting up a new Search Center, I tried to add the Refinement Panel web part, but was unable to locate it.

Navigate to your Site Collection and enable Search Server Web Parts.

From Microsoft:
To activate the Search Server Web Part feature

To open the Site Settings page for the top-level (root) site of the upgraded site collection, append /_layouts/settings.aspx to the root site’s URL, as follows:

http://RootSiteURL/_layouts/settings.aspx

In the Site Collection Administration section of the Site Settings page, click Site collection features.

For Search Server Web Parts, click Activate.

Link for more detail:
http://msdn.microsoft.com/en-us/library/ff512796.aspx