TestingMisconfused

SetCellValueInExcel

It's useful to be able to find cells by name rather than by cell coordinates, so this routine sets the named cell "TestField" to "TestValue" in workbook "TestWorkbook", sheet "TestSheet". Yes, you could just use the .Cells(row, col) method but where's the fun in that? Besides, you just know someday someone will move the cell without telling you.

Note the switch from dot notation to brackets to show how either can be used in c#script - using brackets allows you to use variable names to refer to objects. Just remember, when a variable name is in [brackets] it is evaluated as an object and when it's in (braces) it's a parameter - and have fun debugging your scripts!

function SetCellValueInExcel
    (TestWorkbook, TestSheet, TestField, TestValue)
{
    var MSexcel;
    MSexcel = Sys.OleObject("excel.application");
    MSexcel.Visible = true;
    MSexcel.Workbooks.Open(TestWorkbook);
    MSexcel.Workbooks(TestWorkbook).Activate();
    MSexcel.Worksheets(TestSheet).Activate();
    //For ActiveX objects in the sheet, check for CheckBox
    //and ComboBox names (use Design mode
    //in Excel to see the names)
    if (TestField.indexOf("CheckBox") > -1)
    {
        MSexcel["ActiveSheet"][TestField]["Object"]["Value"]
            = true;
        MSexcel["ActiveSheet"][TestField]["Value"]
            = TestValue;
    }
    else
    {
        if (TestField.indexOf("ComboBox") > -1)
        {
            MSexcel["ActiveSheet"][TestField]["Value"]
                = TestValue;
        }
        else 
        //Named cell
        {
            MSexcel.Names(TestField).RefersToRange.Value2
                = TestValue;
        }
    }
}
                

Running a method in an Add-in (or a macro)

You may be using or testing an Excel add-in, so how do you call the methods in it?

The answer is, you call a method the same way you would run a macro, but remembering to reference the add-in explicitly. For instance to call TestMethod in MyAddIn:

Sub ExcelAutoTest
  Set MSExcel = Sys.OleObject("excel.application")
  MSExcel.Visible = TRUE
  MSExcel.WindowState = -4137 'maximised
  MSExcel.Workbooks.Add
  'Reactivate the add-in
  if Sys.OSInfo.Name = "WinVista" then
    MSExcel.AddIns.Item("My Add In ribbon").Installed = FALSE
    MSExcel.AddIns.Item("My Add In").Installed = FALSE
    Set MSExcel = Sys.OleObject("excel.application")
    MSExcel.AddIns.Item("My Add In ribbon").Installed = TRUE
    MSExcel.AddIns.Item("My Add In").Installed = TRUE
  else
    MSExcel.AddIns.Item("My Add In").Installed = FALSE
    MSExcel.AddIns.Item("My Add In").Installed = TRUE
  End If
  MSExcel.Run "MyAddIn.xla!TestMethod", "Parameter 1", "Parameter 2", "", "Parameter 4"
End Sub