Excel connection

[Andrew]

Hi,

How can I make a connection to an Excel file, and I can select the specific
fields? For example in sheet1:

A B C
1 20 change good
2 short return 50
3 order shipped 3

How can I get the value of "C2" in ASP? I want to the the connection and the
select statement.
Thanks for any help.
--
Andrew

[Manohar Kamath [MVP]]

Andrew,

Look into "Named Ranges" in Excel. Once you have created a named range, for
the ranges of values, you can query Excel as if it were a table.

Considering you will have a header row, the following is a connection
string:

connString = "Provider=Microsoft.JET.OLEDB.4.0;Extended Properties=""Excel
8.0;HDR=YES;"";Data Source=C:\Myfile.xls"

Set conn = Server.CreateObject("adodb.connection")
Conn.Open connString

' Considering C is the header name for third column
Set Rs = conn.Execute("select C from MyNamedRange")
Rs will now contain the data, the question is how to get the second row. You
can either specifically get the second row with your query ("select C from
where A=2")

Hope that helps.

--
Manohar Kamath
Editor, .netWire
www.dotnetwire.com
"Andrew" <du******@yahoo.com> wrote in message
news:u6*****************@news04.bloor.is.net.cable .rogers.com...

Hi,

How can I make a connection to an Excel file, and I can select the specific fields? For example in sheet1:

A B C
1 20 change good
2 short return 50
3 order shipped 3

How can I get the value of "C2" in ASP? I want to the the connection and the select statement.
Thanks for any help.
--
Andrew

[Andrew]

Manohar,

A, B, C... is the Column is the worksheets1(no column name), and 1, 2, 3 ...
is the row. Actually I want the values of more than one field, like A2, B1,
C2 and so on.
Can you help? Thanks anyway.

--
Andrew
"Manohar Kamath [MVP]" <mk*****@TAKETHISOUTkamath.com> wrote in message
news:%2***************@TK2MSFTNGP09.phx.gbl...

Andrew,

Look into "Named Ranges" in Excel. Once you have created a named range, for the ranges of values, you can query Excel as if it were a table.

Considering you will have a header row, the following is a connection
string:

connString = "Provider=Microsoft.JET.OLEDB.4.0;Extended Properties=""Excel
8.0;HDR=YES;"";Data Source=C:\Myfile.xls"

Set conn = Server.CreateObject("adodb.connection")
Conn.Open connString

' Considering C is the header name for third column
Set Rs = conn.Execute("select C from MyNamedRange")
Rs will now contain the data, the question is how to get the second row. You can either specifically get the second row with your query ("select C from
where A=2")

Hope that helps.

--
Manohar Kamath
Editor, .netWire
www.dotnetwire.com
"Andrew" <du******@yahoo.com> wrote in message
news:u6*****************@news04.bloor.is.net.cable .rogers.com...

Hi,

How can I make a connection to an Excel file, and I can select the

specific

fields? For example in sheet1:

A B C
1 20 change good
2 short return 50
3 order shipped 3

How can I get the value of "C2" in ASP? I want to the the connection and

the

select statement.
Thanks for any help.
--
Andrew

[Manohar Kamath [MVP]]

You could use HDR=No, and for column names use 1, 2, 3 instead of A, B, C.

--
Manohar Kamath
Editor, .netWire
www.dotnetwire.com
"Andrew" <du******@yahoo.com> wrote in message
news:UV****************@news04.bloor.is.net.cable. rogers.com...

Manohar,

A, B, C... is the Column is the worksheets1(no column name), and 1, 2, 3 .... is the row. Actually I want the values of more than one field, like A2, B1, C2 and so on.
Can you help? Thanks anyway.

--
Andrew
"Manohar Kamath [MVP]" <mk*****@TAKETHISOUTkamath.com> wrote in message
news:%2***************@TK2MSFTNGP09.phx.gbl...

Andrew,

Look into "Named Ranges" in Excel. Once you have created a named range,

for

the ranges of values, you can query Excel as if it were a table.

Considering you will have a header row, the following is a connection
string:

connString = "Provider=Microsoft.JET.OLEDB.4.0;Extended Properties=""Excel 8.0;HDR=YES;"";Data Source=C:\Myfile.xls"

Set conn = Server.CreateObject("adodb.connection")
Conn.Open connString

' Considering C is the header name for third column
Set Rs = conn.Execute("select C from MyNamedRange")
Rs will now contain the data, the question is how to get the second row.

You

can either specifically get the second row with your query ("select C from where A=2")

Hope that helps.

--
Manohar Kamath
Editor, .netWire
www.dotnetwire.com
"Andrew" <du******@yahoo.com> wrote in message
news:u6*****************@news04.bloor.is.net.cable .rogers.com...

Hi,

How can I make a connection to an Excel file, and I can select the

specific

fields? For example in sheet1:

A B C
1 20 change good
2 short return 50
3 order shipped 3

How can I get the value of "C2" in ASP? I want to the the connection

and the

select statement.
Thanks for any help.
--
Andrew

[Bonj]

This is a VERY bad way of programming - names ranges are unreliable as the size of the data is liable to change, and then some of it will fall outside of the named range. Additionally if data is removed the named range will include rows of nulls which will inevitably make the database fall over.

[Bonj]

Instantiate an instance of Excel (CreateObject("Excel.Application")) and use the Cells(row, column) property of a worksheet object.

[Manohar Kamath [MVP]]

My answer was specific to this problem... of course you can instantiate
Excel objects, but these are not multi-user friendly and take a ton of
memory. Actually, you can set the named range to be the entire worksheet
(although I have not tried querying this).

--
Manohar Kamath
Editor, .netWire
www.dotnetwire.com
"Bonj" <an*******@discussions.microsoft.com> wrote in message
news:AC**********************************@microsof t.com...

This is a VERY bad way of programming - names ranges are unreliable as

the size of the data is liable to change, and then some of it will fall
outside of the named range. Additionally if data is removed the named range
will include rows of nulls which will inevitably make the database fall
over.