Excel VBA user defined type is not defined -
I am trying to create an excel program that can receive data from sheet1 to sheet2 in the same file using VBA. But when I declared ADODB it doesn't appear in the dropdown. And when I try to run sub, I get a "user defined type not defined" error. Anyone can share any fixes with me?
The code looks like this:
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
source to share
You can solve this in two ways:
-
Early binding (as outlined by your code)
What you need to do is specify the correct oneMicrosoft ActiveX Data Object
. For my version, this is6.1
.
-
Using Late Binding (No need to link to the 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 the use, I can only give a suggestion. During development use Early Binding
to use Intellisense
. When deploying, change to Late Binding
to resolve compatibility issues.
source to share