Creating Excel file + inserting values

[Bertrand]

Hello,

I am trying to archieve the following:

- copy an excel file present on the server
- insert values into named ranges of the copy

I am using Excel97 on my PC, the server does not have
Excel installed on it so I can't create the object
Excel.Application

The server runs Microsoft-IIS/4.0

Although things seem to work, I have a couple of issues:
- when I insert a value into the new copy, the named range
is deleted
- the value is inserted into the worksheet in the cell
BELOW the named cell.
- I don't know how to refer to a cell by its address
instead of a named range.

I am using the following code:
<%

Dim sSourceXLS
Dim sDestXLS

sSourceXLS = Server.MapPath(".") & "\test2.xls"
sDestXLS = Server.MapPath(".") & "\AutoCRF2.xls"

'Copy the source workbook file (the "template") to the
destination filename
Dim fso
Set fso = Server.CreateObject
("Scripting.FileSystemObject")
fso.GetFile(sSourceXLS).Copy sDestXLS
Set fso = Nothing

'Open the ADO connection to the destination Excel
workbook
Set oConn = Server.CreateObject("ADODB.Connection")
oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & sDestXLS & ";" & _
"Extended Properties=""Excel 8.0;HDR=NO;"""


'Add values to individual cells
oConn.Execute "Insert into FirstName Values ('Nancy')"
oConn.Execute "Insert into Surname Values ('Leroy')"
oConn.Execute "Insert into SalaryCode Values
('GM0001')"
oConn.Execute "Insert into DateLastPayed Values
('05/09/2003')"
oConn.Execute "Insert into Address Values ('30/1
Pilrig House Close')"
oConn.Execute "Insert into Telno Values ('0131-
5663452')"


oConn.Close

%>

Does someone know why the cell below the named cell is
updated?
why is the name deleted?
and/or
How to refer to a range by its address instead of its name.

Any help would be greatly appreciated,

Thanks,

kindest regards,

Bertrand


I am using

 

[Ray at]

Hi Bertrand,

When using named ranges, you have to name the cell that is above the one
that you want to insert into. Think of the named range as a column header
in a table design or something.

Ray at work

 

[Ray at]

I don't know of any way to say like "update [sheet1$] set [f1]='some value'
where ADDRESS='$a$1'"

And it seems that all the ADO samples out there for Excel concentrate mostly
on SELECTS, INSERTs, or UPDATEs that don't specify any range info.

Maybe someone else knows more?

Ray at work