next »

[Madhu S]

Hi,
     In my requirement using the datasource as excel sheet ,iam using normal way to connect to the xls as datasource ,but its not working.

Set con=createobject("adodb.connection")

Set rs=createobject("adodb.recordset")

con.provider="microsoft.jet.oledb.4.0" ///here how can we get the provider

con.open"d:testdata.mdb"

rs.open "Giving the excel sheet path ",con

if con.state=1 the

msgbox "connection is opened"

else

Msgbox "Failed"

end if


Pls help me out .Always this concept confuse me alot while conencting database.

Regards
Madhu

[Madhu S]

Set a =CreateObject("ADODB.connection")
a.Provider ="Microsoft.Jet.OLEDB.4.0"
a.ConnectionString="Data Source=C:\Documents And Settings\Administrator\Desktop\Book4.xls;" & "Extended Properties=Excel 8.0;"
A.Open

This is to open the connection to the excel sheet .
After that i want to retrive the data fr tha excel.

Pls help me out

[Asiq Ahamed]

Hi Madhu,
       Try the below code,

Code

GeSHi (qtp):

Const adOpenForwardOnly=0

Const adOpenKeyset= 1

Const adOpenDynamic= 2

Const adOpenStatic= 3

mySheet="Sheet1"'Sheet Name

my1stCell="A1"'Range Start Position

myLastCell="A3"'Range End Position

myXlsFile="C:\Documents and Settings\Desktop\Book1.xls"'Path of your xl

Set objExcel = CreateObject( "ADODB.Connection" )

objExcel.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _

myXlsFile & ";Extended Properties=""Excel 8.0;" & _

strHeader & """"



Set objRS = CreateObject( "ADODB.Recordset" )

strRange = mySheet & "$" & my1stCell & ":" & myLastCell

objRS.Open "Select * from [" & strRange & "]", objExcel, adOpenStatic



Do Until objRS.EOF

Msgbox objRS.Fields.Item("MiddleInitial")'In my sheet, I have MiddleInitial column and I am retrieving values from it.

objRS.MoveNext

Loop



objRS.Close

objExcel.Close

Set objRS    = Nothing

Set objExcel = Nothing

 

Created by GeSHI 1.0.7.20

Let me know if you have any questions.

[Madhu S]

Asiq,always ur code  Rocks,its working v much fine.

Super Thanks .

Here i want ur valueable inputs ,its always confusing me wether we use provider or connection string.which one give accurate conenction,If we use Provider,hw can we get this provider and connectionstring for particular database wether its sql,oracle,r excel?

Pls give me the ways to get the 1.Connection string 2.Provider
for any databases.

[Asiq Ahamed]

Hi Madhu,
     The connection string contains the information that the provider need to know to be able to establish a connection to the database or the data file.

For example; the provider needs the address to the server (or the path to the data file) to connect to. This parameter is often named "Server" or "Data Source". The value specified for this key in the connection string is passed on to the provider and this is how its possible for the provider to know where to connect.

hw can we get this provider and connectionstring for particular database wether its sql,oracle,r excel?

Refer the below link, you will get the complete list of connection strings for various databases.

http://www.connectionstrings.com/

[Madhu S]

Provided URL is gives very valuable information.Now i am quite confident about this connection establishment.

Thanks Asiq.Now my query is resolved .Pls close the thread.

Regards

Madhu