|
楼主 |
发表于 2013-7-9 13:49
|
显示全部楼层
本帖最后由 feifangsiyue 于 2013-7-9 15:04 编辑
一指禅62 发表于 2013-7-8 21:54
你看这样写行不?
帮我看看返回的记录数怎么老是-1
--------------------------------------------最后补充,问题解决了--------------------------------------
rs.CursorLocation = 3 道理不是很懂,不过加上这一句问题暂时解决了。
- Sub GetGDD()
- Dim i%, strCn$, serIP$, uid$, pwd$, dbName$, strSQL$
- Dim sht As Worksheet
- Dim cn As New ADODB.Connection
- Dim rs As New ADODB.Recordset
- Dim stime As Date, etime As Date
- stime = Timer
- serIP = "192.168.1.1"
- uid = "sa"
- pwd = "123"
- dbName = "AAA"
- strCn = "Provider=sqloledb;Server=" & serIP & ";Database=" & dbName & ";Uid=" & uid & ";Pwd=" & pwd & "; "
- '-------------------------增加了期间输入步骤------------------------------------------------------------------
- TX = 201304
- If TX = "" Or Len(TX) <> 6 Then
- MsgBox "期间输入错误,请检查!"
- Else
- cn.Open strCn
- strSQL = " DECLARE @YM char(6) set @YM='201204'"
- strSQL = strSQL & " SELECT Rtrim(TA001+'-'+TA002),Rtrim(TA006),TA035,TA015,TA040,"
- strSQL = strSQL & " TA011,left(TA006,8) ,TA034,TA022 ,TA032 ,TA015 ,TA016,TA017 ,TA018 ,TA009 ,TA010,TA014 ,CREATE_DATE"
- strSQL = strSQL & " FROM MOCTA Where TA013='Y' and TA001<>'52E1' and left(TA040,6) <=@YM Order by TA001 asc,TA002 asc"
- Set sht = ThisWorkbook.Sheets("C-100")
- sht.[A11:E2000].ClearContents
- sht.[AA11:AT2000].ClearContents
- rs.Open strSQL, cn, 1, 1
- MsgBox rs.RecordCount '返回的记录数怎么老是-1
- For i = 1 To 100 ' rs.RecordCount
- sht.Range("A" & i + 10).Value = rs.Fields(0).Value
- sht.Range("B" & i + 10).Value = rs.Fields(1).Value
- sht.Range("C" & i + 10).Value = rs.Fields(2).Value
- sht.Range("D" & i + 10).Value = rs.Fields(3).Value
- sht.Range("E" & i + 10).Value = rs.Fields(4).Value
-
- sht.Range("AG" & i + 10).Value = rs.Fields(5).Value
- sht.Range("AH" & i + 10).Value = rs.Fields(6).Value
- sht.Range("AI" & i + 10).Value = rs.Fields(7).Value
- sht.Range("AJ" & i + 10).Value = rs.Fields(8).Value
- sht.Range("AK" & i + 10).Value = rs.Fields(9).Value
- sht.Range("AL" & i + 10).Value = rs.Fields(10).Value
- sht.Range("AM" & i + 10).Value = rs.Fields(11).Value
- sht.Range("AN" & i + 10).Value = rs.Fields(12).Value
- rs.MoveNext
- Next i
- rs.Close
- cn.Close
- Set rs = Nothing
- Set cn = Nothing
- etime = Timer
- MsgBox "提取工单信息完成" & Format(etime - stime, "0.00") & "秒,更新完毕!"
- End If
- End Sub
复制代码 |
|