Excel VBA user defined type not defined-

Question

I'm trying to create an excel program that can get data from sheet1 to sheet2 within the same file using VBA. But when I declared the ADODB, it does not appear in the drop down list. And when I try to run sub I get the 'user defined type not defined' error. Can anyone please share with me any fixes?

The code is as below:

Sub testsql()

'declare variable
Dim objMyConn As ADODB.Connection
Dim objMyCmd As ADODB.Command
Dim objMyRecordSet As ADODB.Recordset

Set objMyConn = New ADODB.Connection
Set objMyCmd = New ADODB.Command
Set objMyRecordSet = New ADODB.Recordset

'open connection
objMyConn.connectionstring = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & wbWorkBook & ";Extended Properties=Excel 8.0;"
objMyConn.Open

'set and execute command
Set objMyCmd.activeconnection = objMyConn
objMyCmd.CommandText = "select top 10000 [Die No], Description from DieMaintenanceEntry"
objMyCmd.CommandType = adcmdtext


'open recordset
Set objMyRecordSet.Source = objMyCmd
objMyRecordSet.Open

'copy data to excel
ActiveWorkbook.Sheets("Display-Die Maintenance Summary").ActiveSheet.Range("A5").CopyFromRecordset (objMyRecordSet)

End Sub

Tools-> References -> Microsoft ActiveX Data object 2.0 library check the box click ok — Sivaprasath Vadivel
– Sivaprasath Vadivel, Commented May 30, 2017 at 5:44
If you're just copying data from one sheet to another, inside the same Excel session, and you're not applying sorting or filtering, then all of this ADO business is probably overkill. — ThunderFrame
– ThunderFrame, Commented May 30, 2017 at 6:08
@SivaprasathV You should use the 6.x version (Windows Vista and later). 2.8 is for use with Windows XP and 2.0-2.7 are unsupported. — ThunderFrame
– ThunderFrame, Commented May 30, 2017 at 6:11
Thanks @ThunderFrame, I was usually going with 2.8 library without knowing the reason and thought that was the latest library. Since the code didnt have any version reference i thought he can go with the lowest version and thought that the later versions had backward compatability. — Sivaprasath Vadivel
– Sivaprasath Vadivel, Commented May 30, 2017 at 6:14

L42 · Accepted Answer · 2017-05-30 06:00:14Z

5

You can solve this in two ways:

Early Binding (as hinted by your code)
What you need to do is reference the correct Microsoft ActiveX Data Object. For my version, it is 6.1.

Using Late Binding (No need to reference library)

Dim objMyConn As Object '/* Declare object type variable */
Dim objMyCmd As Object
Dim objMyRecordset As Object

'/* Set using create object */
Set objMyConn = CreateObject("ADODB.Connection")
Set objMyCmd = CreateObject("ADODB.Command")
Set objMyRecordset = CreateObject("ADODB.Recordset")

As for which to use, I can only give suggestion. During development, use Early Binding for you to take advantage of Intellisense. At deployment, change to Late Binding to overcome version compatibility issues.

answered May 30, 2017 at 6:00

L42

19.8k11 gold badges47 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

hjh93 Over a year ago

Thanks for the input. But now after changing the code I get a new error.

hjh93 Over a year ago

"objMyConn.Open" what needs to be changed?

L42 Over a year ago

@hjh93 That is already a new error and is out of scope on my answer :). Just kidding (but I'm serious), anyways, you need to check your connection string. Try replacing wbWorkbook with ThisWorkbook.FullName

Sivaprasath Vadivel Over a year ago

@L42, is the msjet4.0 dll available in windows 10 by default?

L42 Over a year ago

@SivaprasathV Is that related to this post or you're asking a different question. Btw, it should probably work (no way to check, I'm on Win7) but you should use ACE OLEDB instead since it superceded JET.

|

Collectives™ on Stack Overflow

Excel VBA user defined type not defined-

1 Answer 1

8 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related