A lot of folks have to geocode data that they get from various folks in their organization, and it often makes its way in to Excel.  Of course, you can batch geocode using Bing Maps Services, but in some scenarios it is a lot more convenient or reasonable to use Excel.

Wouldn’t it be handy if there was an Excel function like =GEOCODE(<Some Address>)?

Well, here it is.

This uses the Bing Maps REST services and synchronous VBA and XML.  This is similar to asynchronous JavaScript and XML (AJAX) but in this case it would be SVAX.

This code is provided without warranty, etc.

You will need a Bing Maps key (See: Getting a Bing Maps Key), Excel, and an internet connection.

Option 1:  Create your own module

1. Expose the Developer tools (See: Show the Developer Tab)
2. Click the Visual Basic button
3. Insert a Module
4. Paste in this code and modify as you see fit:

Option Explicit

Function GeocodeAddress(address As String, BingMapsKey As String) As String

    Dim oHttpReq As MSXML2.XMLHTTP

    Set oHttpReq = New MSXML2.XMLHTTP

    oHttpReq.Open "get", "https://dev.virtualearth.net/REST/v1/Locations?q=" & address & "&o=xml&key=" & BingMapsKey, "false"

    oHttpReq.send

    If oHttpReq.readyState = 4 Then

        GeocodeAddress = oHttpReq.responseXML.SelectNodes("//Point/Latitude").Item(0).Text & "," & oHttpReq.responseXML.SelectNodes("//Point/Longitude").Item(0).Text

    End If

End Function

Function DriveDistance(From As String, Dest As String, BingMapsKey As String) As String

    Dim oHttpReq As MSXML2.XMLHTTP

    Set oHttpReq = New MSXML2.XMLHTTP

    oHttpReq.Open "get", "https://dev.virtualearth.net/REST/V1/Routes/Driving?o=xml&wp.0=" & From & "&wp.1=" & Dest & "&avoid=minimizeTolls&key=" & BingMapsKey, "false"

    oHttpReq.send

    If oHttpReq.readyState = 4 Then

        DriveDistance = oHttpReq.responseXML.SelectNodes("//TravelDistance").Item(0).Text

    End If

End Function

Function GetStateCountryFromPoint(Lat As String, Lon As String, BingMapsKey As String) As String

    Dim oHttpReq As MSXML2.XMLHTTP

    Set oHttpReq = New MSXML2.XMLHTTP

    oHttpReq.Open "get", "https://dev.virtualearth.net/REST/v1/Locations/" & Lat & "," & Lon & "?o=xml&key=" & BingMapsKey, "false"

    oHttpReq.send

    If oHttpReq.readyState = 4 Then

        If oHttpReq.responseXML.SelectNodes("//Address/AdminDistrict").Length > 0 Then

            GetStateCountryFromPoint = oHttpReq.responseXML.SelectNodes("//Address/AdminDistrict").Item(0).Text & ", " & oHttpReq.responseXML.SelectNodes("//Address/CountryRegion").Item(0).Text

        Else

            GetStateCountryFromPoint = "No Country"

        End If

    End If

End Function

Now you can use cell references or addresses to geocode your addresses or find the distance between two locations.

E.g. =GeocodeAddress(“San Antonio, TX”, BingMapsKey)

Option 2:   Add this file to your Add-ins directory.

(See: Office.com: Add or remove add-ins )

This also includes several other geocoding functions.

Here is a neat trick for getting graphs in PowerPoint to automatically update from SQL Reporting Services.

Prerequisites:

• Word
• PowerPoint
• SQL Reporting Services

1. Go directly to the report server, not the SharePoint integrated mode. Have it output to HTML,
2. insert it into a field in Word, then
3. embed that word document in a PowerPoint slide.

http://ReportServerMachine/ReportServer?http://LocationOfReport/Shared%20Documents/DailyReport.rdl&rs:Command=Render&rs:Format=HTML4.0&rc:Toolbar=False

Alternatively, you can render it as an image:
http://ReportServerMachine/ReportServer?http://LocationOfReport/Shared%20Documents/DailyReport.rdl&rs:Command=Render&rs:Format=Image&rc:Toolbar=False&rc:OutputFormat=PNG

The reason for this is that the SharePoint integration puts in a bunch of AJAX that confuses Word. The report server can produce pure HTML with no Javascript.

Thanks:  Shashank Pawar, his post has some screenshots.

Inspired by this post: http://on10.net/blogs/sarahintampa/Is-it-NET-Net-or-net/

Here is a script to add the common misspellings of Microsoft brands to Word AutoCorrect.

Copy and paste this into a file with a .JS extension and run it from your local computer.  Then launch Word and try typing Powerpoint and notice that it automatically changes to PowerPoint. 

// JScript source code
//Adds Word AutoCorrect entries for common misspellings and capitalizations for Microsoft Brands
//inspired by http://blogs.msdn.com/danielfe/archive/2010/04/27/correcting-grammar-for-microsoft-products-and-technology.aspx

var wd = new ActiveXObject("Word.Application");
var AutoCorrections = [[".net", ".NET"], [".Net", ".NET"], ["Clear Type", "ClearType"], ["Kin", "KIN"], ["Powerpoint", "PowerPoint"], ["Power Point", "PowerPoint"],
["Seadragon", "SeaDragon"], ["Xbox Live", "Xbox LIVE"], ["Bizspark", "BizSpark"], ["Sharepoint", "SharePoint"], ["PlayTo", "Play To"],
["Infopath", "InfoPath"], [".net", ".NET"], [".Net", ".NET"], ["AdCenter", "adCenter"],
["Ad Center", "adCenter"], ["Adcenter", "adCenter"], ["Ado.net", "ADO.NET"], ["ADO.Net", "ADO.NET"],
["Asp.net", "ASP.NET"], ["ASP.Net", "ASP.NET"],
["Biz Spark", "BizSpark"], ["Bizspark", "BizSpark"],
["Clear Type", "ClearType"], ["Clear type", "ClearType"],
["Cleartype", "ClearType"], ["Directaccess", "DirectAccess"],
["Direct Access", "DirectAccess"], ["Direct Show", "DirectShow"],
["Directshow", "DirectShow"], ["Direct x", "DirectX"],
["Dream Spark", "DreamSpark"], ["Dreamspark", "DreamSpark"],
["Home Group", "HomeGroup"], ["Homegroup", "HomeGroup"],
["HoTMaiL", "Hotmail"], ["Hot Mail", "Hotmail"], ["Hot Mail", "Hotmail"],
["Info Path", "InfoPath"], ["Infopath", "InfoPath"],
["intellisense", "IntelliSense"], ["Intellisense", "IntelliSense"],
["Iron Ruby", "IronRuby"], ["MSN Messenger", "Windows Live Messenger"],
["onenote", "OneNote"], ["Onenote", "OneNote"], ["One Note", "OneNote"],
["XBOX", "Xbox"], ["xbox", "XBox"], ["Xaml", "XAML"],
["Sql Server", "SQL Server"], ["SilverLight", "Silverlight"],
["Share Point", "SharePoint"], ["sharepoint", "SharePoint"]];

for (var i=0;i<AutoCorrections.length;i++)
{
    wd.AutoCorrect.Entries.Add(AutoCorrections[i][0],AutoCorrections[i][1]);
}

This is one of my favorite stupid SharePoint tricks.

If you have a situation where there is a web part (or any web page component) that is slow, and it is slowing the page render time for your page this is a handy trick.

Here is the summary:

1. Create another web part page and add the slow loading web part.
2. Look at the source code for the render page and get the control id of the div tag for the web part (the skewer click in the IE8 dev toolbar or something similar is an easy way to do this).
3. Back on the page where you originally wanted the web part, add a content editor web part
4. Put the following script in the source of the web part:

var ctId="ctl00_"; //Replace this with your control ID from step 2
var sourceURL="http://YourServer/somesite/PageThatHasTheSlowWebPart.aspx";
var outputId="MakeshiftAsyncWebPart";

_spBodyOnLoadFunctionNames.push("renderASlowWebPartAsynchronously"); 

function renderASlowWebPartAsynchronously()
{
      loader(sourceURL);
}

function renderOutput(output)
{
document.getElementById(outputId).innerHTML=output;
      
}

function getElementByClassname(className)
{
      var allItems = document.all;
      for (var i=0; i < allItems.length; i++) 
      {
            if (allItems[i].className == className) 
            {
                  return allItems[i];
            }
      }
      return null;
}

function getElementByClassnameFromString(className,str)
{
      var myTempDiv=document.createElement("div");
      myTempDiv.innerHTML=str;
      
      var allItems = myTempDiv.all;
      for (var i=0; i < allItems.length; i++) 
      {
            if (allItems[i].className == className) 
            {
                  return allItems[i];
            }
      }
      return null;
}

function getElementByIDFromString(myID,str)
{
      var myTempDiv=document.createElement("div");
      myTempDiv.innerHTML=str;
      
      var allItems = myTempDiv.all;
      for (var i=0; i < allItems.length; i++) 
      {
            if (allItems[i].id == myID) 
            {
                  return allItems[i];
            }
      }
      
      return null;
}

function loader(url) {
  
  if (window.XMLHttpRequest) {
    request = new XMLHttpRequest();
  } else if (window.ActiveXObject) {
    request = new ActiveXObject("Microsoft.XMLHTTP");
  }
  if (request != undefined) 
  {
    request.onreadystatechange = function() {asyncHandler(url);};
    request.open("GET", url, true);
    request.send("");
  }
}  

function asyncHandler(url) 
{
  if (request.readyState == 4) 
  { 
    if (request.status == 200) 
    {
      var output=getElementByIDFromString(ctId,request.responseText).innerHTML;
      if (output==null)
      {
            /* 
            //Optional:  
            output="No results";
            renderOutput(output);
            */
      }
      else
            renderOutput(output);
    } 
    else 
    {
      //Optional
      //document.getElementById('dOutput').innerHTML=" Error: "+ request.status + "\n" +request.statusText;
    }
  }
}

<div id=”MakeshiftAsyncWebPart”></div>

You can even reference an Animated GIF in SharePoint 2007 for AJAX progress indicators so that users know the web part is loading.

Keep in mind that users won’t get the security prompt for cross site scripting if the two pages are on the same server.

Of course: This code is provided as an example. Use at your own risk. No warranties.

Here are possible AJAX animation alternatives in http://<myMOSSServer>/<anysiteurl>/_layouts/images/

• Ewr133.gif or GEARS_AN.gif (same)
  
• Kpiprogressbar.gif
  
• Ewr120.gif
  
• Crperspc.gif
  

These gifs are on all MOSS servers and can be used if you need a quick progress indicator image.

Ever want the “Incoming Links” page to show up on the wiki page rather than be a separate page?  Well, here is a handy web part you can add to a wiki page.

This provided without warranty, just link to this article and tell folks how you used it.

It uses some clever JavaScript to get the content from that page and asynchronously render it in a web part.

1. Upload the Incoming Links.dwp file to your Web Part Gallery
2. Edit the wiki page in question (not the wiki content, the whole page)
3. Add the Incoming Links web part to the web part zone at the bottom of the page.

This will either prevent you from the hassle of also having to support wikimedia in your environment (which is really great if you can) or buy you time until you can deploy SharePoint 2010.

Here is a handy macro that allows me to set the task priorities in Microsoft Project 2007 based on tasks that I have imported/synchronized to Visual Studio Team System. 

The reason I do this is because this allows me to have dependencies between TFS work items, determine which ones need to happen first, level my resources, and calculate completion dates for the work items.

Attribute VB_Name = "Module2"

Sub SetPriorityFromTFS()

Attribute SetPriorityFromTFS.VB_Description = ""

‘Description

    LevelingOptions Automatic:=False

    For Each T In ActiveProject.Tasks

        Select Case T.Text19

            Case 1

                T.Priority = "900"

            Case 2

                T.Priority = "700"

            Case 3

                T.Priority = "500"

            Case 4

                T.Priority = "300"

            Case 5

                T.Priority = "100"

        End Select

    Next T

    LevelingOptions Automatic:=True

    LevelNow

End Sub

I recently created a XML Web part that uses XSL and the DHS Threat Level web service to render the current threat level in a way that looks exactly like the DHS Threat Advisory image:

Here it is for free to download with no warranty: DHS Thread Advisory.dwp

There is also an image: http://www.dhs.gov/threat_level/current-sm.gif

For fun, link to this post if you use the web part. 

Very handy for testing and help desk calls:

Tip: insert form version with the expression:
substring-before( substring-after( /processing-instruction()[local-name(.) = "mso-infoPathSolution"], 'solutionVersion="'), '"')

Source:  http://www.nivot.org/2008/09/30/WhyVSTO30VisualStudio2008SharePointWindowsWorkflowAndInfoPathMightGiveYouAHernia.aspx

Scenario: “Please respond to this email indicating your t-shirt size and which session you can attend… or whatever”   send to: 40 people.

Read this: http://office.microsoft.com/en-us/access/HA100154271033.aspx