Excell Connection Problem 1

[mpadgett]

I'm using the following to connect to an Excel spreadsheet from VB6.

Dim xlConn As Object

Set xlConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=k:\clad\form 102\31 plate\237C1003-31.xls;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""

When I RUN the code I get a TYPE MISMATCH error.

Thanks for any help!

-Mike P.

 

[RonRepp]

You are using an ADO connection. You should set a reference to ADO (Microsoft ActiveX Data Objects) and do it like this:

Dim xlConn As ADODB.Connection
Set xlConn as New ADODB Connection
Dim xlRS as ADODB.Recordset
Set xlRS as 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=k:\clad\form 102\31 plate\237C1003-31.xls;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""

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

SQL="SELECT * FROM 237C1003-31.xls"

RS.Open SQL, xlConn, adOpenDynamic, adLockBatchOptimistic, adCmdText
    Do While Not xlRS.EOF
       'Iterate through the recordset 
          
      xlRS.MoveNext
    Loop
xlRS.Close
Set xlRS = Nothing
xlCon.Close
Set xlCon = Nothing

That code was meant to rifle through a database. Inserting, deleting and updating have a slightly different syntax.

I hope this helps.



Ron Repp

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

 

[mpadgett]

I'm probably going about it the wrong way then. Essentially all I want to do is have the values from 2 cells be displayed in textboxes when the user clicks on a spreadsheet filename in a filelistbox.

Thanks,

-Mike P.

 

[BobRodes]

The reason that you're getting a type mismatch is because you're attempting to set an object variable as a string, when it needs to be a reference to an object. So, yes, you're going about it the wrong way.

If you're going to use the connection string in your code, you need to use ADO as Ron shows you.

HTH

Bob

 

[RonRepp]

Use the XL object library. Like this:

[code--Paste all in Form ]

''There are four buttons on this form and 2 textboxes
''tBox1 and tBox2
''btnGetValues, btnSetValues,btnXL,btnQuitXL

Private objXL As Object 'Declare the variable

Private Sub btnGetValues_Click()
tBox1.Text = objXL.Range("A1").Value 'Get the value of the 1st assigned cell
tBox2.Text = objXL.Range("A2").Value 'Get the value of the 2nd assigned cell
End Sub

Private Sub btnQuitXL_Click()
QuitXL 'Call the quit routine
End Sub

Private Sub btnSetValues_Click()
objXL.Range("A1").Value = tBox1.Text 'Set the value of the 1st assigned cell
objXL.Range("A2").Value = tBox2.Text 'Set the value of the 2nd assigned cell
End Sub

Private Sub btnXL_Click()
''you may want to add the Topmost API, or you'll have to
''click on the minimized form to have it above XL
Set objXL = CreateObject("Excel.Application") 'set the object for XL
objXL.Visible = True 'Open XL
objXL.Workbooks.Add 'Add WB
End Sub

Private Sub Form_Load()
Show
End Sub

Private Sub QuitXL()
objXL.Quit 'Close down XL
Set objXL = Nothing 'Clear the variable
End Sub

[/code]

This is an example of using an object through VB6. If you were to do it in VBA (in Excel), I would simply use the Application object of Excel.

I hope this helps.




Ron Repp

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

 

[BobRodes]

I didn't mention that because it requires that both the developer and the production entities both be registered owners of Microsoft Excel. (I had to look it up to be sure: see

http://support.microsoft.com/kb/q140629/)

If the OP meets these qualifications, this is indeed another way to go.

Bob

 

[mpadgett]

I pulled-it-off using this code.

Dim file_name As String
Dim oXLApp As Excel.Application

file_name = "k:\clad\form 102\" & List1.Text & "\" & File1.FileName
Text1.Text = ""
Text2.Text = ""

Set oXLApp = New Excel.Application
Set oxlbook = oXLApp.Workbooks.Open(file_name)
Set Worksheet = oXLApp.Worksheets(1)
Text1.Text = Worksheet.Range("E7")
Text2.Text = Worksheet.Range("E9")
oXLApp.Quit

-Mike P.

 

[BobRodes]

Ok, in this case you're using Excel as a background application, since you don't set the visible property to true. Good job!

By the way, Ron, Mike's solution uses early binding, which is generally to be preferred over late binding (which you're using) unless there is a compelling reason not to use it (for example, when you don't know what type of object you're binding to until you get to runtime).

Bob

 

[RonRepp]

Bob:

I'd heard the opposite, which is why I used late binding. I prefer early, because then I get the intellisense (bascically, I'm lazy). If I'm using it through my company, I always use early, because we all have the same version of Excel. If it's code that a vendor will use from us, I use late binding, because I don't know what version they have.

Either case, problem solved. Good job on helping him.

Thanks,



Ron Repp

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

 

[BobRodes]

There are other strong reasons to use early binding, Ron, besides intellisense. In particular, late binding requires significant extra overhead, and tends to create errors that are more difficult to handle.

When you late bind, you also have to use the iDispatch interface, which you don't have to do with early binding. While VB always implements a "dual interface" (i. e. an interface that uses both iUnknown and iDispatch--basically) for COM classes, that simply means that you implement both interfaces, not that you actually use both of them. You only use iDispatch with late binding.

The use of iDispatch to resolve object pointers can significantly increase overhead. In particular, setting properties and short method calls will be impacted. So, writing code to late bound objects should take this into account. Finally, I would recommend that one always use early binding unless one can make a case not to, rather than always using late binding unless one can make a case not to, as you appear to have done here.

Interesting related material:

http://msdn2.microsoft.com/en-us/library/2c0y4cce(VS.80).aspx

http://vb.mvps.org/hardcore/html/dualinterfacesidispatch.htm

http://www.windowsdevcenter.com/pub/a/oreilly/windows/news/mojica_1000.html

http://www.microsoft.com/msj/0100/versioning/versioning.aspx

http://www.bridgeport.edu/sed/fcourses/cs545/Lectures/cs545_IDispatch.doc

HTH

Bob

 

[RonRepp]

Bob:

Thanks for that explanation. I'll read the articles and let you know what I think.

Thanks,



Ron Repp

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

 

[BobRodes]

You bet Ron. I'll let you in on a secret: I didn't read those articles, at least not every word. But I skimmed them pretty good.

 

[RonRepp]

Thanks for the smile, tool. I needed it.

Ron Repp

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