Programming with Automation: Getting Started

[Positronicus]

Shortest version of the question:

I want to learn to program Excel, using JScript, through its Automation interface. I can't find a comprehensive programmers reference manual detailing all aspects of the interface. Can you suggest one?

Intermediate length version:

I made progress! But it took me all afternoon to write this simple program:

app = WScript.CreateObject("Excel.Application");

wb = app.workbooks.add;

wb.Activesheet.Range("A1").Value = "TestData";
wb.Activesheet.Range("A2").Value = "MoreData";

app.Visible=true;

WScript.Sleep(5000);

app.quit();

Where can I find complete documentation on things like "app.workbooks.add" and "wb.Activesheet.Range("A1").Value", which is not scattered about on random web pages or in "How you would do this (if you actually had some documentation)" how-to articles?

Longest version:

Here's the real application: I have data from a scientific instrument rendered in comma-separated-value format. I can read the CSV file and manually create various charts, but it is impossibly tedious to do that by hand in every case. I would like to write a javascript which,

* Opens a CSV file and reads it
* Selects columns A, E, and F (for example)
* Does simple math within certain ranges (e.g. averages, max & min)
* Creates one or more scatter plots of a certain type
* Adds labels to the axes, changes X & Y ranges, turns off gridlines, etc.
* Saves the results as .xls for later printing (or print now)

That is to say, I would like to use Excel as a programmable graphics device. Obviously I need an excellent reference manual to puzzle it all out. Where should I start?

Thanks!

 

[obiron]

They are manipulating the what

The DOM: Document Object Model.

It basically describes how different bits of Excel are linked and work together.

DOM 101:

At the top is the Application - Excel
Within the application you will have one or more Workbooks
Within a workbook you will have one or more sheets. These will mostly be worksheets but may also be chart sheets.
Within a worksheet you will have Rows, Columns, Cells and Ranges
A Cell will have properties: Value, Background colour, border etc...

Each level of the DOM will also have properties. The workbook will have a name and a file location, last saved datetime etc... The worksheet will have a name, a sequence index, a visible/hidden status etc..


Most VBA (or JScript) code will be to perform logic statements and modify the properties of a particular element or elements. Many parts of the DOM are pretty easy to understand as per the above example. Some parts are pretty fiendish (like pivot tables...)

 

[obiron]

@Norie

Yes Reporting services is an SQL server thing, which is why I suggested using it with SQLExpress. That said, it can work with a number of data sources including XML, Excel and plain old CSV, but works best with RDBMS.

@Positronicus

You should be able to automate and Excel workbook to search a directory or directories and automatically load the relevant CSV files and do whatever charts are required.

Most of what the master application is doing went over my head, but it sounds like MSSQL may be a better supersolution where you load all of the original data (SQLExpress is limited to 4 GIG) and slice and dice it within there - if you can invest the time for a total re-write.

 

[Norie]

The DOM? Isn't that the Document Object Model for html/xhtml based on some sort of protocol?

Anyway, here's a link which includes the object model for Excel http://msdn.microsoft.com/en-us/library/ff846392.aspx.

Not sure where exactly it includes an actual 'model' - I think I saw a diagram once, but it's quite a useful resource.

PS It's for Excel 2010 but you should find information for other versions too if you have a dig about.

You might need that if you are going to be automating Excel to create graphs etc, they've changed that part of the 'model' a few times.

 

[shg]

My background is in Fortran, was away from programming for a long time, and then spent a few months writing C. I started in VBA about eight years ago, and find VBA to be a wonderfully simple and intuitive language. It only has about a dozen objects.

The larger problem is the Excel model, with its hundreds of objects; it's not complex, there is just a lot of it. That part you are going to have to learn anyway, so crossing to VBA is, IMO, the far smaller hurdle. And it goes with Excel like peanut butter with jelly.

 

[John_w]

I want to learn to program Excel, using JScript, through its Automation interface. I can't find a comprehensive programmers reference manual detailing all aspects of the interface. Can you suggest one?

There are 2 aspects to consider: the Excel Object Model, which you can control via automation using JScript/Javascript/VBScript; and the VBA language itself (keywords, functions, operators, etc.) which is specific to Excel/Access/Word etc. and can only be used within a VBA module or class in Excel/Access/Word etc.

There is an Excel 2003 object reference at http://msdn.microsoft.com/en-us/library/aa272268(v=office.11).aspx. You don't say which version of Excel you have, but the 2003 object model also applies to Excel 2007 and 2010, with some additional objects for those later versions.

More importantly for you, I think, is how to use the Excel objects, and there is a reference for this at http://msdn.microsoft.com/en-us/library/aa272260(v=office.11).aspx.

For the VBA language, there is help in the Visual Basic editor in Excel, or you can download it from http://www.microsoft.com/downloads/...2e-4877-4563-8e83-4848dda796e4&displaylang=en. The VB editor also has help for the Excel object model and programming concepts - the same as the MSDN links above.

For writing a JScript Excel program, I suggest you develop the code in Excel VBA, using the Macro Recorder to help you, and then port the code to JScript. The main change required for JScript is the need for a variable which references the Excel application, for example in your code:

app = WScript.CreateObject("Excel.Application");

and then use that 'app' reference where required, for example in the next line:

wb = app.workbooks.add;

Note - if you create a new blank workbook with the Macro Recorder running, the code generated is:

Workbooks.Add

Because the code is within Excel, there is an implied (Excel) Application object at the start of that line, like this:

Application.Workbooks.Add

For JScript, you need to specify the Excel Application object reference, as your code does with the 'app' variable.

The other change for JScript is to end each statement in a semi-colon (that might be optional - it is for Javascript), and to convert any VBA language-specific keywords, functions, etc. to their JScript equivalents.

 

[Positronicus]

For writing a JScript Excel program, I suggest you develop the code in Excel VBA, using the Macro Recorder to help you, and then port the code to JScript ...

Many thanks to everybody who responded to this thread. Y'all are genuinely helpful, generous, knowledgeble, and insightful. This is a great board.

Here's where I am, about 5 hours after the original post. Step 1 is to record a macro. Step 2 is to translate to JScript.

Step 1 -- Record a Macro

I enabled macro recording, loaded one of my data files, selected columns A, C, and I, made a scatter plot, and changed the X & Y axis ranges, chart title, axis title, cleared the gridlines, etc., then saved the result. That produced the following macro:

Sub Macro1()
 
    ChDir "C:\ScriptPlay"
    Workbooks.Open Filename:= _
        "C:\ScriptPlay\TestFile.csv"
    Range("A:A,C:C,I:I").Select
    Range("I1").Activate
    Charts.Add
    ActiveChart.ChartType = xlXYScatterLinesNoMarkers
    ActiveChart.SetSourceData Source:=Sheets("TestFile").Range( _
        "A:A,C:C,I:I")
    ActiveChart.Location Where:=xlLocationAsObject, Name:="TestFile"
    ActiveChart.Axes(xlValue).Select
    With ActiveChart.Axes(xlValue)
        .MinimumScale = -4
        .MaximumScale = 6
        .MinorUnitIsAuto = True
        .MajorUnitIsAuto = True
        .Crosses = xlCustom
        .CrossesAt = -4
        .ReversePlotOrder = False
        .ScaleType = xlLinear
        .DisplayUnit = xlNone
    End With
    ActiveChart.Axes(xlCategory).Select
    With ActiveChart.Axes(xlCategory)
        .MinimumScaleIsAuto = True
        .MaximumScale = 100
        .MinorUnitIsAuto = True
        .MajorUnitIsAuto = True
        .Crosses = xlAutomatic
        .ReversePlotOrder = False
        .ScaleType = xlLinear
        .DisplayUnit = xlNone
    End With
    ActiveChart.Axes(xlValue).MajorGridlines.Select
    Selection.Delete
    ActiveChart.PlotArea.Select
    Selection.ClearFormats
    ActiveChart.PlotArea.Select
    With ActiveChart
        .HasTitle = True
        .ChartTitle.Characters.Text = "This is the chart title"
        .Axes(xlCategory, xlPrimary).HasTitle = True
        .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = _
        "This is the X axis label"
        .Axes(xlValue, xlPrimary).HasTitle = True
        .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = _
        "This is the Y axis label"
    End With
    ActiveWorkbook.SaveAs Filename:= _
        "C:\ScriptPlay\TestFile.xls", _
        FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
        ReadOnlyRecommended:=False, CreateBackup:=False
    ActiveSheet.ChartObjects("Chart 1").Activate
End Sub

Step 2 -- Translate to JScript

I had to fight with this a little, and I cannot yet get the chart.Location() function to work correctly. Otherwise, this seems to work:

True = 1;
False = 0;
xlValue = 2;
xlPrimary = 1;
xlCategory = 1;
xlNone = -4142;
xlCustom = -4114;
xlLinear = -4132;
xlAutomatic = -4105;
xlXYScatterLinesNoMarkers = 75;
xlLocationAsObject = 2;
 
Excel = WScript.CreateObject("Excel.Application"); 
 
Excel.Visible = true;
 
wb = Excel.Workbooks.Open("C:\\ScriptPlay\\TestFile.csv");
 
wb.Activesheet.Range("A:A,C:C,I:I").Select;
chart = wb.Charts.Add;
chart.ChartType = xlXYScatterLinesNoMarkers;
chart.SetSourceData( wb.Sheets("TestFile").Range("A:A,C:C,I:I") );
 
chart.Axes(xlValue).Select;
with( chart.Axes(xlValue) ){
    MinimumScale = -4;
    MaximumScale = 6;
    MinorUnitIsAuto = True;
    MajorUnitIsAuto = True;
    Crosses = xlCustom;
    CrossesAt = -4;
    ReversePlotOrder = False;
    ScaleType = xlLinear;
    DisplayUnit = xlNone;
}
 
chart.Axes(xlCategory).Select;
with( chart.Axes(xlCategory) ){
    MinimumScaleIsAuto = True;
    MaximumScale = 100;
    MinorUnitIsAuto = True;
    MajorUnitIsAuto = True;
    Crosses = xlAutomatic;
    ReversePlotOrder = False;
    ScaleType = xlLinear;
    DisplayUnit = xlNone;
}
 
chart.Axes(xlValue).HasMajorGridlines = False;
chart.PlotArea.ClearFormats(); 
 
chart.PlotArea.Select;
with( chart ){
 HasTitle = True;
 ChartTitle.Characters.Text = "This is the chart title";
 Axes(xlCategory, xlPrimary).HasTitle = True;
    Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "This is the X axis label";
    Axes(xlValue, xlPrimary).HasTitle = True;
    Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "This is the Y axis label";
}
 
wb.SaveAs("C:\\ScriptPlay\\TestFile.xls");
 
Excel.quit();
WScript.Quit();

I think this problem is close enough to solved! Thanks for all your help!