我在excel中放置了一个文本框和按钮,目的是在文本框中输入后,点击按钮能把sql server数据库某个表中的数据提取出来放到excel中,语句如下:
Private Sub CommandButton1_Click()
Dim qt As QueryTable
sqlstring = "select ITEM,ITEM_DESC,UM from ITEMLIST Where ITEM='" & TextBox1.Text & "' order by ITEM "
connstring = "ODBC;DSN=DBMR;UID=sa;PWD=1234;Database=DatabaseMR"
Set qt = Workbooks(1).Worksheets(1).QueryTables(1)
With qt
.Connection = connstring
.Destination = Range("A1")
.CommandType = xlCmdSql
.CommandText = sqlstring
.Refresh BackgroundQuery:=False
End With
End Sub可是执行时抱错,说下面这个语句有错误:
Set qt = Workbooks(1).Worksheets(1).QueryTables(1)
运行时错误'9',下标越界。请问如何解决???
Private Sub CommandButton1_Click()
Dim qt As QueryTable
sqlstring = "select ITEM,ITEM_DESC,UM from ITEMLIST Where ITEM='" & TextBox1.Text & "' order by ITEM "
connstring = "ODBC;DSN=DBMR;UID=sa;PWD=1234;Database=DatabaseMR"
Set qt = Workbooks(1).Worksheets(1).QueryTables(1)
With qt
.Connection = connstring
.Destination = Range("A1")
.CommandType = xlCmdSql
.CommandText = sqlstring
.Refresh BackgroundQuery:=False
End With
End Sub可是执行时抱错,说下面这个语句有错误:
Set qt = Workbooks(1).Worksheets(1).QueryTables(1)
运行时错误'9',下标越界。请问如何解决???
Dim qt As QueryTable
sqlstring = "select ITEM,ITEM_DESC,UM from ITEMLIST Where ITEM='" & TextBox1.Text & "' order by ITEM "
connstring = "ODBC;DSN=DBMR;UID=sa;PWD=1234;Database=DatabaseMR"
Set qt = Workbooks(1).Worksheets(1).QueryTables.Add(connstring, Range("A1"), sqlstring)
qt.Refresh BackgroundQuery:=False
End With
End Sub
非常感谢,可是还有一个问题:
第一次查询后没问题,可是在文本框里输入第二个物料号再查询的话,查询结果直接在第一次查询结果的后面追加,我不想让它追加,而是想先把第一次查询结果清空,再从A1显示,请问怎么做?谢谢。
例如你显示的是三列,可以在最前面的代码先删除前三列,再进行查询,代码如下Private Sub CommandButton1_Click()
Columns("A:C").Delete'删除前三列,即A到C列
Dim qt As QueryTable
sqlstring = "select ITEM,ITEM_DESC,UM from ITEMLIST Where ITEM='" & TextBox1.Text & "' order by ITEM "
connstring = "ODBC;DSN=DBMR;UID=sa;PWD=1234;Database=DatabaseMR"
Set qt = Workbooks(1).Worksheets(1).QueryTables.Add(connstring, Range("A1"), sqlstring)
qt.Refresh BackgroundQuery:=False
End Sub
请问这种在Excel里编程的叫什么?VBA?