UPDATE: 25/09/2012 – Macro Scheduler now includes some native Excel functions (XLOpen, XLGetCell, XLSetCell) etc. DDE is also no longer supported in the latest version of Excel. Using DBQuery, as described here, is still a very useful way to retrieve Excel data, especially for doing SQL style lookups.
Last July I wrote this post summarising three different ways Macro Scheduler can read/modify Excel data including using DDE to quickly retrieve/modify cells, and VBScript to script pretty much anything in Excel. Example scripts demonstrating both methods, and an example.xls file ship with Macro Scheduler.
Well, now with the native database functions there’s another way. DBConnect can connect to Excel and treat it as a database, using one of the following connection strings:
OLE DB:
Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\myfolder\workbook.xls; Extended Properties=”Excel 8.0; HDR=No;”
ODBC:
Driver={Microsoft Excel Driver (*.xls)}; DriverId=790; Dbq=c:\myfolder\workbook.xls;
Either should work if you have Excel installed. Note that HDR=No in the first connection string tells ConnectDB to retrieve the first row. Without it the first row is treated as column names and not retrieved. This is not supported in the second method. See Microsoft KB257819 for more info and other options.
So the following code will retrieve the entire contents of Sheet1 into an array:
Let>connStr=Provider=Microsoft.Jet.OLEDB.4.0;Data Source=%SCRIPT_DIR%\example.xls;Extended Properties="Excel 8.0;Hdr=No;" DBConnect>connStr,dbH Let>SQL=select * from [Sheet1$] DBQuery>dbH,SQL,rsSheet1,nR,nF DBClose>dbH
This reads everything in Sheet1 from example.xls which is stored in the same folder as the macro (SCRIPT_DIR). Just modify the path in the Data Source= part of the connection string to point to a different workbook.
The entire sheet is now in the rsSheet1 array. nR contains the number of rows and nF the number of columns (records and fields). So rsSheet1 looks like:
rsSheet1_1_1 .. nsSheet_1_nF
..
rsSheet1_nR_1 .. nsSheet_nR_nF
A nice quick way of sucking an entire worksheet into a MacroScript array.
Note that you have to use [Sheetname$] as the table name. According to Microsoft you can also use named ranges, or unnamed ranges:
Named Range:
- SELECT * FROM MyRange
Unnamed Range:
- SELECT * FROM [Sheet1$A1:B10]
To insert/modify data you first need to name the columns in your worksheet. You can then do something like:
- INSERT INTO [Sheet1$]([First Name], [Last Name]) VALUES (‘John’, ‘Smith’)
Where “First Name” and “Last Name” are names given to columns. The data will be added at the first blank row.
See also:
Using Macro Scheduler’s Database Functions
Methods for Accessing Excel Data
Update: Here’s a connection string for Excel 2007:
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=%SCRIPT_DIR%\example.xlsx;Extended Properties="Excel 12.0 Xml;HDR=Yes";
