Updating a Excel 2007 cell with VBScript/ADO

Hi All,

I have done very little with VBScript so this may be a straight forward question.

I need to update two cells in an Excel 2007 spreadsheet. The cells are in the same column and the column does not have a heading. The system where the spreadsheet is located and the VBScript code are the same. Excel is not and cannot be installed on the system.

The system OS is Windows 2008 R2 SP1 and can have the 2007 Office System Driver Data Connectivity Components installed to allow a ADO connection.

The Excel workbook is located at C:\SysTools\DP_Mthly_Stats.xlsx and contains two worksheets of which the one that needs to be updated is named Prot_Servers.

The code I have so far is lised below. It works to some degree in that as long as the two cells in the Excel worksheet are empty the cells can be updated. If either or both already contain data (which will be the case) I get the Windows Script Host error message;

Script:
C:\SysTools\write_ecel_new.vbs
Line: 34
Char: 1
Error: Cannot expand named range.
Code: 80040E21
Source: Microsoft Office Access Database Engine

Also, when I open the Excel workbook after running the code aginst it and then close it I am prompted to save. I would normally want to have the data saved as part of running the code.

Code snippet:

Const adOpenStatic = 3
Const adLockOptimistic = 3

Num_VM=40
Num_Phy=70

Set oConn = CreateObject("ADODB.Connection")
oConn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\SysTools
\DP_Mthly_Stats.xlsx;Extended Properties=""Excel 12.0 Xml;HDR=NO"";"
oConn.Open


query = "Select * from [Prot_Servers$B3:B3]"
Set rs = CreateObject("ADODB.Recordset")
rs.Open query, oConn, adOpenStatic, adLockOptimistic

rs.AddNew
rs.Fields(0).Value = Num_VM
rs.update

rs.close

query = "Select * from [Prot_Servers$B4:B4]"
Set rs = CreateObject("ADODB.Recordset")
rs.Open query, oConn, adOpenStatic, adLockOptimistic

rs.AddNew
rs.Fields(0).Value = Num_Phy
rs.update

rs.close

Set oConn = Nothing