Excel VBA用户定义的类型未定义 -

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?

我正在尝试创建一个excel程序，可以使用VBA在同一个文件中从sheet1到sheet2获取数据。但是当我宣布ADODB时，它不会出现在下拉列表中。当我尝试运行sub时，我得到'用户定义的类型未定义'错误。任何人都可以与我分享任何修复？

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

1 个解决方案

#1

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.

早期绑定（如代码所示）您需要做的是引用正确的Microsoft ActiveX数据对象。对于我的版本，它是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.

至于使用哪个，我只能提出建议。在开发过程中，使用Early Binding可以利用Intellisense。在部署时，更改为Late Binding以克服版本兼容性问题。

#1