Programming with Automation: Getting Started

Positronicus

New Member
Joined
Feb 25, 2011
Messages
12
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!
 
Last edited:

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,263
Office Version
  1. 365
Platform
  1. Windows
How about the Excel VBA help files?

That code you've posted could actually run in VBA as it stands with only a few minor changes.
 

obiron

Active Member
Joined
Jan 22, 2010
Messages
469
What would jScript do that VBA does not (not being faceious because I don't know.... but the sample you provided looks just like VBA; they are both manipulating the Excel DOM)

Assuming little or no difference then there are plenty of good Excel VBA books out there - get a 2nd hand one from Amazon. If the differences are significant then the msdn site has reasonable documentation about all Office Interop features, which I suspect Jscript will be accessing.

Final point (and slightly OT), If you are looking to load data and present it graphically, your time MAY be better invested in installing, learning and delivering your 'solution' in SQLExpress 2008 (or 2010) and Reporting Services. A lot of the time, the questions in here (and on most Excel Boards) the bigger answer would be DONT DO IT IN EXCEL!!

At the end of the day Excel is a spreadsheet and the two things it does best are:
A) what if type calculations where values change on a cascading basis. B) Pivot Tables for summation and aggregation of data with multiple dimensions

If you are using Excel to cross reference two or more data sets or track changes in data over time, you are probably better off with a full RDBMS solution like Access, MSSQL or mySQL whcih tend to scale more more elegantly than Excel. INDEX and VLOOKUP are no substitue for table indexes and referential contraints in a proper database solution.

Having said that, the things you CAN do with Excel are pretty awesome, but most would be easier in another product better suited to the task.

Obiron
SQL Expert and Enough Excel VBA to get me into trouble ;)
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,263
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Obiron

I believe Excel's quite a dab hand at chart's and things.:)
 

Positronicus

New Member
Joined
Feb 25, 2011
Messages
12
Thanks!

This piece of the post gets a 10 out of 10:

The macro recorder in Excel is a good place to start. Or search the web for a free Excel VBA tutorial - here's one: http://www.anthony-vba.kefra.com/

It is a specific reference, which, as a beginner, is exactly what I need! It tells me exactly what I need to do to get started. Thanks!

This piece unfortunately gets a zero:

Or you could buy an Excel VBA book.

That is not specific. Which book? And why would I want to buy it? As a beginner, I lack the knowledge needed to make the right connections. Why would I want to buy a book on BASIC when I intend to use javascript? (Ok, I can figure that out, but more importantly,) What do Visual Basic macros have to do with javascript automation interface? Are they somehow the same thing? Is that obvious to everyone but me?
 
Last edited:

obiron

Active Member
Joined
Jan 22, 2010
Messages
469

ADVERTISEMENT

@Norie
I believe Excel's quite a dab hand at chart's and things
\

Excel 2003 is a dog for graphs and charts. Excel 2007 is better. HAve you seen what you can do in Reporting Services!!!!

@Postonicus
TOUCHY!! :)

You don't say what version of Excel you are using. You don't say whether you are PC or MAC based (VBA is not in Excel 2007 for MAC apparently!!). You don't say what your general level of programming knowlege is, but if you are playing with scientific instruments I presume you are pretty bright.

If you are loading a pre-made CSV file, why use anything other than VBA? If you are creating the spreadsheet from within another application then this is a different matter...

Book suggestions from series that I have used in the past and found easy to comprehend (but not used these particular volumes)

Excel 2003 VBA Programmer to Programmer
http://www.amazon.com/Excel-2003-Pr...06/ref=sr_1_25?ie=UTF8&qid=1298647447&sr=8-25

Pro Excel 2007 VBA
http://www.amazon.com/Pro-Excel-200...78/ref=sr_1_16?ie=UTF8&qid=1298647676&sr=8-16


Obiron.
 
Last edited:

Positronicus

New Member
Joined
Feb 25, 2011
Messages
12
What would jScript do that VBA does not ... they are both manipulating the Excel DOM ... there are plenty of good Excel VBA books out there ...

They are manipulating the what? Where is it documented?

JScript and VB probably do the same thing, but I have been writing code in JScript for years and in VB not at all. It will be easier for me to do it using JScript.

"Plenty of good books" -- the name of ONE would be helpful, thanks.

... If you are looking to load data and present it graphically, your time MAY be better invested in ... SQLExpress ... DONT DO IT IN EXCEL!!

That is a very excellent point, and if someone can suggest a better method for creating scatter plots from CSV files, I'm all ears. I must confess that using a DBMS as a graphics device did not occur to me at all. I am only trying to use Excel as a graphics device because I know it is (somehow) possible.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,263
Office Version
  1. 365
Platform
  1. Windows
Well I've been using Excel since about 97 for charts etc with data from all sorts of sources and it works fine.

As for Reporting Services, isn't that kind of an SQL Server thing?
 

Positronicus

New Member
Joined
Feb 25, 2011
Messages
12
You don't say what version of Excel you are using. You don't say whether you are PC or MAC based (VBA is not in Excel 2007 for MAC apparently!!). You don't say what your general level of programming knowlege is, but if you are playing with scientific instruments I presume you are pretty bright.

If you are loading a pre-made CSV file, why use anything other than VBA? If you are creating the spreadsheet from within another application then this is a different matter...

Book suggestions from series that I have used in the past...

Excel version 2003 (alas), on Windows. I have been doing scientific programming using C++ for many, many years, hence my preference for javascript. The pre-made CSV files do indeed come from other applications.

The whole context is -- I start with many data sets, of about a gigabyte each, from this particular instrument, and analyze it using a variety of programs, all under the control of a master script. To analyze all the cases takes about a week. At the end of the week, I'm left with perhaps 100 CSV files with maybe 22,000 rows and 2 dozen columns representing various time domain signals and their Fourier transforms. I need some way of summarizing the data graphically that doesn't involve click-click-clicking through 100 CSV files. I think it can be done using Excel automation, which would fit pretty well within my existing programming environment.

Thanks VERY MUCH for book suggestions.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,127,151
Messages
5,623,058
Members
415,949
Latest member
mcrandall99

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top