|
发表于 2013-4-13 19:54
|
显示全部楼层
本楼为最佳答案
D:K列数字以2位小数显示。
列宽度自动调整。- Sub adosub()
- Dim AdoConn As Object, AdoRst As Object
- Dim StrConn$, strSQL$, DataSource$
- Dim arr(), i As Byte
- DataSource = ThisWorkbook.FullName
- Set AdoConn = CreateObject("ADODB.Connection")
- Set AdoRst = CreateObject("ADODB.Recordset")
- strSQL = "select A.*,B.销售精装,B.销售简装,B.销售普通,B.销售收藏 from [购进$] as A,[销售$] as B where a.日期=b.日期 and a.年级=b.年级 and a.书名=b.书名"
- Select Case Application.Version
- Case Is = "14.0":
- StrConn = "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & _
- DataSource & ";Extended Properties=""Excel 12.0;HDR=yes;imex=0"";"""
- Case Is = "12.0"
- StrConn = "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & _
- DataSource & ";Extended Properties=""Excel 12.0;HDR=yes;imex=0"";"""
- Case Else
- StrConn = "Provider= Microsoft.Jet.OLEDB.4.0;" & _
- "Data Source=" & DataSource & "Extended Properties=""Excel 8.0;HDR=yes;imex=0"";"
- End Select
- On Error GoTo Errcheck
- AdoConn.Open StrConn
- Set AdoRst = AdoConn.Execute(strSQL)
- Application.ScreenUpdating = False
- Worksheets.Add
- Range("a2").CopyFromRecordset AdoRst
- With AdoRst.Fields
- ReDim arr(1 To .Count)
- For i = LBound(arr) To UBound(arr)
- arr(i) = .Item(i - 1).Name
- Next
- Range("a1").Resize(, .Count) = arr
- End With
- Columns("A:A").NumberFormatLocal = "yyyy/m/d"
- Columns("d:k").NumberFormatLocal = "0.00"
- AdoConn.Close
- ActiveSheet.UsedRange.EntireColumn.AutoFit
- Application.ScreenUpdating = True
- MsgBox "合并完成"
- Set AdoRst = Nothing
- Set AdoConn = Nothing
- Exit Sub
- Errcheck:
- MsgBox Err.Number & vbNewLine & Err.Description
- Exit Sub
- End Sub
复制代码 |
评分
-
查看全部评分
|