在VBA中从SQL Server提取数据
|
您好，下面是我的代码，我无法从SQL服务器中获取数据，
其投掷错误为
Compiler error : object required.
Private Sub CommandButton1_Click()
Set SQLConn = CreateObject(\"ADODB.Connection\")
SQLConn.Open \"provider =sqloledb; Data Source = xxxx; Initial Catalog = jjjj; User Id = yyyy; Password = zzzz\"
MsgBox \"Connection Succesful\"
Set SQLData = CreateObject(\"ADODB.Recordset\")
With SQLData
\' Assign the Connection object.
.ActiveConnection = SQLConn
\' Extract the required records.
.Open \"select invoice_num, invoice_date, invoice_amount from im_invoice where billing_account = \'HS0076A\' and invoice_date =\'01-apr-2011\'\"
\' Copy the records into cell A1 on Sheet1.
Sheet1.Range(\"A1\").CopyFromRecordset SQLData
\' Tidy up
.Close
End With
SQLConn.Close
Set SQLData = Nothing
Set SQLConn = Nothing
End Sub
没有找到相关结果
已邀请:
2 个回复
捻盒愧杯
购藏盗码韦
Sub GetDataFromADO() \'Declare variables\' Set objMyconn = New ADODB.Connection Set objMyCmd = New ADODB.Command Set objMyRecordset = New ADODB.Recordset Dim rc As Long \'Open Connection\' objMyconn.ConnectionString = \"Provider=SQLOLEDB;Data Source=SAXAM\\SQLEXPRESS;Initial Catalog=AdventureWorks2012; Integrated Security=SSPI;\" objMyconn.Open \'Set and Excecute SQL Command\' Set objMyCmd.ActiveConnection = objMyconn objMyCmd.CommandText = \"select * from [Person].[BusinessEntity] \" objMyCmd.CommandType = adCmdText objMyCmd.Execute \'Open Recordset\' Set objMyRecordset.ActiveConnection = objMyconn objMyRecordset.Open objMyCmd \'Copy Data to Excel\' \'ActiveSheet.Range(\"A1\").CopyFromRecordset (objMyRecordset) Application.ActiveCell.CopyFromRecordset (objMyRecordset) rc = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row ActiveSheet.Cells(rc + 1, 1).Select \'Worksheets(\"Sheet1\").Cells(Rows.Count, 1).End(xlUp).Value objMyconn.Close End Sub