|
楼主 |
发表于 2019-9-6 14:45
|
显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
本帖最后由 kulagen 于 2019-9-6 15:25 编辑
谢谢 能取;但是我取的是1-12月的数据;我自己按你复制黏贴 ;能不能优化???;好像也不对
Sub 取数P3()
Dim wd As Workbook, ws As Worksheet
Dim cnn, aa, ab, ac, ad, ae, af, ag, ah, ai, aj, ak, al$
Set cnn = CreateObject("adodb.connection")
Set rs = CreateObject("adodb.recordset")
Set ws = ActiveSheet
Set wd = ThisWorkbook
'------------------------
mydir = Dir(ThisWorkbook.Path & "\数据源\" & "*.xlsx")
Do While mydir <> ""
If mydir = "表1.xlsx" Then
cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;Data Source=" & ThisWorkbook.Path & "\数据源\" & mydir
aa = "select * from[1月$g15:g250]"
Set rs = cnn.Execute(aa)
ws.Range("ac6").CopyFromRecordset rs
ab = "select * from[2月$g15:g250]"
Set rs = cnn.Execute(ab)
ws.Range("ad6").CopyFromRecordset rs
ac = "select * from[3月$g15:g250]"
Set rs = cnn.Execute(ac)
ws.Range("ae6").CopyFromRecordset rs
ad = "select * from[4月$g15:g250]"
Set rs = cnn.Execute(ad)
ws.Range("af6").CopyFromRecordset rs
ae = "select * from[5月$g15:g250]"
Set rs = cnn.Execute(ae)
ws.Range("ag6").CopyFromRecordset rs
af = "select * from[6月$g15:g250]"
Set rs = cnn.Execute(af)
ws.Range("ah6").CopyFromRecordset rs
ag = "select * from[7月$g15:g250]"
Set rs = cnn.Execute(ag)
ws.Range("ai6").CopyFromRecordset rs
ah = "select * from[8月$g15:g250]"
Set rs = cnn.Execute(ah)
ws.Range("ad6").CopyFromRecordset rs
ai = "select * from[9月$g15:g250]"
Set rs = cnn.Execute(ai)
ws.Range("aj6").CopyFromRecordset rs
aj = "select * from[10月$g15:g250]"
Set rs = cnn.Execute(aj)
ws.Range("ak6").CopyFromRecordset rs
ak = "select * from[11月$g15:g250]"
Set rs = cnn.Execute(ak)
ws.Range("al6").CopyFromRecordset rs
al = "select * from[12月$g15:g250]"
Set rs = cnn.Execute(al)
ws.Range("am6").CopyFromRecordset rs
End If
mydir = Dir
Loop
End Sub |
|