RyanDev.com

Many people use Excel to analyze data and to “watch” data.  Did you know that you can have Excel play a sound or even a song when a cell changes if you want to alert someone?  In fact, you could trigger any kind of workflow you wanted.  This example shows how to call into the Win32 API using Excel to play a sound.

We first need to declare to Excel how to call into the Win32 API.  We also want to declare a public variable to keep track of the value in cell A1 so we can know when it changes.

First, add a module to the workbook that you will be using and add the following 2 lines of code:

Declare Function sndPlaySound32 Lib “winmm.dll” Alias “sndPlaySoundA” (ByVal lpszSoundName As String, ByVal uFlags As Long) As Long
 
Public CellValue As Variant ’store the cell value here

The first line is how you make Excel aware of the Win32 API call to play a sound.  The second line of code is declaring the global variable.

Second, we want to read in the value of cell A1 when the workbook first opens so we can keep track of when it changes.  Use the following code:
Private Sub Workbook_Open()
CellValue = Sheets(1).Range(”a1″)
End Sub

Now that we are storing what the value is and we have told Excel how to play the sound we now need to check the cell each time something on that sheet changes.  Use the following code:

Private Sub Worksheet_Calculate()
If Range(”a1″) > 1 And CellValue <> Range(”a1″) Then
    sndPlaySound32 “C:\Error.WAV”, 0
    CellValue = Range(”a1″)
End If
End Sub

‘if you want it to play the sound every time anything on the sheet changes, delete out the
‘parts about CellValue

Obviously you can add in any logic you would like.  You could choose to send an email, print the document to the printer or even using Unified Communications (UC) services (from companies such as http://www.engage2day.com) you could have the system IM someone or even have the system call them and let them know a threshold has been reached.  This may be overkill for an Excel spreadsheet but it certainly opens up the possibilities of what you can do with a simple Excel sheet.

The Page Setup window of Excel does not offer many choices for adding dynamic information into the header and footer.

To add the time the file was last saved use the following code:

Sub LastSavedTimeInFooter()
ActiveSheet.PageSetup.CenterFooter = ActiveWorkbook.BuiltinDocumentProperties(”Last Save Time”)
End Sub

Note, the above code will have to be manually run.  To have this happen automatically you could do the following:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
ActiveSheet.PageSetup.CenterFooter = ActiveWorkbook.BuiltinDocumentProperties(”Last Save Time”)
End Sub

With this code it would automatically run every time you had to print and would always be updated on your hard-copies. 

When you create new documents in Microsoft Word, for example, when you click save Word will often suggest a name of Document1.  If you create a new document and click save Word will suggest Document2 and so on until you restart Word and then it will be Document1 again.  Since Microsoft InfoPath is more web-based it always suggests the same name when saving a document to a SharePoint repository. 

Sometimes you want to auto-increment the number attached to the file name.  Maybe you use InfoPath forms to fill out PTO requests in your organization and instead of everyone saving one with the default name you want to control that name.  Below is some easy to use code to increment the number that is stored on the end of the file-name regardless of who in your organization is saving the form to the library.  You will want to modify the OnSubmitRequest of the Form in InfoPath and add in the custom code.

---

function XDocument::OnSubmitRequest(eventObj)
{
    // If the submit operation is successful, set
    // eventObj.ReturnStatus = true
    var fSuccessful = false;
 
 
 //start of custom code
 var fso,d, f,ts;
 fso = new ActiveXObject(”Scripting.FileSystemObject”);
 //Change the following path to a file that everyone has acccess to or have some fun with AJAX and make a db call.
 //Note: In Java Script to do a “\” you actually need 2, “\\”
 //NextNumber.txt needs to be a text file with only a # in it.  Start it at 0.
 f = fso.GetFile(”\\\\servershare\\InfoPathForms\\NextNumber.txt”);

 ts = f.OpenAsTextStream(1,-2);
 s = ts.ReadLine( );
// Read in the value from the file that will be used as the next document number
        s++;
        ts.Close( );
// Now open the file to write back the next number after incrementing it
        ts = f.OpenAsTextStream(2,-2);
        ts.Write( s );
        ts.Close( );
 //end custom code
    // Set the URL of the file that you want to submit here.
    var strUrl = “http://servername/InfoPathPTOForms/PTORequest” + s + “.xml”;
 
    try
    {
        // Create an xmlhttp object.
        var oXmlHttp = new ActiveXObject(”MSXML2.XMLHTTP”);
 
        // Check whether the document with the same name already exists on SharePoint Team Services (STS).
        oXmlHttp.Open(”HEAD”, strUrl, false);
        oXmlHttp.Send();
 
        //  No document with the URL has been found. Continue to submit.
        // If you must replace the original file, you must call
        // oXmlHttp.Open(”DELETE”, strUrl, false) to delete the document
        // on STS first.
        if (oXmlHttp.Status == 404)
        {
            // Put the document on STS.
            oXmlHttp.Open(”PUT”, strUrl, false);
            oXmlHttp.Send(XDocument.DOM.xml);
 
            //  A 200 status code or a 201 status code indicates that the form has been submitted successfully.
            if (oXmlHttp.Status == 200 || oXmlHttp.Status == 201)
            {
                fSuccessful = true;
            }
        }
    }
    catch (ex){}
   
    if (fSuccessful)
    {
        XDocument.UI.Alert(”Document submitted successfully!”);
        eventObj.ReturnStatus = true;
    }
    else
    {
        eventObj.ReturnStatus = false;
    }
}