Excel & VBScript Index Page

To help usability of Nerds-Central, I have created this index page for posts covering Excel & VBScript.

Baby Steps Code

Here is all the VBScript code extracted directly from the text of Baby Steps. If you are reading the book and want to follow the code without too much typing - go here :)

The VBScript/Excel Forums

Here are the forums for discussing all things VBScript and Excel, including a special forum just for Baby-Steps.

Using Excel As A Web Service Client

Based on a question from the Baby-Steps forum I have made a straight forward example to show HOW SIMPLE it really is to make Excel a web service client.

Excel Filter Report Scripting Macro

Here is a general example of how to filter data in a spreadsheet using VBScript. It is in response to a set of questions posted to the Scripting Macros forum.

Using IE as a GUI for VBScript.

You want some user interaction with a script, but you don't want to write an HTA. This is a very simple to use class that allows you to do just that. The DB Visualizer post (below) is a full worked example of using it.

DB Visualiser With Just Excel And VBScript

A full worked example of using IE as a GUI for VBScript. This also shows advanced dynamic interaction between ADODB data sources and Excel. Some nice acceleration tricks in this post.

Very Fast Loading Of Record Set Into Excel

Excel makes a great way to look at record sets. Using it to read data directly from SQL Server, dbf files, Access databases - all cool. The snag is that things like MS Query are nothing like as easy to distribute and enhance as a nice simple VBScript. Here is a VBScript way to load record sets in Excel really fast, and have the column headers etc.

Excel 2007 - Reading DBF Files At Super Speed!

This post is a worked example of using the above method for loading record sets into Excel. It loads dbf files into Excel in an instant.

Write DBF File From Excel 2007 With VBScript

Excel 2007 will no longer write dbf files. No problem - use this script!

Reading Data From A Binary File And Loading It Into Excel

This is one of the examples from the very end of Baby Steps for 'out of the box' thinking. I wrote this post during the writing of the book. It is not typical of the book as a whole but it does show just how far the techniques I explain in the book can take you. (actually, I am thinking a writing another book which goes a whole lot further!)

Built In Excel Constants

As part of the Baby-Steps project, I created an appendix of all the Excel constants using in Macros. Here it is.

High Speed String Concatenation In 100% Pure VBScript

Often in scripting we want to concatenate a load of little strings into one big one. This can start to get very slow. Just adding them together slows down at a rate that is the square of the number of strings added and/or their length. This post shows a very much faster way of doing it indeed.

Sending Emails Using NetCat With VBScript

This shows how to send emails with VBScript and a little free helper program. It also shows the differences between JScript and VBScript because I once also posted on the same technique in JScript.

All ADODB Constants In VBScript

ADODB and VBScript make a powerful combination, but remembering the value of all those ADODB constants can be tricky. Well - you don't have to!

Using VBScript To Paste Text Into Notepad

There is no COM interface to Notepad, so we are going to have to use the old SendKeys method. So here is a quick crib on how that works.

How To Excel VBScript Autofit

So you want to autofit Excel Columns or Rows from VBScrpt - here is how.

Mixing VBScript And JScript, The Magic Of WSFs

If you hunt around the Internet you will probably find lots of examples of how to write wsf files. Here is my experiance of how to write them easily to allow the mixing of VBScript with JScript. You see, no matter how much one might say that VBScript is better than JScript or JScript is better than VBScript, there are times when mixing them is better than being forced to work in just one.

Setting Excel Borders From VBScript Or JScript

In response to the following Google search: "script excel .borders createobject" here is how to do it.

VBScript Arrays Push

Sometimes I get hits on Nerds-Central when people are looking for ways to 'push' data only VBScript arrays. Well - you cannot :( This post explains why you cannot, and offers an alternative implementation to a normal array which lets you push, pop etc.

Excel: Copy And Paste Cells/Rows/Columns Using JScript/VBScript

A very early post on the subject, but still popular.

Working With Excel From VBScript

This is one of the very first Nerds-Central posts on the subject, and still gets a lot of hits!

Data Load & Templating From Excel

Data load is always a pain. How about simple, business friendly ways to make it easier and more reliable? Excel and Scripting Macros might just be the solution.

Simple SQL Escape Library For VBScript

Writing VBScript to talk to a database can be dangerous if we just write out SQL and submit it. This leaves us open to all sorts of problems with strange bugs all the way through to SQL injection attacks. Here is a super simple set of functions which can really help avoid this!

Recursive Registry Hive Listing For Test Purposes

A simple VBScript which uses WMI and regread to recursively write out a text description of an entire registry hive. This script is useful for testing and debugging systems which make registry edits.

VBScript to back up SQL Server data to pure SQL

"Repeatedly over the last 8 years I have needed to do the same thing - back up SQL Server (actually - it started with Sybase 11) to pure SQL. I started out using Perl - but here is a pure VBScript evolution of the technique I devised."

BabySteps Corrections

Some time ago a very gifted co-worker help me by spotting some corrections in Baby-Steps. I was hoping to make a new addition with them all included but simply have not had the time; no-one has complained either. One day I shall make a second edition, but it will not be any time soon. Anyhow - here they are so they don't get lost and anyone can double check if something does not make sense.

Column Uniquing In Excel Via VBScript

I have had a few requests from work lately for a script to find all the unique values in a column, or all the values in a column that are not in another column. Here is an example solution.

Simple Persistent Transactional Dictionary

Recently I have been working on a project to send emails when events happen. I have taken an aggressively simple approach: Use VBScript,Do not use a database,Make it robust and restartable. Part of the solution is a persistent, semi-transactional extension of the Scripting.Dictionary object written in pure VBScript. This post explains how it works and gives the code.

Excel/Vibscript: Formating, Pivot Tables And Conditional Formatting

This post is based on a real business example of formating an Excel report, creating a Pivot Table and then using conditional formatting in the table.

MySQL/VBScript/Excel: Making Excel reports from MySQL

Here is an example I made of loading data straight from MySQL to Excel.