Const rowCount = 2000
 Const 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