[ntsc]

i know this is a mac forum but there is a mac question here as well but since i know you guys are all uber knowledgeable i figured this would be as good a place to start as any.

i'm going to be doing a little work for a business and i need to learn how to script excel to do simple stuff like create a new spreadsheet from the columns of others, so to do this i understand i need to learn VB(Script?). first of all i'm AI+CS student and i've got some working knowledge of java how difficult is VB gonna be for me to pick do you reckon? secondly i've been looking for a decent book on the subject and a quick look on Amazon turned up this. Anyone have experience with this book or any better suggestions.

finally the mac part (yes it is here somewhere!), can i do this development with Excel on my Mac (its Excel vX no updates - tis not the most legal version of Office in the world!) or do i need Windows and Excel 2003 or whatever the latest version is. Office 2004 is an option its not overly expensive with my student discount.

thanks in advance!

[ntsc]

anyone?

[DBursey]

I found VB to be quite easy to pick up and apply. Googling will turn up a number of good resources on the web, such as the following, for example:

Visual Basic Tutorial

or this:

Homekey - VB Tutorial

There's a lot of info out there. Hopefully you'll find a tutorial which is germaine to your needs.

[turtle777]

Originally posted by ntsc:
anyone?

Actually, I'm not sure what you really want to know.

Do you look for a book recommendation on VB or are you looking for a book for Excel in general ?

The one specific thing you mentioned ("simple stuff like create a new spreadsheet from the columns of others") does not give me enough of an idea what you want to do. I think you could even use the macro recorder to do that, no need of VB scripts.

Regarding VB compatibility: I thought that Excel vX was supposed to be fully compatible to the Windows Office VB, but I never got into it deep enough to figure out if it's true.

-t

[ringo]

VBScript is easy to pick up, check out VBScript in a nutshell by O'Rielley if you already have strong fundamental programming skills.

Excel automates well using VBS, but google is going to be your best resource when you need to figure out how to do something. I don't know of a good book that teaches excel automation, and Microsoft's documentation on the object model for Excel sucks.

I don't know that VBS will work on a mac, but I haven't tried it so somebody correct me if I'm wrong. I hear Excel can be automated using Python as well, so you might want to look into that for scripting instead. I use vbs at work because its a windows shop anyway and I didn't need to get anyone's permission to make changes to our production servers to run vbscripts there. For windows, you don't need the latest version. I do most of my automation using Office 2000.

Your Java experience is only going to help a little...forget everything you know about objects and remember what you learned about functions, looping, and working with arrays. If you had a C class in there somewhere, VBScript will be a breeze. If not, you may need to struggle a little at first with operations you might have assigned to a nice object to take care of in Java.

Once you get the hang of it, you can do a lot with Excel and VBS. Anything you can do in excel can be scripted. Learn a little about ADO recordsets and manipulating strings and dates in vbs and you can write a mean report writer that will generate its own sql, query a datasource, and produce as complex a report as you would want to see in Excel. Throw a scheduler behind it and you're good to go.