![cover](https://img-blog.csdnimg.cn/direct/8d71d83083994a2c831fb8fe93646163.png)
VBA初学: 引用从SQL数据库取数据的几个方法
方法三,由于方法二在EXCEL中执行会有问题,经查询资料,使用ListObjects的方法进行。但此方法在EXCEL中能执行,WPS中执行会报错(WPS中无ListObject对象)方法二,这段代码在WPS下能够执行,但在EXCEL下会报错。如果系统设置过ODBC,也可以将连接语句设置如下。然后在代码中要定义SQL语句,以便获取数据。在EXCEL中执行时,会提示。首先,要定义连接的数据集。有三个方
·
首先,要定义连接的数据集
Set objRec = CreateObject("ADODB.Recordset")
Set objConn = CreateObject("ADODB.Connection")
然后在代码中要定义SQL语句,以便获取数据
sqlstr = sqlstr + " select t1.FBillNo ,t_Item.fname type,t1.FNote,t2.FNumber,t2.FName, t2.FModel,t1.FQty, "
sqlstr = sqlstr + " convert(varchar,T1.FCommitDate,23) rwxdrq,convert(varchar,t1.fheadselfj01111,23) rkrq, "
sqlstr = sqlstr + "t4.FItemID,t4.FName,t3.Fmaketime from icmo t1 inner join t_icitem t2 on t1.fitemid=t2.FItemID "
sqlstr = sqlstr + " left join t_BOS257800028Entry2 t3 on t3.FID_SRC=t1.FInterID and t3.FBillNo_SRC1=t1.FBillNo "
sqlstr = sqlstr + " left join t_Item_3005 t4 on t3.FBase4=t4.FItemID "
sqlstr = sqlstr + " left join t_Item on t_item.fitemid=t1.FHeadSelfJ01100 and t_item.FItemClassID=3002 "
sqlstr = sqlstr + "where t1.fheadselfj01111 >=" & "'" & Sdate & "'" & " and t1.fheadselfj01111<=" & "'" & Edate & "'"
有三个方案用来获取数据
方法一
'连接数据库并执行SQL语句
objConn.ConnectionString = "Provider=SQLOLEDB;Data Source=192.168.100.3;Initial Catalog=AIS20150813141843;User ID=sa;Password=Chr_2016"
objConn.Open
Set objRec = objConn.Execute(sqlstr)
If Not objRec.EOF Then
'将结果集保存到工作表
Set WS = ThisWorkbook.Worksheets(sheetName) '
'将标题写入工作表
For i = 0 To objRec.Fields.count - 1
WS.Cells(1, i + 1).Value = objRec.Fields(i).Name
Next i
ActiveSheet.Range("A2").CopyFromRecordset objRec
''' 关闭记录集和连接
objRec.Close
objConn.Close
'' '释放对象
Set objRec = Nothing
Set objConn = Nothing
Else
MsgBox "没有数据,请重新选择时间段"
Exit Sub
End If
方法二,这段代码在WPS下能够执行,但在EXCEL下会报错
'' 执行查询并将结果存储在记录集对象中
'' '连接数据库并执行SQL语句
objConn.ConnectionString = "Provider=SQLOLEDB;Data Source=192.168.100.3;Initial Catalog=AIS20150813141843;User ID=sa;Password=Chr_2016"
objConn.Open
objRec.Open sqlstr, objConn
If Not objRec.EOF Then
'' 设置工作表对象
Set WS = ThisWorkbook.Sheets(sheetName) ' 可以更改为你要写入数据的工作表名称
'' 将数据写入工作表
With WS.QueryTables.Add(Connection:=objRec, Destination:=WS.Range("A1"))
.Refresh
End With
''' 关闭记录集和连接
objRec.Close
objConn.Close
'' '释放对象
Set objRec = Nothing
Set objConn = Nothing
Else
MsgBox "没有数据,请重新选择时间段"
Exit Sub
End If
在EXCEL中执行时,会提示
方法三,由于方法二在EXCEL中执行会有问题,经查询资料,使用ListObjects的方法进行。但此方法在EXCEL中能执行,WPS中执行会报错(WPS中无ListObject对象)
ActiveWorkbook.Queries.Add Name:="查询1", Formula:= _
"let" & Chr(13) & "" & Chr(10) & " 源 = Odbc.Query(""dsn=CHR"", """ & sqlstr & """)" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " 源" & ""
'' 设置工作表对象
Set WS = ThisWorkbook.Sheets(sheetName) ' 可以更改为你要写入数据的工作表名称
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=查询1;Extended Properties=""""" _
, Destination:=WS.Range("$A$1")).QueryTable
.CommandType = xlCmdSql
.CommandText = Array("SELECT * FROM [查询1]")
.RowNumbers = False
.Refresh BackgroundQuery:=True '后台进行查询,false时会跳出对话框
End With
ActiveWorkbook.Queries(1).Delete '删除查询
如果系统设置过ODBC,也可以将连接语句设置如下
dim connstring as string
connString = "DRIVER={ODBC Driver 17 for SQL Server};" & _
"SERVER=192.168.100.3;" & _
"DATABASE=AIS20150813141843;" & _
"UID=sa;" & _
"PWD=Chr_2016;"
更多推荐
所有评论(0)