|
求救
横向为日期 纵向为型号怎么写出循环呢 Sub SCSJ()
'定义ADODB 连接Access数据库
Dim Cnn As New ADODB.Connection
Dim Rs As New ADODB.Recordset
Dim strCon As String
Dim strSQL As String
Dim strFileName As String '数据库文件名
Dim kisyu As String
Dim kada As String
strFileName = "D:\生产实绩.accdb"
' strFileName = "\\192.168.50.11\syteadd\mdb\KURUMONO.mdb"
strCon = "provider=Microsoft.ACE.OLEDB.12.0;;" _
& "Data Source=" & strFileName & ";"
Cnn.Open strCon '打开数据库链接
t_ym = InputBox("DATE:(201909)", "DATA", Format(Date, "yyyymm"))
t_date_from = DateSerial(CInt(Left(t_ym, 4)), CInt(Right(t_ym, 2)), 1)
t_date_to = DateAdd("m", 1, DateSerial(CInt(Left(t_ym, 4)), CInt(Right(t_ym, 2)), 1)) - 1
'清除数据
Range("D6:AH20").Select
Selection.ClearContents
Dim r As Integer '行
r = 6
Dim c As Integer '列
c = 4
Dim rowStep As Integer
rowStep = 15 '每个型占9行
kisyu = Cells(2, 2)
kada = Cells(r, 2)
' With Worksheets(t_sheetname)
'设置SQL查询语句
Sql = " SELECT T_组品出荷.seisandate, Mid([T_组品捆包].[item_name],3,5) AS kisyu, Right([T_组品捆包].[item_name],2) AS kada, T_组品出荷.syukka_di, T_组品出荷LOT.qty" & _
" FROM T_组品出荷 INNER JOIN (T_组品出荷LOT INNER JOIN T_组品捆包 ON T_组品出荷LOT.kanri_NO = T_组品捆包.kanri_NO) ON T_组品出荷.ID = T_组品出荷LOT.zpch_ID" & _
" WHERE (((T_组品出荷.seisandate)>=#" & t_date_from & "# And (T_组品出荷.seisandate)<=#" & t_date_to & "#) AND ((Mid([T_组品捆包].[item_name],3,5))='F7636') AND ((T_组品出荷.syukka_di)='组立'))"
Set Rs = New ADODB.Recordset
Rs.Open Sql, Cnn, adOpenKeyset, adLockOptimistic
Rs.AddNew
' Rs(4 + CInt(Format(CDate(seisandate), "dd")) - 1) = Cells(r + 1, c + CInt(Format(CDate(seisandate), "dd")) - 1)
index = 4
For tt = c To c + 30
'rs!year_month = "201908"
Rs(index) = IIf(Cells(r + 1, tt) = "", "0", Cells(r + 1, tt))
index = index + 1
Next
Rs.Close
' End With
MsgBox "successful!"
End Sub
|
|