在日常数据统计分析时可能需要将原来行的数据转换成列的形式,在access中可以使用insert into方式快速实现行转列的数据处理。
代码如下:
Dim rst As Object
Dim intI As Integer
'判断是否存在本地临时表tbl_Temp,有则先清空数据
If Nz(DLookup("Type", "MSysObjects", "Flags=0 AND Name='tbl_Temp'")) = 1 Then
CurrentProject.Connection.Execute ("delete from tbl_Temp")
Else
'创建临时表
CurrentProject.Connection.Execute ("create table tbl_Temp(科目编码 char(30),代码 char(30),数量 double)")
End If
Set rst = CreateObject("adodb.recordset")
rst.Open "select top 1 * from sheet1", CurrentProject.Connection, 1, 1
For intI = 1 To rst.Fields.Count - 1 '从第二列开始循环字段
strName = rst.Fields(intI).Name '获取字段名称
CurrentProject.Connection.Execute ("insert into tbl_Temp(科目编码,数量,代码) select 编码,[" & strName & "],'" & strName & "' from sheet1")
Next
rst.Close
Set rst = Nothing
Me.frmchild2.SourceObject = "table.tbl_Temp"
MsgBox "转换完成!", vbInformation, "提示"
附 件:
点击下载此附件
演 示: