By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,617 Members | 1,792 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,617 IT Pros & Developers. It's quick & easy.

Creating Excel file + inserting values

P: n/a
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
Jul 19 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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
"Bertrand" <be******@bertrandyesyes.com> wrote in message
news:05****************************@phx.gbl...
Set oConn = Server.CreateObject("ADODB.Connection")
oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & sDestXLS & ";" & _
"Extended Properties=""Excel 8.0;HDR=NO;"""

Does someone know why the cell below the named cell is
updated?

Jul 19 '05 #2

P: n/a
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

"Bertrand" <df********@sdfgdfgd.cvom> wrote in message
news:02****************************@phx.gbl...
Do you also know if it possible (and if so how) to target
a cell by refering to it by its address instead of its
name?

thanks,

Bertrand

Jul 19 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.