Joseph Michael Pesch
VP Programming

VBA Script for Visio 2013 to Retrieve User Contact Information from SharePoint 2013 MySites

by 11. September 2016 13:03

Created a VBA code script (included below) to run in Visio and look for objects that have a custom property named "UserRef", taking the corresponding value entered into that custom property (assumed to be a Windows AD user account id) to lookup the users contact information (Name, Title, Department, Email, Phone, Office Location) from the users SharePoint "MySite".  Also, adds a hyperlink to the object that points to the users SharePoint MySite.  In my case I was using Visio 2013 and SharePoint 2013.

Sub PopulateUserInfoObjects()

    Dim page As Integer, pages As Integer, shape As Integer, shapes As Integer
    page = 1
    pages = Application.ActiveDocument.pages.Count
    
    While page <= pages
        Application.ActiveWindow.page = Application.ActiveDocument.pages(page)
        page = page + 1
        shape = 1
        shapes = Application.ActiveWindow.page.shapes.Count
        Debug.Print Application.ActiveWindow.page.name & " has " & shapes & " shapes"
        While shape <= shapes
            Dim visioShape As Visio.shape
            Set visioShape = Application.ActiveWindow.page.shapes(shape)
            Dim userRef As String
            userRef = GetCustomPropertyValue(visioShape, "UserRef")
            If userRef <> "" Then
                Dim chars As Visio.Characters
                Set chars = visioShape.Characters
                chars.Text = GetUserInfoFromIntranet(userRef, visioShape)
            End If
            shape = shape + 1
        Wend
    Wend

End Sub

Function GetUserInfoFromIntranet(userId As String, addHyperlink As Visio.shape)
    
    ' Required references:
    '   Microsoft Internet Controls
    '   Microsoft Shell Controls and Automation
    
    ' The InternetExplorerMedium object is required instead of InternetExplorer object 
    ' to avoid the following exception:
    '   Run-time error '-21474178848 (80010108)':
    '   Automation Error
    '   The object invoked has disconnected form its clients.
    
    Dim IE As InternetExplorerMedium
    Dim targetURL As String
    Dim webContent As String
    Dim sh
    Dim eachIE
    
    targetURL = "http://SHAREPOINT_MYSITES_URL_HERE/Person.aspx?accountname=DOMAIN_NAME_HERE%5C" & userId
    Set IE = New InternetExplorerMedium
    IE.Visible = False
    IE.Navigate targetURL
    
    While IE.Busy
      DoEvents
    Wend
    
    Do
      Set sh = New Shell32.Shell
      For Each eachIE In sh.Windows
        If InStr(1, eachIE.LocationURL, targetURL) Then
          Set IE = eachIE
            'In some environments, the new process defaults to Visible.
            IE.Visible = False  
          Exit Do
          End If
        Next eachIE
      Loop
    Set eachIE = Nothing
    Set sh = Nothing
    
    While IE.Busy  ' The new process may still be busy even after you find it
      DoEvents
    Wend
      
    Dim name As String
    name = IE.Document.getElementById("ctl00_PictureUrlImage_NameOverlay").innerHTML
    Dim title As String
    title = IE.Document.getElementById("ProfileViewer_ValueTitle").innerHTML
    Dim dept As String
    dept = IE.Document.getElementById("ProfileViewer_ValueDepartment").innerHTML
    Dim email As String
    email = IE.Document.getElementById("ProfileViewer_ValueWorkEmail").innerHTML
    Dim phone As String
    phone = IE.Document.getElementById("ProfileViewer_ValueWorkPhone").innerHTML
    Dim office As String
    office = IE.Document.getElementById("ProfileViewer_ValueOffice").innerHTML

    On Error Resume Next
    DeleteHyperlinks addHyperlink
    AddHyperlinkToShape addHyperlink, targetURL
 
    name = name & Chr(10) & title & Chr(10) & phone & Chr(10) & email 
    name = name & Chr(10) & dept & Chr(10) & "Location: " & office
    GetUserInfoFromIntranet = name 

End Function

Function GetCustomPropertyValue(TheShape As Visio.shape, ThePropertyName As String) As String
    Dim value As String
    If TheShape.CellExistsU("Prop." & ThePropertyName, 0) Then
        GetCustomPropertyValue = TheShape.CellsU("Prop." & ThePropertyName).ResultStr(visNone)
    Else
        GetCustomPropertyValue = ""
    End If
End Function

Sub AddHyperlinkToShape(shape As Visio.shape, url As String)
    Dim link As Visio.Hyperlink
    Set link = shape.Hyperlinks.Add
    link.IsDefaultLink = False
    link.Description = ""
    link.Address = url
    link.SubAddress = ""
End Sub

Sub DeleteHyperlinks(shape As Visio.shape)
    Dim i As Integer
    i = 1
    While i < shape.Hyperlinks.Count
        shape.Hyperlinks.ItemU(i).Delete
    Wend
End Sub

Tags:

Sharepoint | VBA Script | Visio

Change the "SharePoint" Text in the Ttop Left Corner of SharePoint 2013 Site

by 20. April 2016 11:04

To change the text you can use PowerShell with the sample code snippet shown below.  NOTE: To use PowerShell for SharePoint make sure add the snapin (Add-PSSnapIn Microsoft.SharePoint.Powershell).

PowerShell Script Sample:

$webApp = Get-SPWebApplication http://MySiteUrlHere; $webApp.SuiteBarBrandingElementHtml = "<a href='/'>My Site Title Here</>"; $webApp.Update(); 

Tags:

Sharepoint

SharePoint PowerPivot Data Refresh - Can't Open the Workbook Because it Uses Unsupported Features

by 14. April 2015 07:44

Trying to run the SharePoint PowerPivot Unattended Data Refresh errors with: "We're sorry. We can't open the workbook in the browser because it uses these unsupported features: • Comments, Shapes, or other objects"

Use this tool from: http://blogs.msdn.com/b/cumgranosalis/archive/2007/06/29/excel-services-compatibility-checker-download-page.aspx

to fix the workbook.

Tags:

Sharepoint

SharePoint Document Sets

by 27. November 2013 08:49

Tags:

Sharepoint

SharePoint Always Run Configuration Wizard on All Farm Servers After Server Patching

by 26. November 2013 13:52

After patching (SharePoint updates) you need to run the configuration wizard.  The configuration wizard has to be ran on all SP servers in the farm. There is a command line that forces the wizard to re-run in the event of an issue or failure with the wizard. 

Psconfig.exe -cmd upgrade -inplace b2b -wait -force.

Tags:

Sharepoint

SharePoint WebPart Maintenance Page

by 5. September 2013 12:09

Issue with SharePoint is that the WebParts already in place on an existing page don't indicate what type of web part they actually are.  This is frustrating if it is not obvious what type of web part you are looking at.  In order to get a better view of the WebPart in question you can navigate to the WebPart Maintenance Page by entering a URL like this: http://yoursite/whatever/default.aspx?contents=1

Tags:

Sharepoint

Sharepoint PDF Document Prompting User to Checkout when it Shouldn't

by 26. August 2013 15:54

We had issues with PDF documents on Sharepoint that were prompting the average "View Only" users to check-out the document.  If they actually attempted to check-out the document they could not; however, this was cofusing to the users.  The desired effect was to have the document open directly into the PDF viewer.  Below are some registry settings that seemed to clear this issue up in most cases.

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\Policies\Adobe\Adobe Acrobat\11.0\FeatureLockDown\cSharePoint]
"bDisableSharePointFeatures"=dword:00000001

[HKEY_LOCAL_MACHINE\SOFTWARE\Policies\Adobe\Adobe Acrobat\10.0\FeatureLockDown\cSharePoint]
"bDisableSharePointFeatures"=dword:00000001

Tags:

Sharepoint

Sharepoint 2010 Development on Windows 7 Virtual Machine

by 22. July 2013 07:01

Sharepoint 2010 Registry Entries

I recently installed a development machine with the necessary tools to perform SharePoint development on a Windows 7 64bit virtual machine.  The machine was then used as a master for a set of 7 virtual desktop developer workstations.  The issue; however, was that the inital intallations created several registry entries that referenced the original machine name.  This rendered the Sharepoint environment broken on all the virtual desktops (as each one had a different unique machine name).  To resolve this, I went back through the registry on the master and change the original machine name to "LOCALHOST" virtual name on all the following registry entries.  NOTE: The initial symptom manifest itself initially when opening the browser and attempting to browse to the local sharepoint site (in this case htt://localhost).  The page threw the follwing error: "Cannot connect to the configuration database."  Upon reviewing the log Sharepoint files (C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\LOGS\...) I noticed the original machine name in the failed connection attempt log entry.

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office Server\14.0\LauncherSettings\LoadBalancerUrl
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office Server\14.0\Search\Applications\c0e071e1-533e-457a-8a61-8a86a6b78dcd\Gather\Portal_Content\ContentSources\0\StartPages\0\URL
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office Server\14.0\Search\Applications\c0e071e1-533e-457a-8a61-8a86a6b78dcd\Gather\Portal_Content\ContentSources\0\StartPages\1\URL
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office Server\14.0\Search\Applications\c0e071e1-533e-457a-8a61-8a86a6b78dcd\ResourceManager\ (several entries)
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Shared Tools\Web Server Extensions\14.0\Search\Applications\fd2e5092-25e3-46cf-8fc6-d62c85334b3d\Gather\Search\ContentSources\0\StartPages\0\URL
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Shared Tools\Web Server Extensions\14.0\Search\Applications\fd2e5092-25e3-46cf-8fc6-d62c85334b3d\ResourceManager\ (several entries)

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Shared Tools\Web Server Extensions\14.0\Secure\ConfigDB\dsn
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Shared Tools\Web Server Extensions\14.0\WSS\CentralAdministrationURL

Tags:

Sharepoint | Virtual PC | Windows

Sharepoint 2013 Development Using Visual Studio 2012

by 16. July 2013 13:14

Here is a good article on setting up Visual Studio 2012 to allow development of Sharepoint 2013 applications.

http://blogs.msdn.com/b/timquin/archive/2013/01/22/setting-up-visual-studio-2012-for-sharepoint-2013-development-offline.aspx

Tags:

ASP.Net | Sharepoint

Sharepoint - User Profile Synchronization Proccess

by 9. April 2013 10:52

When the user information is synchronized from Active Directory (AD) into Sharepoint there are two basic processes. 

First, User Profile Service Application running under the Sharepoint Services on Server is what actually imports data from AD into Sharepoint.  This can be customized to select specific AD attributes and can also be configured to export data back from Sharepoint to Acitve Directory.  This could be useful if you want to allow Sharepoint users to update selected attributes directly in Sharepoint and publish them back to Active Directory.  You can view the current status of an active synchronization by using the Forefront Identity Manager (located at [InstatllationDrive]:\Program Files\Microsoft Office Servers\14.0\Synchronization Service\UIShell\miisclient.exe).  The Sharepoint Service relies on the "Forefront Identity Manager Service" which must also be running on the Sharepoint server that is running the synchronization service.

That process simply creates, updates and deletes the records in the Sharepoint User Database by inspecting the current state of Active Directory.  To have the information visible in the Sharepoint People search and liked visually to organizational heirarchy it must also be propogated into the corresponding Sharepoint content databases.  That portion of the process occurs through the User Profile Sync Timer Job (running under the Sharepoint Timer service) which typically runs at the top of each hour.

IMPORTANT NOTE: If you use the People Picker in Sharepoint to locate user it will locate users directly in Active Directory (i.e. even if they have not been synchronized or setup in Sharepoint previously).  If you then add a user to a library, list, etc. (i.e. grant them permissions, add to a group, etc.) they Sharepoint will create a user stub record in the User Database with minimal data (basically just the user id and guid).  Next time the synchronization process runs it will update all the additional user attributes based on how the service is configured.  In my case the synchronization process was failing so there was some confusion as to how some users were getting into the Sharepoint environment with minimal data (i.e. they were getting added when they were granted permissions to resources in Sharepoint).

A couple of maintenance items that can help if these processes start failing:

1) Clearing the User Profile Sync Table - Over time the data in this table will get corrupted specifically when dropping and attaching databases this is a known issue.  In order to see a list of the entries you can use the listolddatabases commande below.  To clean up the corrupted data you can run the deleteolddatabases command below which will delete all entries that have not been successfully updated since 1 day ago (i.e. the last parameter is the number of days since last update).

STSADM -o sync -listolddatabases 0
STSADM -o sync -deleteolddatabases 1

2) Clear the configuration cache on the servers in the Sharepoint server farm:

To resolve this issue, clear the file system cache on all servers in the server farm on which the Windows SharePoint Services Timer service is running. To do this, follow these steps:
  1. Stop the Timer service. To do this, follow these steps:
    1. Click Start, point to Administrative Tools, and then click Services.
    2. Right-click Windows SharePoint Services Timer, and then click Stop.
    3. Close the Services console.
  2. On the computer that is running Microsoft Office SharePoint Server 2007 and on which the Central Administration site is hosted, click Start, click Run, type explorer, and then press ENTER.
  3. In Windows Explorer, locate and then double-click the following folder:
    Drive:\Documents and Settings\All Users\Application Data\Microsoft\SharePoint\Config\GUID
    Notes
    • The Drive placeholder specifies the letter of the drive on which Windows is installed. By default, Windows is installed on drive C.
    • The GUID placeholder specifies the GUID folder.
    • The Application Data folder may be hidden. To view the hidden folder, follow these steps:
      1. On the Tools menu, click Folder Options.
      2. Click the View tab.
      3. In the Advanced settings list, click Show hidden files and folders under Hidden files and folders, and then click OK.
    • In Windows Server 2008, the configuration cache is in the following location:
      Drive:\ProgramData\Microsoft\SharePoint\Config\GUID
  4. Back up the Cache.ini file.
  5. Delete all the XML configuration files in the GUID folder. Do this so that you can verify that the GUID folder is replaced by new XML configuration files when the cache is rebuilt.

    Note When you empty the configuration cache in the GUID folder, make sure that you do not delete the GUID folder and the Cache.ini file that is located in the GUID folder.
  6. Double-click the Cache.ini file.
  7. On the Edit menu, click Select All.
  8. On the Edit menu, click Delete.
  9. Type 1, and then click Save on the File menu.
  10. On the File menu, click Exit.
  11. Start the Timer service. To do this, follow these steps:
    1. Click Start, point to Administrative Tools, and then click Services.
    2. Right-click Windows SharePoint Services Timer, and then click Start.
    3. Close the Services console.
    Note The file system cache is re-created after you perform this procedure. Make sure that you perform this procedure on all servers in the server farm.
  12. Make sure that the Cache.ini file has been updated. For example it should no longer be 1 if the cache has been updated.
  13. Click Start, point to Programs, point to Administrative Tools, and then click SharePoint 3.0 Central Administration.
  14. Click the Operations tab, and then click Timer job status under Global Configuration.
  15. In the list of timer jobs, verify that the status of the Config Refresh entry is Succeeded.
  16. On the File menu, click Close.
Original link to these clean up steps: http://support.microsoft.com/kb/939308

Tags:

Sharepoint

SQL Read Sharepoint Document Content for SSRS Reports

by 6. December 2012 09:43

I had a case where I needed to review a few hundred SSRS reports installed on a Sharepoint 2010 envrionment.  Below is a SQL script I created to read the content from the underlying SQL database and search the report structure for certain keywords (in my case I was looking for specific database/table names in the report SQL).

create table #tmp(name varchar(150), content varchar(max))
/* Get cursor of all SSRS report (*.rdl) files to cycle through */
declare cur cursor for 
select Id, LeafName, InternalVersion 
from AllDocs where LeafName like '%.rdl'
declare @id varchar(50), @name varchar(200), @version int
open cur
while 1=1 begin
  fetch next from cur into @id, @name, @version
  if @@FETCH_STATUS <> 0 break
  /* Check is current version of the report contains specific keywords */
  if exists
  (
    select 1 
	from WSS_CONTENT_SITENAMEHERE.dbo.AllDocStreams 
	where Id = @id 
	and InternalVersion = @version 
	and 
	(
	  cast(Content as varchar(max)) like '%KEYWORD #1 HERE%' 
	  or 
	  cast(Content as varchar(max)) like '%KEYWORD #2 HERE%')
	)  
    insert into #tmp 
    select @name, cast(Content as varchar(max)) 
    from WSS_CONTENT_SITENAMEHERE.dbo.AllDocStreams 
    where Id = @id and InternalVersion = @version
end
close cur
deallocate cur
select * from #tmp
drop table #tmp

Tags:

Sharepoint | SQL Server

Excel Services in SharePoint 2010 Error: This workbook is larger than the maximum workbook size allowed to be opened in the browser

by 24. October 2012 08:10

By default the maximum file size that can be opened in the browser (by Excel services) is 10MB.  This is managed through the Central Administration > Application Management > Manage Service Applications > Excel Services > Trusted File Locations.  Below is an excerpt from Microsofts documentation on this topic located here: http://technet.microsoft.com/en-us/library/ff191194.aspx  When you attempt to open a file larger than this setting allows you will receive this error message: "This workbook is larger than the maximum workbook size allowed to be opened in the browser"

In the Workbook Properties section, you determine the resource capacity for the server when Excel Services opens workbooks.

Performance and resource availability can be compromised if users open extensive workbooks. If you do not manage the approved size that workbooks running in open Excel Calculation Services sessions can have, you risk having users exceed your resource capacity and overloading the server.

  1. In the Maximum Workbook Size box, type a value in megabytes (MB) for the maximum size of workbooks that Excel Calculation Services can open. The default size is 10 megabytes. 

Tags:

Sharepoint

Open PDF in Sharepoint 2010

by 16. July 2012 17:02

We added an editable PDF document to our Sharepoint 2010 site; however, under the sites default settings it forced the user to save the document (i.e. it would not open the document directly from the site).  Below is a PowerShell script that will add the PDF MIME type to the list of allowable inline download types.  NOTE: Save the script with .PS1 extension to run in PowerShell.

$webapp = Get-SPWebApplication http://sharepointsitehere
If ($webapp.AllowedInlineDownloadedMimeTypes -notcontains "application/pdf")
{
  Write-Host -ForegroundColor White "Adding Pdf MIME Type..."
  $webapp.AllowedInlineDownloadedMimeTypes.Add("application/pdf")
  $webapp.Update()
  Write-Host -ForegroundColor White "Added and saved."
}
 Else
{
  Write-Host -ForegroundColor White "Pdf MIME type is already added."
}

Tags:

Sharepoint | PowerShell

C# Upload Local Document to SharePoint Site

by 22. February 2012 10:17

Below is a sample windows service that will run daily.  This method is used to open a local Excel file, refresh it's data connection(s), save the updated file locally and finally, upload the local file to a SharePoint site.

Command prompts to Delete and Create the Windows Service:

sc delete ServiceNameHere
sc create ServiceNameHere binpath= "c:\pathtoexe\AutomationService.exe"

Sample App.Config:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <configSections>
    <section name="FileDictionary" type="System.Configuration.DictionarySectionHandler" />
  </configSections>
  <FileDictionary>
    <add key="C:\Data\File1.xlsx" value="http://mysharepoint/sites/reports/File1.xlsx" />
    <add key="C:\Data\File2.xlsx" value="http://mysharepoint/sites/reports/File2.xlsx" />
  </FileDictionary>
</configuration>

Sample Code:

using System;
using System.Collections;
using System.ComponentModel;
using System.Configuration;
using System.Net;
using System.Runtime.InteropServices;
using System.ServiceProcess;
using Microsoft.Office.Interop.Excel;

namespace AutomationService
{
  public partial class AutomationServiceInstance : ServiceBase
  {

    string path = ConfigurationManager.AppSettings["FilePath"];
    // Go back one day so we run on start...
    DateTime lastRun = DateTime.Now.AddDays(-1);

    public AutomationServiceInstance()
    {
      InitializeComponent();
    }

    protected override void OnStart(string[] args)
    {
      System.IO.File.AppendAllText(path + @"\AutomationServiceLog.txt"
        , Char.ToString('\r') + Char.ToString('\n') 
        + "Starting service ... " + DateTime.Now.ToString());
      if(System.IO.File.Exists(path + @"\LastRun.txt"))
      {
        // If last run time file exists load the date/time from the file
        lastRun = Convert.ToDateTime(System.IO.File.ReadAllText(path + @"\LastRun.txt").Trim());
      }
      BackgroundWorker wkr = new BackgroundWorker();
      wkr.DoWork += TimeCycle;
      wkr.RunWorkerAsync();
    }

    protected override void OnStop()
    {
      System.IO.File.AppendAllText(path + @"\AutomationServiceLog.txt"
        , Char.ToString('\r') + Char.ToString('\n')
        + "Stopping service ... " + DateTime.Now.ToString());
    }


    protected void TimeCycle(object sender, DoWorkEventArgs e)
    {
      do
      {
        lastRun = Convert.ToDateTime(System.IO.File.ReadAllText(path + @"\LastRun.txt").Trim());
        System.IO.File.AppendAllText(path + @"\AutomationServiceLog.txt"
          , Char.ToString('\r') + Char.ToString('\n')
          + "Checking time ... " + DateTime.Now.ToString()
          + " lastRun: " + lastRun + "; curTime: " + DateTime.Now.ToString());
        if (!lastRun.DayOfYear.Equals(DateTime.Now.DayOfYear) 
          && DateTime.Now.TimeOfDay >= new TimeSpan(8,15,0)) ProcessIt();
        System.Threading.Thread.Sleep(60 * 1000);
      } while (true);
    }

    protected void ProcessIt()
    {
      // Key is the local source file, Value is the target web URL
      Hashtable tbl = (Hashtable)ConfigurationManager.GetSection("FileDictionary");
      foreach (DictionaryEntry de in tbl)
      {
        try
        {
          Application excel = new ApplicationClass();
          excel.DisplayAlerts = false;
          System.IO.File.AppendAllText(path + @"\AutomationServiceLog.txt"
            , Char.ToString('\r') + Char.ToString('\n')
            + "Begin processing ... " + DateTime.Now.ToString());
          string srcFile = de.Key.ToString();
          string outFile = de.Value.ToString();
          // Open the file 
          // Important, do not double up on objects in COM 
          // Notice the Workbooks variable used to open the workbook 
          // DO NOT DO THIS: Workbook wkb = excel.Workbooks.Open(srcFile, false, true); 
          Workbooks wkbs = excel.Workbooks;
          Workbook wkb = wkbs.Open(srcFile, false, true);
          System.IO.File.AppendAllText(path + @"\AutomationServiceLog.txt"
            , Char.ToString('\r') + Char.ToString('\n')
            + "     workbook open: " + DateTime.Now.ToString());
          wkb.EnableConnections();
          System.IO.File.AppendAllText(path + @"\AutomationServiceLog.txt"
            , Char.ToString('\r') + Char.ToString('\n')
            + "     connections enabled: " + DateTime.Now.ToString());
          wkb.RefreshAll();
          System.IO.File.AppendAllText(path + @"\AutomationServiceLog.txt"
            , Char.ToString('\r') + Char.ToString('\n')
            + "     workbook refreshed: " + DateTime.Now.ToString());
          System.IO.File.AppendAllText(path + @"\AutomationServiceLog.txt"
            , Char.ToString('\r') + Char.ToString('\n')
            + "Processing complete ... " + DateTime.Now.ToString());
          // Clean up to release resources 
          // IMPORTANT: This cleanup sequence was required when running 
          // this inside a service that remains open and recycles on a schedule 
          // otherwise multiple EXCEL processes would be running and resources 
          // left open causing file access problems and memory leaks. 
          GC.Collect();
          GC.WaitForPendingFinalizers();
          wkb.Close(false, Type.Missing, Type.Missing);
          Marshal.ReleaseComObject(wkb);
          Marshal.ReleaseComObject(wkbs);
          excel.Quit();
          Marshal.ReleaseComObject(excel);
          GC.Collect();
          GC.WaitForPendingFinalizers();
          if (WebClientUpload(srcFile, outFile, path))
          {
            lastRun = DateTime.Now;
            System.IO.File.WriteAllText(path + @"\LastRun.txt", lastRun.ToString());
          }
        }
        catch (Exception ex)
        {
          System.IO.File.AppendAllText(path + @"\AutomationServiceLog.txt"
            , Char.ToString('\r') + Char.ToString('\n')
            + "Exception occurred ... " + DateTime.Now.ToString());
          System.IO.File.AppendAllText(path + @"\AutomationServiceLog.txt"
            , Char.ToString('\r') + Char.ToString('\n') + ex.Message);
          System.IO.File.AppendAllText(path + @"\AutomationServiceLog.txt"
            , Char.ToString('\r') + Char.ToString('\n') + ex.StackTrace);
        }
      }
    }
    static bool WebClientUpload(string SourcePath, string TargetPath, string LogPath)
    {
      WebClient wc = new WebClient();
      wc.UseDefaultCredentials = true;
      try
      {
        byte[] response = wc.UploadFile(TargetPath, "PUT", SourcePath);
      }
      catch (WebException ex)
      {
        System.IO.File.AppendAllText(LogPath + @"\AutomationServiceLog.txt"
          , Char.ToString('\r') + Char.ToString('\n')
          + "Exception occurred ... " + DateTime.Now.ToString());
        System.IO.File.AppendAllText(LogPath + @"\AutomationServiceLog.txt"
          , Char.ToString('\r') + Char.ToString('\n') + ex.Message);
        System.IO.File.AppendAllText(LogPath + @"\AutomationServiceLog.txt"
          , Char.ToString('\r') + Char.ToString('\n') + ex.StackTrace);
        return false;
      }
      return true;
    }
  }
}

Tags:

C# | Excel | Sharepoint

SharePoint 2010 Default Log File Location

by 24. January 2012 07:29

Since I always seem to forget the path to the SharePoint 2010 default log folder... Here it is: C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\LOGS

Tags:

Sharepoint

SharePoint Deploy Workflow Feature to Server Farm

by 11. January 2012 13:24

This post covers steps used to deploy the workflow feature to a SharePoint site (or sub-site as shown in this example URL: http://ServerNameHere/sites/SubSiteNameHere).  Attached is a sample SharePoint workflow feature that can configured to perform a daily refresh of an Excel file from a SharePoint site (i.e. refreshing the data connections to update data from external sources).

To setup the Visual Studio Solution, you will need to change the URL located in the three areas shown below:

  1. Project -> Site URL -> http://ServerNameHere/sites/SubSiteNameHere
  2. Web References -> ExcelWebService -> Web Reference URL -> http://ServerNameHere/sites/SubSiteNameHere/_vti_bin/excelservice.asmx
  3. Web References -> ListService -> Web Reference URL -> http://ServerNameHere/sites/SubSiteNameHere/_vti_bin/lists.asmx

When deploying the workflow feature on the server (either through Visual Studio right-click and “Deploy”; or, via Power Shell script shown below) I received the following error message:

Error occurred in deployment step 'Activate Features': Feature with Id '{GUID}' is not installed in this farm, and cannot be added to this scope.

Power Shell Deployment Script:

rem NOTE: Make sure the "Sharepoint 2010 Administration" service is NOT running.
rem We are using the execadmsvcjobs to execute rather than letting th Timer service run them.
rem ADDING...
cd "c:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\BIN"
stsadm -o addsolution -filename "C:\VSS\ExcelRefreshWorkflow\bin\Debug\ExcelRefreshWorkflow.wsp"
stsadm -o deploysolution -name "ExcelRefreshWorkflow.wsp" -immediate -allowgacdeployment -allowcaspolicies
stsadm -o execadmsvcjobs
stsadm -o activatefeature -name "ExcelRefreshWorkflow_Feature1" -url http://ServerNameHere/sites/SubSiteNameHere

NOTE: This feature is being deployed to “Scope = Site”, not Farm, Web or Web Application scope.

image

If you are running a single server instance of Sharepoint this should not happen (i.e. your deployment should be completed successfully). In the case of a Sharepoint server farm you will need to perform the following steps manually to complete the installation.

  1. From the “SharePoint 2010 Central Administration” site:
    • Navigate to: “System Settings” -> “Manage farm solutions”
    • Click on “excelrefreshworkflow.wsp”
    • Click on “Deploy Solution” and complete the deployment screen (schedule or run immediately).
    • Refresh the farm solutions screen until the solution management screen until the status column shows “Deployed” (note, the “Deployed To” column should show “Globally deployed.”).
    • NOTE: Make sure the “SharePoint 2010 Timer” service is running; otherwise, this step will not complete.
  2. Open the normal SharePoint site and from the top level site:
    • Navigate to: “Site Actions” -> “Site Settings” -> “Site Collection Administration” -> “Site collection features”
    • Click the “Activate” button associated with the “ExcelRefreshWorkflow” item in the list.
    • NOTE: Repeat this step on every sub-site that you want to use the workflow in.
  3. At this point the installation is complete. You can now navigate to the sub-site and configure the workflow on the appropriate Library and activate it on the appropriate documents in the library.
    • From the sub-site home page, navigate to: “Library Tools” -> “Library” -> “Library Settings” -> “Workflow Settings”.
    • From this screen you should see the “ExcelRefreshWorkflow” item in the “Select a workflow template:” list. Select that item, complete the remaining configuration settings and click “OK”.
    • For each document desired, click on the document dropdown and select “Workflows”.
    • Click on the “ExcelRefreshWorkflow”
    • Setup the appropriate configuration settings and click the “Start Workflow” button.
    • NOTE: All activity related to the workflow (including the execution history) will be stored in the associated “Workflow History”.

Attached is a sample workflow that will open an Excel file in document library, refresh the data connection and save the file as a new version in the document library on a scheduled interval.

ExcelRefreshWorkflow.zip (422.11 kb)

Tags:

Sharepoint | Workflow Foundation

Deploying SharePoint Workflow Feature to Server Farm

by 11. January 2012 11:52

 

Assume you are trying to deploy the workflow feature to a sub-site:

(http://ServerNameHere/sites/SubSiteNameHere)

 

To setup the Visual Studio Solution, change the URL located in three areas:

1.       Project -> Site URL -> http://ServerNameHere/sites/SubSiteNameHere

2.       Web References -> ExcelWebService -> Web Reference URL -> http://ServerNameHere/sites/SubSiteNameHere/_vti_bin/excelservice.asmx

3.       Web References -> ListService -> Web Reference URL -> http://ServerNameHere/sites/SubSiteNameHere/_vti_bin/lists.asmx

 

When deploying the workflow feature on the server (either through Visual Studio right-click and “Deploy”; or, via Power Shell script shown below) I received the following error message:

 

Error occurred in deployment step 'Activate Features': Feature with Id '{GUID}' is not installed in this farm, and cannot be added to this scope.

 

Power Shell Deployment Script:

rem NOTE: Make sure the "Sharepoint 2010 Administration" service is NOT running.

rem We are using the execadmsvcjobs to execute rather than letting th Timer service run them.

 

rem ADDING...

cd "c:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\BIN"

stsadm -o addsolution -filename "C:\VSS\ExcelRefreshWorkflow\bin\Debug\ExcelRefreshWorkflow.wsp"

stsadm -o deploysolution -name "ExcelRefreshWorkflow.wsp" -immediate -allowgacdeployment -allowcaspolicies

stsadm -o execadmsvcjobs

stsadm -o activatefeature -name "ExcelRefreshWorkflow_Feature1" -url http://ServerNameHere/sites/SubSiteNameHere

 

NOTE: This feature is being deployed to “Scope = Site”, not Farm, Web or Web Application scope.

 

If you are running a single server instance of Sharepoint this should not happen (i.e. your deployment should be completed successfully).  In the case of a Sharepoint server farm you will need to perform the following steps manually to complete the installation.

 

1.       From the “SharePoint 2010 Central Administration” site:

a.       Navigate to: “System Settings” -> “Manage farm solutions”

b.      Click on “excelrefreshworkflow.wsp”

c.       Click on “Deploy Solution” and complete the deployment screen (schedule or run immediately).

d.      Refresh the farm solutions screen until the solution management screen until the status column shows “Deployed” (note, the “Deployed To” column should show “Globally deployed.”).

e.      NOTE: Make sure the “SharePoint 2010 Timer” service is running; otherwise, this step will not complete.

2.       Open the normal SharePoint site and from the top level site:

a.       Navigate to: “Site Actions” -> “Site Settings” -> “Site Collection Administration” -> “Site collection features”

b.      Click the “Activate” button associated with the “ExcelRefreshWorkflow” item in the list.

c.       NOTE: Repeat this step on every sub-site that you want to use the workflow in.

3.       At this point the installation is complete.  You can now navigate to the sub-site and configure the workflow on the appropriate Library and activate it on the appropriate documents in the library.

a.       From the sub-site home page, navigate to: “Library Tools” -> “Library” -> “Library Settings” -> “Workflow Settings”.

b.      From this screen you should see the “ExcelRefreshWorkflow” item in the “Select a workflow template:” list.  Select that item, complete the remaining configuration settings and click “OK”.

c.       For each document desired, click on the document dropdown and select “Workflows”.

                                                   i.      Click on the “ExcelRefreshWorkflow”

                                                 ii.      Setup the appropriate configuration settings and click the “Start Workflow” button.

                                                iii.      NOTE: All activity related to the workflow (including the execution history) will be stored in the associated “Workflow History”.

Tags:

Sharepoint

Sharepoint 2010 Database Connection Strings in Registry

by 5. October 2011 08:28

I recently installed a new development environment on a virtual machine that was being used as the basis of a new virtual desktop pool for several developers.  My Sharepoint environment was broken once the virtual desktops were created because the installation put the machine name in the SQL connection strings and each of the virtual desktop instances has a unique machine name.  When I attempted to browse to the local sharepoint site I simply received the following error message: "Cannot connect to the configuration database.".  Researching the log file (C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\Logs\...) I saw the original machine name in the connection string of the failed connections.  Next I looked at the web.config of the root site as well as the C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\Config\web.config and was unable to find any connection information.  Finally, I found the following registry entries and changed them from the specific machine name to the "localhost" alias.  NOTE: The first entry is the database connection and the other entries are various URL's.

My Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Shared Tools\Web Server Extensions\14.0\Secure\ConfigDB\

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office Server\14.0\LauncherSettings\LoadBalancerUrl

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office Server\14.0\Search\Applications\c0e071e1-533e-457a-8a61-8a86a6b78dcd\Gather\Portal_Content\ContentSources\0\StartPages\0\URL

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office Server\14.0\Search\Applications\c0e071e1-533e-457a-8a61-8a86a6b78dcd\Gather\Portal_Content\ContentSources\0\StartPages\1\URL

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office Server\14.0\Search\Applications\c0e071e1-533e-457a-8a61-8a86a6b78dcd\ResourceManager\ (several urls are located in this section)

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Shared Tools\Web Server Extensions\14.0\Search\Applications\fd2e5092-25e3-46cf-8fc6-d62c85334b3d\Gather\Search\ContentSources\0\StartPages\0\URL

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Shared Tools\Web Server Extensions\14.0\Search\Applications\fd2e5092-25e3-46cf-8fc6-d62c85334b3d\ResourceManager\ (several urls are located in this section)

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Shared Tools\Web Server Extensions\14.0\WSS\CentralAdministrationURL

 

Tags:

Sharepoint

Get User that Initiated Task Change Event on a Workflow Task in Sharepoint

by 17. June 2011 11:13

This topic covers Sharepoint workflow tasks.  Specifically, the how to get the user account that triggered the workflow event.  By using the typical CurrentUser method on the Web object you will typically get the SHAREPOINT\system account rather than the actual account of the user that initiated the event.  Instead you can use the OriginatorUser method of the Workflowproperties as shown below.

// In both cases below you will typically get "SHAREPOINT\system"
Workflowproperties.web.CurrentUser;
// or
SPContext.Current.Web.CurrentUser;

// To get the acutal user that initiated task change event use this:
Workflowproperties.OriginatorUser

 

Tags:

C# | Sharepoint

Update Sharepoint Task Item inside Workflow Causes Item to be Indefinitely Locked by Workflow

by 17. June 2011 10:31

This topic covers updating a Sharepoint workflow task item, inside of a workflow event.  Sometimes performing an update to the item inside the workflow event will cause the item to be seen as indefinitely locked by the workflow in which case future attempts to update the same item (either inside a workflow event or by a user attempting to edi the item directly through the Sharepoint front end) will result in this error: "This task is currently locked by a running workflow and cannot be edited.". 

Typically, this will happen if you use the basic Update method of the item object (as shown in sample code below).  However, you should instead use the SystemUpdate method along with the explicit setting of the WorkflowVersion field value to 1 (as shown in sample code below).

/* You have to include the following two lines before the call to item.SystemUpdate()   
* to avoid this error:    
* "This task is currently locked by a running workflow and cannot be edited."   
* For details see:    
* http://msdn.microsoft.com/en-us/library/microsoft.sharepoint.splistitem.update%28v=office.12%29.aspx   
*/
web.AllowUnsafeUpdates = true; 
// You have to include this line  
item[SPBuiltInFieldId.WorkflowVersion] = 1;  
item.SystemUpdate(false);  // Don't use: item.Update(); 
//

Tags:

C# | Sharepoint