VBA-读取文件的几种方法用时对比

不同方法的其他优劣可以参考其余文档,本文仅对比用时,并且由于文件以其他方式读取过会影响下次读取速度,本次结果仅供参考.

本人小白,欢迎指正.

方法	用时(s)	备注
workbook.open	0.14-0.39
execut.Excel	1.58
GetObeject	0.28,0.25,0.01,0.02	后台打开
Application	1.429

Sub WorkbooksOpenRoy()
'#方法1:workbookOpen
starttime = Timer
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    
    Dim rFilePath, rFileName, rSheetName, rRowMax
    rFilePath = "C:\Users\roy.luo\Base\"
    rFileName = "ProcessName.xlsx"
    rSheetName = "Sheet1"
    
    Workbooks.Open rFilePath & rFileName, UpdateLinks:=False, ReadOnly:=True, Password:="001"
    With Workbooks(rFileName).Sheets(rSheetName)
        rRowMax = .Cells(.Rows.Count, 2).End(xlUp).Row
        aRaw = .Range("A1:C" & rRowMax)
    End With
    Workbooks("" & rFileName).Close False
    
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
timecum = Timer - starttime
End Sub
Sub ExecuteRoy()
'#方法2:Execute
starttime = Timer
    bkpath = "C:\Users\roy.luo\Base\"
    bkname = "ProcessName.xlsx"
    sheetName = "Sheet1"
    cellname1 = "A1"
    cellname2 = "C140"
    trr = rAds(0, cellname1, cellname2)
    ReDim arr(1 To trr(0, 1), 1 To trr(0, 2))
    i = 1
    For R = trr(1, 1) To trr(2, 1)
        j = 1
        For C = trr(1, 2) To trr(2, 2)
            FullName = "'" & bkpath & "\[" & bkname & "]" & sheetName & "'!" & "R" & R & "C" & C
            arr(i, j) = ExecuteExcel4Macro(FullName)
            j = j + 1
        Next
     i = i + 1
    Next
TimeCsm = Timer - starttime
End Sub
Sub GetObjectRoy()
'#方法3:GetObject
starttime = Timer
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Dim Wb As Workbook
    Dim Temp As String
    rFilePath = "C:\Users\roy.luo\Base\"
    rFileName = "ProcessName.xlsx"
    rSheetName = "Sheet1"
    
    Set Wb = GetObject(rFilePath & rFileName)
        With Wb.Sheets(rSheetName)
          rRowMax = .Cells(.Rows.Count, 2).End(xlUp).Row
          aRaw = .Range("A1:C" & rRowMax)
        End With
    Set Wb = Nothing
     
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
timecum = Timer - starttime
End Sub
Sub ApplicationRoy()
'#方法4:Application
starttime = Timer
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Dim myApp As New Application
    Dim Sh As Worksheet
    Dim Temp As String
     rFilePath = "C:\Users\roy.luo\Base\"
    rFileName = "ProcessName.xlsx"
    rSheetName = "Sheet1"
    myApp.Visible = False
    Set Sh = myApp.Workbooks.Open(rFilePath & rFileName).Sheets(rSheetName)
    With Sh
        rRowMax = .Cells(.Rows.Count, 2).End(xlUp).Row
        aRaw = .Range("A1:C" & rRowMax)
    End With
    myApp.Quit
    Set Sh = Nothing
    Set myApp = Nothing
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
timecum = Timer - starttime
End Sub