SQL插入到EXCEL问题
Const rowCount = 2000Const colCount = 15
Const fstRowIndex = 5
Const fstColIndex = 2
Const batchColIndex = 6
Const stepctColIndex = 3
Const capacityColIndex = 5
Const addInitRowIndex = 3
Const keyColIndex = 4
' Dim myArray(5 To 40) As Integer
Private Sub CommandButton1_Click()
Dim xls As Worksheet
Set xls = Sheets("PC")
Dim colIndex As Integer
' Dim Dat As Date
' Dat = Date
' For colIndex = fstColIndex To 90
' xls.Cells(1, colIndex) = Format(Dat, "mm-dd")
' xls.Cells(4, colIndex) = "WIP"
' Dat = DateAdd("d", 1, Dat)
' Next
xls.Range(xls.Cells(fstRowIndex, fstColIndex), xls.Cells(fstRowIndex + rowCount, fstColIndex + colCount)).Clear
'xls.Cells(1, colIndex) = Format(Dat, "mm-dd")
'Erase myArray
Dim dbconn As New ADODB.Connection
Dim dbrs As New ADODB.Recordset
Dim strcon As String
Dim sql As String
Dim strTime As String
' Dim lot_id As String
' lot_id = xls.Cells(3, 4)
' part_id = xls.Cells(3, 6)
strTime = Format(xls.Cells(3, 4), "yyyy-mm-dd")
strTime = strTime & " 7:30"
'strcon = "DSN=mesrpt;UID=ods;PWD=ods"
strcon = "Driver={SQL Server};Server=10.8.2.31;Database=AIS20080906180500;Uid=k3au;password=2432367"
dbconn.Open strcon
sql = "SELECT top 1 E.Part,E.Fspc,E.ItemCode,F.POQty " & _
"FROM (SELECT A.FItemName Part,A.Fspc,B.FItemID,A.FChildItemCode ItemCode,sum(C.Fqty) AS FQty FROM usb_bom A, t_Item B,ICInventory C WHERE A.FChildItemCode=B.FNumber AND B.FItemID*=C.FItemID AND B.FItemClassID=4 GROUP BY A.FItemName,A.Fspc,B.FItemID,A.FChildItemCode)E,(SELECT FItemID,SUM(FQty-FStockQty) AS POQty FROM POOrderEntry WHERE Fqty-FStockqty>0 And FMRPClosed!=1 GROUP BY FItemID)F" & _
"WHERE E.FItemID*=F.FItemID" (这就是我插入的SQL 我想连接这条SQL语句 但是显示错误 请那个师傅指点下 那里错了)
'Set dbrs = dbconn.Execute(sql)
dbrs.CursorLocation = adUseClient
dbrs.Open sql, dbconn, adOpenStatic, adLockReadOnly
Dim i, j, k As Integer
j = 5
For i = 0 To dbrs.RecordCount - 1
' For j = fstRowIndex To dbrs.RecordCount - 1
'If (xls.Cells(j, 1) = dbrs(0)) Then
xls.Cells(j, 2) = dbrs(0)
'myArray(j) = dbrs(1)
'Exit For
' End If
j = j + 1
' Next
dbrs.MoveNext
Next
'xls.Cells(5, 6) = Format(xls.Cells(5, 7), "yyyy-mm-dd h:m:s")
'For k = 6 To dbrs.RecordCount + 4
'xls.Cells(k, 6) = Format(xls.Cells(k - 1, 6) + xls.Cells(k, 5) / 24, "yyyy-mm-dd h:m:s")
'Next
End Sub
Function clearRang(xls As Worksheet)
xls.Cells(fstRowIndex, fstColIndex) = ""
xls.Range(xls.Cells(fstRowIndex, fstColIndex + 1), xls.Cells(fstRowIndex + rowCount, fstColIndex + colCount)).Clear
End Function
Function AccountQty(ByVal RowIndex As Integer, ByVal initColIndex As Integer, ByVal qty As Integer, ByVal count As Integer, ByVal flag As Integer)
Dim xls As Worksheet
Set xls = Sheets("PC")
Dim disPath As Integer
If flag = 0 Then
disPath = formatKey(CDbl(xls.Cells(RowIndex, keyColIndex)) + count * CDbl(xls.Cells(RowIndex, stepctColIndex)) / 24)
xls.Cells(fstRowIndex, initColIndex + disPath) = Int(xls.Cells(fstRowIndex, initColIndex + disPath)) + qty
Else
disPath = formatKey(CDbl(xls.Cells(RowIndex, keyColIndex)) + count * CDbl(xls.Cells(RowIndex, stepctColIndex)) / 24)
disPath = disPath - formatKey(CDbl(xls.Cells(RowIndex - 1, keyColIndex)))
xls.Cells(RowIndex, initColIndex + disPath) = Int(xls.Cells(RowIndex, initColIndex + disPath)) + qty
End If
End Function
Function formatKey(a As Double) As Integer
Dim result As Integer
If Int(a) < CDbl(a) Then
result = Int(a)
Else
result = Int(a) - 1
End If
formatKey = result
End Function