excel循环从其他工作簿复制数据

excel循环从其他工作簿复制数据

现在工作这里,他们查询单井数据都是在浏览器上,输井号和日期,从网络的数据库查询,返回的数据再输出到网页上。

网页上只是最简单的用表格显示数据,不管是看,还是算,都显得太苍白。之前习惯了在微软的 Excel 里操作,所以打算把2015年之内的数据全粘到一个 Excel 表格里。

文件内部的格式基本一致,文件名和存储路径都很规整,所以直接在固定区域来回的复制粘贴就行了。不会别的办法,用两层循环,实际使用,执行效率可以忍受。

于是有了下面这段丑的代码。

Sub test()
Application.ScreenUpdating = False
Application.ShowWindowsInTaskbar = False
yueZ = Array("一", "二", "三", "四", "五", "六", "七", "八", "九")
yueA = Array("1", "2", "3", "4", "5", "6", "7", "8", "9")
yueMax = Array(31, 28, 31, 30, 31, 30, 31, 31, 5)
myCount = 0
For i = 1 To 1
    For j = 1 To 14
    If j < 10 Then
    havezero = "0"
    Else
    havezero = Null
    End If
    fname = "E:\综合日报表\综合日报\2015年综合日报\" & yueZ(i - 1) & "月\采油五区生产综合日报" & yueA(i - 1) & "." & havezero & j & ".xls"
    Set wb = Workbooks.Open(fname, 0, ture)
    ThisWorkbook.Sheets(1).Range("a" & 6 + myCount * 117 & ":a" & 122 + myCount * 117) = "2015-" & yueA(i - 1) & "-" & j
    wb.Sheets(4).Range("a6:d122").Copy Destination:=ThisWorkbook.Sheets(1).Range("b" & 6 + myCount * 117 & ":e" & 122 + myCount * 117)
    wb.Sheets(4).Range("e6:an122").Copy Destination:=ThisWorkbook.Sheets(1).Range("g" & 6 + myCount * 117 & ":ao" & 122 + myCount * 117)
    wb.Close False
    myCount = 1 + myCount
    Next
Next
For i = 1 To 1
    For j = 15 To yueMax(i - 1)
    If j < 10 Then
    havezero = "0"
    Else
    havezero = Null
    End If
    fname = "E:\综合日报表\综合日报\2015年综合日报\" & yueZ(i - 1) & "月\采油五区生产综合日报" & yueA(i - 1) & "." & havezero & j & ".xls"
    Set wb = Workbooks.Open(fname, 0, ture)
    ThisWorkbook.Sheets(1).Range("a" & 6 + myCount * 117 & ":a" & 122 + myCount * 117) = "2015-" & yueA(i - 1) & "-" & j
    wb.Sheets(1).Range("a6:an122").Copy Destination:=ThisWorkbook.Sheets(1).Range("b" & 6 + myCount * 117 & ":ao" & 122 + myCount * 117)
    wb.Close False
    myCount = 1 + myCount
    Next
Next
For i = 2 To 9
    For j = 1 To yueMax(i - 1)
    If j < 10 Then
    havezero = "0"
    Else
    havezero = Null
    End If
    fname = "E:\综合日报表\综合日报\2015年综合日报\" & yueZ(i - 1) & "月\采油五区生产综合日报" & yueA(i - 1) & "." & havezero & j & ".xls"
    Set wb = Workbooks.Open(fname, 0, ture)
    ThisWorkbook.Sheets(1).Range("a" & 6 + myCount * 117 & ":a" & 122 + myCount * 117) = "2015-" & yueA(i - 1) & "-" & j
    wb.Sheets(1).Range("a6:an122").Copy Destination:=ThisWorkbook.Sheets(1).Range("b" & 6 + myCount * 117 & ":ao" & 122 + myCount * 117)
    wb.Close False
    myCount = 1 + myCount
    Next
Next
Application.ShowWindowsInTaskbar = True
Application.ScreenUpdating = True
End Sub
添加新评论