|
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
Sub Test()
Dim Arr
Dim Conn As Object, Rst As Object
Dim strConn As String, SQL As String
Dim I As Integer, PathStr As String
Set Conn = CreateObject("ADODB.Connection")
Set Rst = CreateObject("ADODB.Recordset")
PathStr = ThisWorkbook.FullName
Select Case Application.Version * 1
Case Is < 12
strConn = "Provider=Microsoft.Jet.Oledb.4.0;Extended Properties=excel 8.0;Data source=" & PathStr
Case Is >= 12
strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties='Excel 12.0;HDR=yes;IMEX=1';Data Source=" & PathStr
End Select
Sheet1.Select
Conn.Open strConn
SQL = "SELECT 项目号,订单编号,图号,物料名称,规格型号,单位,采购承诺交期,日期,数量,单价,金额,未送货数量 FROM[Sheet1$]"
Set Rst = Conn.Execute(SQL)
Sheet2.Select
With Sheet2
For I = 0 To Rst.Fields.Count - 1
.Cells(1, I + 1) = Rst.Fields(I).Name
Next I
Range("A2").CopyFromRecordset Rst
End With
Cells.EntireColumn.AutoFit
Cells.EntireRow.AutoFit
Conn.Close
Set Conn = Nothing
End Sub |
|