|
本帖最后由 opiona 于 2015-7-20 17:27 编辑
- '*********************************
- '******* 北极狐工作室出品 ******
- '******* QQ:14885553 ******
- '*********************************
- Sub Opiona()
- 'On Error Resume Next '// 发生错误,自动执行下一句,就是忽略错误
- Application.ScreenUpdating = False '//关闭屏幕刷新
- Application.DisplayAlerts = False '//关闭系统提示
- t = Timer '//开始时间
- Set SH2 = Sheets("Sheet1")
- SH2.Range("A4:Z1048576").ClearContents
- FileArr = FileAllArr(ThisWorkbook.Path, "*.xls?", ThisWorkbook.Name, False)
- For I = 0 To UBound(FileArr)
-
- Str_coon = "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties='Excel 12.0;HDR=no';Data Source =" & FileArr(I) '//OFFICE2007
- Set WB = Workbooks.Open(FileArr(I))
- For Each SH In WB.Sheets
- If SH.Cells(5, 2) = "Voucher No" Then
- LASTROW = SH.Range("D65536").End(3).Row
- StrSQL = "SELECT F1,F2,F3,F4,F5,F6,F7,F8"
- StrSQL = StrSQL & ",NULL,'" & GetPathFromFileName(FileArr(I)) & "' AS 工作薄名"
- StrSQL = StrSQL & ",'" & SH.Name & "' AS 工作表名"
- StrSQL = StrSQL & " FROM [" & SH.Name & "$B11:I" & LASTROW & "]"
-
- SQLARR = GET_SQL_To_Arr(StrSQL, Str_coon, False)
- IROW = SH2.Range("D65536").End(3).Row + 1
- If IROW <= 4 Then IROW = 4
- SH2.Range("A" & IROW).Resize(UBound(SQLARR, 1) + 1, UBound(SQLARR, 2) + 1) = SQLARR
- End If
- Next SH
- WB.Close False
- Next I
- Application.ScreenUpdating = True '//恢复屏幕刷新
- Application.DisplayAlerts = True '//恢复系统提示
- MsgBox "一共用时:" & Format(Timer - t, "#0.0000") & " 秒", , "北极狐提示!!" '//提示所用时间
- End Sub
复制代码 |
|