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]]

Re: Excel connection

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=Micro soft.JET.OLEDB. 4.0;Extended Properties=""Ex cel
8.0;HDR=YES;""; Data Source=C:\Myfil e.xls"

Set conn = Server.CreateOb ject("adodb.con nection")
Conn.Open connString

' Considering C is the header name for third column
Set Rs = conn.Execute("s elect 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

https://www.dotnetwire.com

"Andrew" <dunggaze@yahoo .com> wrote in message
news:u6Gac.2260 $86a.1247@news0 4.bloor.is.net. cable.rogers.co m...[color=blue]
> Hi,
>
> How can I make a connection to an Excel file, and I can select the[/color]
specific[color=blue]
> 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[/color]
the[color=blue]
> select statement.
> Thanks for any help.
> --
> Andrew
>
>
>[/color]

[Andrew]

Re: Excel connection

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]" <mkamath@TAKETH ISOUTkamath.com > wrote in message
news:%23d7ZYc2F EHA.700@TK2MSFT NGP09.phx.gbl.. .[color=blue]
> Andrew,
>
> Look into "Named Ranges" in Excel. Once you have created a named range,[/color]
for[color=blue]
> 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=Micro soft.JET.OLEDB. 4.0;Extended Properties=""Ex cel
> 8.0;HDR=YES;""; Data Source=C:\Myfil e.xls"
>
> Set conn = Server.CreateOb ject("adodb.con nection")
> Conn.Open connString
>
> ' Considering C is the header name for third column
> Set Rs = conn.Execute("s elect C from MyNamedRange")
>
>
> Rs will now contain the data, the question is how to get the second row.[/color]
You[color=blue]
> 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" <dunggaze@yahoo .com> wrote in message
> news:u6Gac.2260 $86a.1247@news0 4.bloor.is.net. cable.rogers.co m...[color=green]
> > Hi,
> >
> > How can I make a connection to an Excel file, and I can select the[/color]
> specific[color=green]
> > 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[/color]
> the[color=green]
> > select statement.
> > Thanks for any help.
> > --
> > Andrew
> >
> >
> >[/color]
>
>[/color]

[Manohar Kamath [MVP]]

Re: Excel connection

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

--
Manohar Kamath
Editor, .netWire

https://www.dotnetwire.com

"Andrew" <dunggaze@yahoo .com> wrote in message
news:UVGac.2456 $86a.964@news04 .bloor.is.net.c able.rogers.com ...[color=blue]
> Manohar,
>
> A, B, C... is the Column is the worksheets1(no column name), and 1, 2, 3[/color]
....[color=blue]
> is the row. Actually I want the values of more than one field, like A2,[/color]
B1,[color=blue]
> C2 and so on.
> Can you help? Thanks anyway.
>
> --
> Andrew
>
>
> "Manohar Kamath [MVP]" <mkamath@TAKETH ISOUTkamath.com > wrote in message
> news:%23d7ZYc2F EHA.700@TK2MSFT NGP09.phx.gbl.. .[color=green]
> > Andrew,
> >
> > Look into "Named Ranges" in Excel. Once you have created a named range,[/color]
> for[color=green]
> > 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=Micro soft.JET.OLEDB. 4.0;Extended[/color][/color]
Properties=""Ex cel[color=blue][color=green]
> > 8.0;HDR=YES;""; Data Source=C:\Myfil e.xls"
> >
> > Set conn = Server.CreateOb ject("adodb.con nection")
> > Conn.Open connString
> >
> > ' Considering C is the header name for third column
> > Set Rs = conn.Execute("s elect C from MyNamedRange")
> >
> >
> > Rs will now contain the data, the question is how to get the second row.[/color]
> You[color=green]
> > can either specifically get the second row with your query ("select C[/color][/color]
from[color=blue][color=green]
> > where A=2")
> >
> > Hope that helps.
> >
> > --
> > Manohar Kamath
> > Editor, .netWire
> > www.dotnetwire.com
> >
> >
> > "Andrew" <dunggaze@yahoo .com> wrote in message
> > news:u6Gac.2260 $86a.1247@news0 4.bloor.is.net. cable.rogers.co m...[color=darkred]
> > > Hi,
> > >
> > > How can I make a connection to an Excel file, and I can select the[/color]
> > specific[color=darkred]
> > > 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[/color][/color][/color]
and[color=blue][color=green]
> > the[color=darkred]
> > > select statement.
> > > Thanks for any help.
> > > --
> > > Andrew
> > >
> > >
> > >[/color]
> >
> >[/color]
>
>[/color]

[Bonj]

Re: Excel connection

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]

Re: Excel connection

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

[Manohar Kamath [MVP]]

Re: Excel connection

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

https://www.dotnetwire.com

"Bonj" <anonymous@disc ussions.microso ft.com> wrote in message
news:ACA5796F-9022-461F-ACF8-2DF8263E372D@mi crosoft.com...[color=blue]
> This is a VERY bad way of programming - names ranges are unreliable as[/color]
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.