SharePoint 2010 – Drop Off Library Not Indexed For Searching

After patching our SharePoint 2010 farm to SP1 & the Feb 2013 CU, we noticed that our drop off library was no longer being indexed for searching…. The configured crawls were taking place, but new items were no longer showing in the search results for some reason. Under Library Settings > Advanced Settings > Search we could see that the option “Allow items from this document library to appear in search results?” was set to ‘No’. We turned it back on, ran a crawl & items now appeared again.

Allow items from this document library to appear in search results?

Allow items from this document library to appear in search results?

When the users reported that the issue had recurred, we checked this option & it was set back to ‘No’. So we turned it back to ‘Yes’, and then ran a crawl again. An hour later the same thing happened again. We found this post from P Griffiths which indicated that the Content Organizer Timer Job was affecting this setting…. This tied in perfectly with the behavior we were seeing, as the timer job is running every 30 minutes turning this setting back off.

We couldn’t find much information about this online, but from what we could see this was by design as a Drop Off Library is not meant to be configured to be indexed like this. As the crawls are running every 10 minutes and this job is disabling indexing every 30 minutes, I needed a way of quickly forcing this back on without manual intervention.

We came up with the below solution…. Our SharePoint developer wrote this powershell script:

Add-PSSnapIn Microsoft.SharePoint.PowerShell
$site=Get-SPSite ""
$docLibrary =$web.Lists["Drop Off Library"]
# Change the advanced settings
$docLibrary.NoCrawl = $false;

And then a batch file to call the script:

powershell -file "C:\\scripts\\DropOffCrawl.ps1"

And scheduled as a task to run every 5 minutes on the Web Front End server, forcing the option back on in time for the next crawl each time. Biggles.

Posted in SharePoint | Tagged , , , | Leave a comment

Disabling Safari Browser Via Group Policy

Due to the implementation of a proxy server a while back, we had to lock down a previously open environment in order to ensure all user’s web traffic was forced through the proxy server. This presented a problem of it’s own, controlling Safari & it’s proxy settings through Group Policy. Chrome, Firefox and IE are all relatively simple, but for love nor money I wasn’t able to find a way of group policying Safari. My next option was to disable Safari browser via group policy.

As the fleet of computers are Windows 7 Professional, I wasn’t able to use Applocker… Initially, blocking Safari.exe from running on a machine via a Software Restriction Policy works – you get a denied message when trying to launch the application. However renaming the exe to Safari1.exe circumvents this control instantly.

Then I noticed that when you launch Safari, a second process was spawns called WebKit2WebProcess.exe. I tried renaming this .exe and suddenly Safari stopped communicating with the internet . Now it loads Safari, and just refuses to do anything from therein.

Blocking WebKit2WebProcess.exe at the Group Policy level is simple, add a disallow to the C:\\Program Files\\Common Files\\Apple\\Apple Application Support directory, via:

User Configuration > Windows Settings > Security Settings > Software Restriction Policies > Additional Rules

Adding in a New Path Rule, disallowing access to C:\\Program Files\\Common Files\\Apple\\Apple Application Support

EDIT – Having found a couple of users running Safari recently, I discovered that newer versions of the application have moved this file to C:\\Program Files\\Safari\\Apple Application Support


Posted in Group Policy | 1 Comment

Macro Enabled Purchase Order Template

There is a newer version of this template now available, here:

I was asked to come up with a simple purchase order system for our team to use, which would hopefully automate the process a little. We needed:

  • Unique purchase order number on each request
  • Reduce the number of manual steps required to get approval from line manager
  • Provide centralised logging & archive of purchase orders raised

I rehashed a bit of the code from one of my previous posts (Word VBA Macro > Create Doc to Email), and borrowed a few bits from other code – a download is available below. Tabs 2, 3 and 4 are just plain text entries, adding more values to these lists will make them available on the main sheet.

Download PurchaseOrderTemplate.rar (160kb): 



You will also need to add a reference to the Microsoft Office 14.0 Object Library from within Excel, and enable macros for this to work. Hope this template or some part of the code is useful to you. Row 43 & column i can be hidden from the Master spreadsheet, I have left them visible as you may wish to amend the formulas

The Process Explained:

  • The user opens Purchase Order Master.xlsx. The PO number automatically increments by one & the file saves
  • User enters the details as required to the form. Once completed, the user his the Generate Purchase Order button
  • Email is generated & a PDF copy attached. Email is addressed To: {Approval field}; Cc: {Requested By field}
  • If there is a value inserted to the Service Request field, then our address is CC’d to the email, and the SR# is added to the subject line for email integration
  • The PDF is written to disk, using the variables from the spreadsheet & today’s date/time for the filename to ensure no accidental file overwrites
  • The summary on row 43 is copied & pasted to the Purchase Order Log.xlsx sheet
  • The purchase order is copied as a range & pasted to the email ready to send
  • User closes the workbook without saving changes (leaving the blank template ready for other staff)
  • In order to raise two consecutive PO’s, simply hit the ‘Reset Fields’ button to wipe input data, and increment to the next PO number

You should then be presented with your PO request email, ready to send:


And your log spreadsheet should have a new row inserted for the current Purchase Order:


The Macros Explained:

Workbook_Open, inserted to ThisWorkbook. Increments the Purchase Order number in cell B6 by 1 & saves the file, whenever the file is opened. This is to prevent duplicate purchase order numbers being used, occasionally one will be skipped but as long as there are no duplicates, then it’s fine:

Private Sub Workbook_Open()
Range("B6") = Range("B6") + 1
End Sub

Mandatory_fields: the ‘Generate’ button is linked directly to this macro – Assuming all the specified cells have values, it will call the Mail_Sheet_Outlook_Body macro in order to continue:

Sub Mandatory_fields()
'Check Mandatory Fields
 If Range("B10").Value = "" Then
 MsgBox "'Supplier' is a mandatory field...", vbOKOnly, "Required Field"
 Exit Sub
 ElseIf Range("B8").Value = "" Then
 MsgBox "'Cost Code' is a mandatory field...", vbOKOnly, "Required Field"
 Exit Sub
 ElseIf Range("B22").Value = "" Then
 MsgBox "'Requested By' is a mandatory field...", vbOKOnly, "Required Field"
 Exit Sub
 ElseIf Range("B24").Value = "" Then
 MsgBox "'Approval' is a mandatory field...", vbOKOnly, "Required Field"
 Exit Sub
 End If

 'If all required fields are met, then process main macro & generate documentation
 Call Mail_Sheet_Outlook_Body
 End Sub

Mail_Sheet_Outlook_Body: The main portion of the macro – Sets all the required cell values to strings which are then used to create the email To:, CC:, and Subject, file name etc, generates the PDF output locally & dumps a copy into the Archive folder. Brings up an Outlook window & attaches the PDF file, and inserts the email addresses etc:

Sub Mail_Sheet_Outlook_Body()
    Dim rng As Range
    Dim OutApp As Object
    Dim OutMail As Object
    Dim MailSub As String
    Dim MailSub2 As String
    Dim Supplier As String
    Dim PONumber As String
    Dim ToAddress As String
    Dim CCAddress As String
    Dim CCAddress2 As String
    Dim CCAddress3 As String
    Dim SRNumber As String
    Dim fname As String
    Dim strdata As String
    Dim sSource As String
    Dim sDestination As String
        With Application
        .EnableEvents = False
        .ScreenUpdating = False
    End With

  Set rng = Nothing
    Set rng = Range("A1:E24")
    ToAddress = Range("B24")
    CCAddress = Range("B22")
    CCAddress2 = Range("i10")
    CCAddress3 = CCAddress & "; " & CCAddress2
    Supplier = Range("B10")
    PONumber = Range("I4")
    SRNumber = Range("e6")
    MailSub2 = "New " & Supplier & " Purchase Order Raised: " & PONumber & " SR: #" & SRNumber
    todaydate = Format(Date, "d-mmm-yy")
    nowtime = Format(Time, "hhmm")
    fname = "PO#" & PONumber & "_-" & todaydate & "_" & nowtime & "(" & Supplier & ")"

'Generate PDF document to c:\
    strdata = "c:\Purchase Orders\" & fname & ".pdf"
    ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
    strdata, Quality:=xlQualityStandard, _
    IncludeDocProperties:=False, IgnorePrintAreas:=False, OpenAfterPublish:= _

'Create Mail & attach PDF
Set oOutlookApp = GetObject(, "Outlook.Application")
 If Err <> 0 Then
 Set oOutlookApp = CreateObject("Outlook.Application")
 End If

'Create a new message
Set oItem = oOutlookApp.CreateItem(olMailItem)
 With oItem
    .To = ToAddress
    .CC = CCAddress3
    .Subject = MailSub2
    .HTMLBody = RangetoHTML(rng)

'Bring up new mail window

'Add attachment
 oItem.Attachments.Add strdata

'Move PDF Document to network drive
    sSource = strdata
    sDestination = "c:\Purchase Orders\Archive\" & fname & ".pdf"
    Set fs = CreateObject("Scripting.FileSystemObject")
    fs.movefile sSource, sDestination

 'Cleanup, baby
    Set OutMail = Nothing
    Set OutApp = Nothing

End With
End Sub

RangetoHTML – Copies the worksheet range to HTML:

Function RangetoHTML(rng As Range)
    Dim fso As Object
    Dim ts As Object
    Dim TempFile As String
    Dim TempWB As Workbook
    TempFile = Environ$("temp") & "/" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm"

'Copy the range and create a new workbook to past the data in
    Set TempWB = Workbooks.Add(1)
    With TempWB.Sheets(1)
        .Cells(1).PasteSpecial Paste:=8
        .Cells(1).PasteSpecial xlPasteValues, , False, False
        .Cells(1).PasteSpecial xlPasteFormats, , False, False
        Application.CutCopyMode = False
        On Error Resume Next
        .DrawingObjects.Visible = True
        On Error GoTo 0
    End With

'Publish the sheet to a htm file
    With TempWB.PublishObjects.Add( _
         SourceType:=xlSourceRange, _
         Filename:=TempFile, _
         Sheet:=TempWB.Sheets(1).Name, _
         Source:=TempWB.Sheets(1).UsedRange.Address, _
        .Publish (True)
    End With

'Read all data from the htm file into RangetoHTML
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
    RangetoHTML = ts.ReadAll
    RangetoHTML = Replace(RangetoHTML, "align=center x:publishsource=", _
                          "align=left x:publishsource=")

'Close TempWB
    TempWB.Close savechanges:=False

'Delete the htm file used in this function
    Kill TempFile

    Set ts = Nothing
    Set fso = Nothing
    Set TempWB = Nothing

Call UpDateLog
End Function

UpDateLog: Opens the Purchase Order Log.xlsx file and inserts the details of the current row into the spreadsheet & saves:

Sub UpDateLog()
    Dim wb As Workbook, wbTemp As Workbook
    Dim ws As Worksheet, wsTemp As Worksheet
    Dim lastRow As Long

'Setting source workbook
    Set wb = ThisWorkbook
    Set ws = wb.Sheets("Template")

'Setting destination workbook
    Set wbTemp = Workbooks.Open("c:\Purchase Orders\Purchase Order Log.xlsx")
    Set wsTemp = wbTemp.Sheets("Sheet1")

'Paste to next row as values to preserve data
    lastRow = wsTemp.Range("I" & Rows.Count).End(xlUp).Row + 1
    wsTemp.Range("I" & lastRow).PasteSpecial Paste:=xlValues
    Application.CutCopyMode = False

    wbTemp.Close savechanges:=True
    Set wb = Nothing: Set wbTemp = Nothing
    Set ws = Nothing: Set wsTemp = Nothing

End Sub

ResetFields: Wipes all user input data, setting the form back to default. Also runs the PO number increment code, in order to prevent one user raising two separate purchase orders with the same log number:

Sub ResetFields()
' ResetFields Macro

    Range("B6") = Range("B6") + 1
End Sub

Posted in Macro, MS Excel | 10 Comments

Adding Local User Accounts To Sharepoint 2010

Today I was pulled into a request I hadn’t come across before, Adding Local User Accounts To Sharepoint 2010 in a Standalone SharePoint installation. The standalone box is for a corporate team-building exercise, and is not a domain machine:

  • SharePoint is installed in a Standalone installation
  • Several local user accounts created on the box for team members to access the environment
  • The site templates have already been created and copied to each team site, all that is remaining is the permissions to be set
  • The box is not linked to Active Directory in any way
  • Creating a dummy domain and promoting the SharePoint box to be a DC wasn’t an option
  • Several team sites created, and needed to be restricted to each logon
  • Users do not require access to the Site Collection, just their allocated team site

When trying to modify site security settings, the only local account visible was the COMPUTER\\SPAdmin account used to install SharePoint. First off we were looking at a way to use the User Profile Service to import local accounts, but that got us nowhere.

After some playing around, I realised there was an easier option. From the Site Collection level we set NT AUTHORITY\\Authenticated Users full control to the Site Collection. Hitting up http://COMPUTER/ on a separate machine we were prompted to authenticate against the box, and used each of the COMPUTER\\Team accounts to gain access to the site one by one, logging out before proceeding to the next user account.

Once this was done, the Team accounts were listed in Site Permissions as assignable accounts to the different sites. Each site was secured to the appropriate team, before we removed the original NT AUTHORITY\\Authenticated Users addition to Site Owners.

After this, we were able to access http://COMPUTER/ and navigate through all team sites as our SPAdmin account, and with each team site we could access http://COMPUTER/Team sites directly to access SharePoint, but as required we couldn’t navigate to other team’s sites.

Posted in SharePoint | Leave a comment