VBA 使用Excel数据库：行转列

最近工作中有需求，要从Excel表中提取一些统计信息，因为需求变动可能比较频繁，所以用VBA来做了。调试数据操作部分颇费周折，特记录一下。

1.数据源文件:[D:\4月份统计表.xlsx]：

2.最终的统计表格式：

3.计算规则：

要求：

A. 外观等级A，且WLD在451.5-458之间，且LOP在82-200之间判定为OK，其他为NG。

B.将NG的按照表面等级→WLD→LOP的优先顺序，判定是哪一项引起的不良，更新到数据源的 [筛选结果] 中。

C.在统计表中按照入库日期分组，统计总数量、OK数量、NG数量，以及NG产品中外观等级、WLD、LOP各占多大比例。

4.代码：

Option ExplicitPublic Sub DataInquire()    Dim strCnn As String, sqlStr As String, subSqlStr As String, strSql(1 To 4) As String '判断条件 1=OK,2=外观等级NG,3=WLDNG,4=LOPNG    Dim MainBook As Workbook    Dim Arr() As Variant, rngA As Range, rngB As Range    Dim cnn As Object, rs As Object    Dim i As Long, itemStr(1 To 4) As String '判定标志 1=OK,2=外观等级,3=WLD,4=LOP    Dim sourceFile As String        Set cnn = CreateObject("ADODB.Connection")    Set rs = CreateObject("ADODB.Recordset")    Set MainBook = ThisWorkbook    sourceFile = "D:\4月份统计表.xlsx"    strCnn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + sourceFile + "';Extended Properties='Excel 12.0;HDR=YES,IME=1'"            With ThisWorkbook.Worksheets(1)        strSql(1) = " And [外观等级] ='A' And [WLD] >=451.5 And [WLD] <=458 And LOP>=82 And LOP<200"        strSql(2) = " And [外观等级] <>'A'"        strSql(3) = " And ([WLD] <451.5 or [WLD] >458)"        strSql(4) = " And (LOP>=82 or LOP<200"        itemStr(1) = "OK"        itemStr(2) = "外观等级"        itemStr(3) = "WLD"        itemStr(4) = "LOP"        Set rngA = .Range("B" & .Cells(6000, "B").End(xlUp).Row + 1).Resize(1, 12) '//找到写数据的位置    End With        cnn.Open strCnn    If cnn.State = 1 Then        For i = 4 To 1 Step -1 '//更新筛选结果            sqlStr = "update [数据源$] set [筛选结果]= '" & itemStr(i) & "' where 1=1 " & strSql(i)            cnn.Execute sqlStr        Next i        '//////Excel/Access不支持case when then end结构，要用IIF来代替////'        subSqlStr = "(select [序号], case [筛选结果] when 'OK' then 1 end as [OK]," & _'                " case [筛选结果] when '外观等级' then 1 end as [外观等级]," & _'                " case [筛选结果] when 'WLD' then 1 end as [WLD]," & _'                " case [筛选结果] when 'LOP' then 1 end as [LOP]," & _'                " from [数据源$]) B "'       ///////////////////////////////////////////////////////////////////        subSqlStr = "(select [衬底编号], iif( [筛选结果] = 'OK',1,0 ) as [OK]," & _                " iif( [筛选结果] = '外观等级',1,0 ) as [外观等级]," & _                " iif( [筛选结果] = 'WLD',1,0 ) as [WLD]," & _                " iif( [筛选结果] = 'LOP',1,0 ) as [LOP]," & _                " from [数据源$]) B "                sqlStr = "select A.[入库日期],count(A.[入库日期]) as 总数量,sum(B.[OK]) as OK,(count(A.[入库日期])-sum(B.[OK])) as NG,sum(B.[外观等级])/(count(A.[入库日期])-sum(B.[OK]))," & _                " sum(B.[WLD])/(count(A.[入库日期])-sum(B.[OK])),sum(B.[LOP])/(count(A.[入库日期])-sum(B.[OK]))" & _                "from [数据源$] A, " & subSqlStr & " where A.[序号]=B.[序号] " & _                "group by A.[入库日期] order by A.[入库日期] asc"        Set rs = cnn.Execute(sqlStr)        If Not rs.EOF Then            Arr = WorksheetFunction.Transpose(rs.GetRows())            Set rngA = rngA.Resize(UBound(Arr, 1), 12)            rngA = Arr            rngA.Borders.LineStyle = xlContinuous            rngA.Resize(, 1).Offset(, 4).Resize(, 8).NumberFormatLocal = "0.00%"        End If        Set rs = Nothing    End If    cnn.CloseEnd Sub

说明：

A.要将[筛选结果]做细项分析，就要用到行转列。而Excel/Access中没有case when then end 结构，更没有pivot可以用，幸好有IIF可用。

B.Transpose函数使用有些限制，要求结果集不超过1024行[未验证]，且结果集不包含null[已验证]。

C.Excel作为数据库，查询速度明显比Access数据库以及SqlServer、Oracle速度慢很多。5万多行数据，要查询大约20秒了。

D.Excel表作为数据库使用，select和update可正常使用，但insert和delete不能用。使用select into 可以创建新的Excel工作表。