|
可以试试通过ADO访问Excel,执行SQL语句来做:
' 创建ADO
Set conn= createobject("ADODB.Connection")
Set conn2= createobject("ADODB.Connection")
' 打开链接
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Test.xls;Extended
Properties=Excel 8.0"
conn2.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Test2.xls;Extended
Properties=Excel 8.0"
' 查询
sql= "SELECT item,address,value FROM [Sheet1$] Group by item,address,value"
Set RecSet = conn.Execute(sql)
RecSet.MoveFirst
While Not RecSet.EOF
'Msgbox RecSet.Fields("item").Value & " , " & RecSet.Fields.Item(1).Value
' 插入
sql_insert = "insert into [Sheet1$] Values('" & RecSet.Fields("item").Value & "','"&RecSet.Fields
("address").Value & "','" & RecSet.Fields("value").Value & "')"
conn2.Execute( sql_insert )
RecSet.MoveNext
Wend
' 关闭链接
conn.close
conn2.close
按这种方法需要:
1、先把Excel的第一行数据改成列名,例如:item、address、value
2、新建一个excel表,只包含表头不包含数据 |
|