如何用VBA对列重排

附件

列的重排.rar(1.86 KB)

2011-8-5 22:39, 下载次数: 9

复制内容到剪贴板

代码:

Sub ADO_method()
    Dim cnn, rst
    Dim sSQL As String
    Dim sConStr As String
    Dim i As Long
    Set cnn = CreateObject("adodb.connection")
    Set rst = CreateObject("adodb.recordset")
    cnn.connectionstring = "Provider=Microsoft.Jet.OLEDB.4.0;" _
     & "data source=" & ThisWorkbook.FullName & ";" _
                         & "Extended Properties='Excel 8.0;HDR=Yes;IMEX=1';"
    cnn.Open
    sSQL = "select 行政区划,有效证件号码,证件名称,姓名,金额 from [sheet1$]"
    Set rst = cnn.Execute(sSQL)
    For i = 0 To rst.fields.Count - 1
        Sheet2.Cells(1, i + 1) = rst.fields(i).Name
    Next i
    Sheet2.[a2].CopyFromRecordset rst
    Sheet2.[a1].CurrentRegion.EntireColumn.AutoFit
    rst.Close: cnn.Close
    Set rst = Nothing: Set cnn = Nothing   
End Sub