在Excel-VBA中访问SQL数据库 [英] Accessing SQL Database in Excel-VBA

问题描述

我正在从MSDN复制一个VBA代码段，向我展示如何从SQL查询中获取excel表（Excel 2007）的结果：

  Sub GetDataFromADO（）
 
'声明变量'
设置objMyConn =新建ADODB.Connection 
设置objMyCmd =新建ADODB.Command 
设置objMyRecordset =新建ADODB.Recordset 
 
'打开连接'
 objMyConn.ConnectionString =Provider = SQLOLEDB; Data Source = localhost; User ID = abc; Password = abc 
 objMyConn.Open 
 
'Set and Excecute SQL Command'
 Set objMyCmd.ActiveConnection = objMyConn 
 objMyCmd.CommandText =select * from myTable
 objMyCmd.CommandType = adCmdText 
 objMyCmd.Execute 
 
'打开Recordset'
设置objMyRecordset.ActiveConnection = objMyConn 
 objMyRecordset.Open objMyCmd 
 
 'copy data to Excel'
 ActiveSheet.Range（A1）。CopyFromRecordset（objMyRecordset）
 
 End Sub 
  

我已经添加了Microsoft ActiveX Data Objects 2.1库作为参考。此数据库可以访问。

现在，当我运行这个子例程时，它有一个错误：

运行时间错误3704：对象关闭时不允许操作。

在声明中：

  ActiveSheet.Range（A1）。CopyFromRecordset（objMyRecordset）
  

任何为什么？

谢谢。

解决方案

初始目录到你的连接字符串。我也放弃了ADODB.Command语法，赞成简单地创建自己的SQL语句并打开该变量的记录集。

希望这有帮助。

  Sub GetDataFromADO（）
'声明变量'
设置objMyConn =新建ADODB.Connection 
设置objMyRecordset =新的ADODB.Recordset 
 Dim strSQL As String 
 
'打开连接'
 objMyConn.ConnectionString =Provider = SQLOLEDB;数据源= localhost;初始目录= MyDatabase;用户ID = ABC;密码= ABC; 
 objMyConn.Open 
 
'Set and Excecute SQL Command'
 strSQL =select * from myTable
 
'打开Recordset'
设置objMyRecordset.ActiveConnection = objMyConn 
 objMyRecordset.Open strSQL 
 
'将数据复制到Excel'
 ActiveSheet.Range（A1）。CopyFromRecordset（objMyRecordset）
 
 End Sub 
  

I am copying an VBA code snippet from MSDN that shows me how to grab results from a SQL query into excel sheet (Excel 2007):

Sub GetDataFromADO()

    'Declare variables'
        Set objMyConn = New ADODB.Connection
        Set objMyCmd = New ADODB.Command
        Set objMyRecordset = New ADODB.Recordset

    'Open Connection'
        objMyConn.ConnectionString = "Provider=SQLOLEDB;Data Source=localhost;User ID=abc;Password=abc;"
        objMyConn.Open

    'Set and Excecute SQL Command'
        Set objMyCmd.ActiveConnection = objMyConn
        objMyCmd.CommandText = "select * from myTable"
        objMyCmd.CommandType = adCmdText
        objMyCmd.Execute

    'Open Recordset'
        Set objMyRecordset.ActiveConnection = objMyConn
        objMyRecordset.Open objMyCmd

    'Copy Data to Excel'
        ActiveSheet.Range("A1").CopyFromRecordset (objMyRecordset)

End Sub

I have already added Microsoft ActiveX Data Objects 2.1 Library under as a reference. And this database is accessible.

Now, when I run this subroutine, it has an error:

Run-time error 3704: Operation is not allowed when object is closed.

On the statement:

ActiveSheet.Range("A1").CopyFromRecordset (objMyRecordset)

Any idea why?

Thanks.

解决方案

I've added the Initial Catalog to your connection string. I've also abandonded the ADODB.Command syntax in favor of simply creating my own SQL statement and open the recordset on that variable.

Hope this helps.

Sub GetDataFromADO()
    'Declare variables'
        Set objMyConn = New ADODB.Connection
        Set objMyRecordset = New ADODB.Recordset
        Dim strSQL As String

    'Open Connection'
        objMyConn.ConnectionString = "Provider=SQLOLEDB;Data Source=localhost;Initial Catalog=MyDatabase;User ID=abc;Password=abc;"
        objMyConn.Open

    'Set and Excecute SQL Command'
        strSQL = "select * from myTable"

    'Open Recordset'
        Set objMyRecordset.ActiveConnection = objMyConn
        objMyRecordset.Open strSQL            

    'Copy Data to Excel'
        ActiveSheet.Range("A1").CopyFromRecordset (objMyRecordset)

End Sub

这篇关于在Excel-VBA中访问SQL数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！