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

时间:2022-02-10 08:35:13

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


4  

You can solve this in two ways:

您可以通过两种方式解决此问题:

  1. 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。

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

  2. 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


4  

You can solve this in two ways:

您可以通过两种方式解决此问题:

  1. 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。

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

  2. 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以克服版本兼容性问题。