|
Sub ttt1()
Dim isand As Boolean
Dim rs As Object, dic As Object, arr(), brr()
Set conn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
Set dic = CreateObject("Scripting.Dictionary")
isand = False
conn.Open "provider=microsoft.ace.oledb.12.0;extended properties=excel 12.0;data source=" & ThisWorkbook.FullName
Sql = "select 库存组织名称,供应商名称,sum(本币价税合计) from [采购入库明细查询$a:ac] where "
If Sheets("报表").Range("B2").Value <> "" Then
If isand Then
Sql = Sql + " and "
End If
isand = True
Sql = Sql + "仓库名称 = """ + Sheets("报表").Range("B2").Value + """"
End If
If Sheets("报表").Range("C2").Value <> "" Then
If isand Then
Sql = Sql + " and "
End If
isand = True
Sql = Sql + "一级分类名称 = """ + Sheets("报表").Range("C2").Value + """"
End If
If Sheets("报表").Range("D2").Value <> "" Then
If isand Then
Sql = Sql + " and "
End If
isand = True
Sql = Sql + "二级分类名称 = """ + Sheets("报表").Range("D2").Value + """"
End If
If Sheets("报表").Range("E2").Value <> "" Then
If isand Then
Sql = Sql + " and "
End If
isand = True
Sql = Sql + "三级分类名称 = """ + Sheets("报表").Range("E2").Value + """"
End If
If Sheets("报表").Range("F2").Value <> "" Then
If isand Then
Sql = Sql + " and "
End If
isand = True
Sql = Sql + "物料名称 = """ + Sheets("报表").Range("F2").Value + """"
End If
Sql = Sql + " group by 库存组织名称,供应商名称 "
Debug.Print Sql
rs.Open Sql, conn
Debug.Print rs.RecordCount
rs.MoveFirst
' For Each fld In rs.Fields
' Debug.Print fld.Name, '列标题
' Next
'
' Do Until rs.EOF
' For Each fld In rs.Fields
' Debug.Print fld.Value,
' Next
'
' rs.MoveNext
' Debug.Print
' Loop
'存入数组
arr = Application.Transpose(rs.getrows)
Dim m&, k&
ReDim brr(1 To 1000, 1 To 1000)
k = -1
For i = 1 To UBound(arr)
m = 0
If dic(arr(i, 1)) = "" Then
k = k + 2
dic(arr(i, 1)) = 1
brr(k, 1) = arr(i, 1)
brr(k + 1, 1) = "采购金额小计"
brr(k, 2) = arr(i, 2)
brr(k + 1, 2) = arr(i, 3)
Else
m = m + 1
brr(k, m + 2) = arr(i, 2)
brr(k + 1, m + 2) = arr(i, 3)
End If
Next i
Sheets("报表").Range("A5").Resize(1000, 1000) = brr()
conn.Close
Set rs = Nothing
Set conn = Nothing
End Sub
|
|