Import excel2003 book into vb6 datagrid using recordset

[Stripes1283]

Hi there I am having trouble getting the data from my excel file stored in my folder under c drive into the datagrid in vb6. I have the following code please help:

Dim oRS As New ADODB.Recordset
Dim cnn As New ADODB.Connection

cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source= C:\Werner\Ex1.xls;"
rs.Open "Select * from Ex1", cnn, adOpenStatic, adLockReadOnly, adCmdText
Set DataGrid1.DataSource = rs

 

[Andrzejek]

Dim oRS As New ADODB.Recordset
Dim cnn As New ADODB.Connection
    
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source= C:\Werner\Ex1.xls;"

[blue]Set oRS = New ADODB.Recordset
oRS.CursorType = adOpenForwardOnly
oRS.CursorLocation = adUseClient
oRS.LockType = adLockReadOnly[/blue]

oRS.Open "Select * from Ex1[blue].xls[/blue]", cnn, adOpenStatic, adLockReadOnly, adCmdText
[blue]oRS.MoveLast
Debug.Print oRS.recordCount
oRS.Close
Set oRS = Nothing[/blue]

What does your RecordCount say?


Have fun.

---- Andy

 

[Stripes1283]

How do I do that exactly and where do I put the code in. Cause I know it is something like msgbox(oRS.recordcount).

But i still keep on getting an error saying :
"Unrecognized database format 'C:\Werner\Ex1.xls

I dont know why it keeps giving me the path problem cause I know it is right.

Cheers

 

[Andrzejek]

I have this code:

Option Explicit

Private Sub Form_Load()
Dim xlConn As ADODB.Connection
Set xlConn = New ADODB.Connection
Dim xlRS As ADODB.Recordset
Set xlRS = New ADODB.Recordset

Dim ConStr As String 'The connection String
Dim SQL As String    'The SQL String that you want to manipulate Excel.

ConStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\temp\Excel\SomeFile.xls;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""

xlConn.ConnectionString = ConStr
xlConn.Open ConStr 'Open the Connection

SQL = "SELECT * FROM SomeFile"

[red]xlRS.Open SQL, xlConn, adOpenDynamic, adLockBatchOptimistic, adCmdText
[/red]
Do While Not xlRS.EOF
   'Iterate through the recordset
      
  xlRS.MoveNext
Loop
    
xlRS.Close
Set xlRS = Nothing
xlConn.Close
Set xlConn = Nothing
End Sub

But it errors on red line with:
error 2147217865 Could not find the object 'SomeFile'. Make sure the object exists and the you spell its name and the path names correctly.



Have fun.

---- Andy

 

[BobRodes]

Stripes, you're getting the error you're getting because you didn't set the extended properties in your connection string to Excel. Let's break this down:

[tt]Provider=Microsoft.Jet.OLEDB.4.0;
[/tt]
Means that you're using JET's OLE DB provider. OLE DB is a group of classes that Microsoft makes available to anyone, that they can use to provide data to a data consumer. ADO is an example of a data consumer; of course there are others. JET ("Joint Engine Technology") is one of many data providers. JET was created to allow a universal means of accessing ISAM ("Indexed Sequential Access Method") databases. These include Access, Paradox, dBase, Excel, and a number of others. Now, the default database that JET uses is Access (which is called "native JET" in a typical masterpiece of Microsoft marketspeak).

[tt]Data Source=C:\temp\Excel\SomeFile.xls;
[/tt]
This is the file or directory that contains the database files. In the case of Paradox or dBase, this will be a directory, since these two databases have each of the tables in the database in a separate file.

[tt]Extended Properties="Excel 8.0;HDR=Yes;IMEX=1"
[/tt]
You'll notice that I've removed some of the quotes from Andy's code. I've only left the ones that are actually in the string. Using a double double quote ("") in VB means to take the double quote as a literal double quote in the middle of a string, rather than as the signal that the string is beginning or ending (the "string delimiter"). For example:

Dim x as string
x = "I said ""Hello"" to my brother."  'or also
x = """Hello"", I said to my brother."

So, the extended properties are properties used by the OLE DB provider, in this case JET 4.0. They are a string enclosed in quotes, and separated by semicolons. In this case, the first property is the type of database that the file is, or Excel 8.0. (The HDR property means that the first row of data is a header, or the names of the columns. The IMEX property says whether to treat intermixed columns--columns that have some rows with numbers and some with text--as text or not.)

In your case, then, by not specifying an extended property to the JET OLE DB provider, you're telling JET that it's dealing with the default database type, which is Access. And it's telling you that no, it isn't.

Finally, see faq222-6008 for reasons not to use the "As New" construct. Andy's way is the better way.

HTH

Bob

 

[Stripes1283]

Hi there I am still having problems with this code it is telling me that I should check if Ex1 is valid name and path, and also that the Microsoft jet engine cannot find Ex1. Please help me.

Dim rs As New ADODB.Recordset
Dim con As New ADODB.Recordset

Private Sub CmdBrowse_Click()


Option Explicit

Private Sub Form_Load()
Dim xlConn As ADODB.Connection
Set xlConn = New ADODB.Connection
Dim xlRS As ADODB.Recordset
Set xlRS = New ADODB.Recordset

Dim ConStr As String 'The connection String
Dim SQL As String 'The SQL String that you want to manipulate Excel.

ConStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= C:\Werner\Ex1; Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""

xlConn.ConnectionString = ConStr
xlConn.Open ConStr 'Open the Connection

SQL = "SELECT * FROM Ex1"

xlRS.Open SQL, xlConn, adOpenDynamic, adLockBatchOptimistic, adCmdText

Do While Not xlRS.EOF
'Iterate through the recordset

xlRS.MoveNext
Loop

xlRS.Close
Set xlRS = Nothing
xlConn.Close
Set xlConn = Nothing
End Sub

 

[sipos]

do you already try to install jet4sp8 (service pack for jet engine 4) ? ...
download it from microsoft site (

http://www.microsoft.com/downloads/...-350e-4cd0-a12a-d7f70a153156&displaylang=en).

 

[Stripes1283]

I have sp6 installed on vb6, could you just try to explain to me why the program would give me an error that the path is wrong if I know it is correct. I checked the path, and the folder everything, please try and explain it to me.

Regards
Stripes

 

[Stripes1283]

Hey there I have downloaded this file: Jet 4.0 Service Pack 8 (SP8) for Windows XP (KB829558), it is the link you provided.

When I tried to install this exe it gave me an error saying: I have a newer version installed on the system, what else can I do?

Regards

 

[BobRodes]

<I checked the path, and the folder everything, please try and explain it to me.

Check it again. It's not the same as the original. The reason that it would give you the error that the path is wrong when you KNOW it is correct is because people know things that aren't true. Especially when they start getting frustrated.

HTH

Bob

p. s. looks like the .xls is missing in the second pathname.

 

[RonRepp]

Shouldn't it also be:

Select * FROM Sheet1$

??

Just a thought.

Ron Repp

If gray hair is a sign of wisdom, then I'm a genius.

 

[BobRodes]

Yup, good catch Ron. So stripes, quit doing heroic surgery on your application and fix the little dumb stuff, and it will probably start to work.

Bob