Fastest Code to Open a workbook

[NatetheGreat]

Hi there,

I am experimenting with opening files in excel.

Currently I am aware of and testing two approaches. the first is as follows via a dbconnection to the file without actually opening it.

Sub GetDataFromClosedWorkbook(SourceFile As String, SourceRange As String, _
    TargetRange As Range, IncludeFieldNames As Boolean)
    
   
    Dim dbConnection As Object, rs As Object
    
    Set dbConnection = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")
    
    
    'Dim dbConnection As ADODB.Connection, rs As ADODB.Recordset
    Dim dbConnectionString As String
    Dim TargetCell As Range, I As Long
    

        dbConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
          "Mode=1;Data Source=" & SourceFile & ";Extended Properties=Excel 12.0;"
 
        'Set dbConnection = New ADODB.Connection

        dbConnection.Open dbConnectionString

Wrapping a timer around the dbconnection.open dbconnection string line, gave me a time of 7 seconds.

Alternatively is the following second approach, whereby I Use workbooks.open method

  For Each myFile In fso.GetFolder(myDir).Files 
            Workbooks.Open (myDir & "\" & myFile.Name)
            myCounter = myCounter + 1
            ReDim Preserve myList(1 To myCounter)
            myList(myCounter) = myDir & "\" & myFile.Name

Suprisingly, this approach only took 4 seconds to open the same .xlsx file of 10.9MB. 3 seconds less than the database connection method.

Is anyone aware of a faster way to gain access to a files data? I am extracting from hundreds of files so every second counts!!

Thanks
Nate

 

[Arithos]

Hi there,

I am experimenting with opening files in excel.

Currently I am aware of and testing two approaches. the first is as follows via a dbconnection to the file without actually opening it.

Sub GetDataFromClosedWorkbook(SourceFile As String, SourceRange As String, _
    TargetRange As Range, IncludeFieldNames As Boolean)
    
   
    Dim dbConnection As Object, rs As Object
    
    Set dbConnection = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")
    
    
    'Dim dbConnection As ADODB.Connection, rs As ADODB.Recordset
    Dim dbConnectionString As String
    Dim TargetCell As Range, I As Long
    

        dbConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
          "Mode=1;Data Source=" & SourceFile & ";Extended Properties=Excel 12.0;"
 
        'Set dbConnection = New ADODB.Connection

        dbConnection.Open dbConnectionString

Wrapping a timer around the dbconnection.open dbconnection string line, gave me a time of 7 seconds.

Alternatively is the following second approach, whereby I Use workbooks.open method

  For Each myFile In fso.GetFolder(myDir).Files 
            Workbooks.Open (myDir & "\" & myFile.Name)
            myCounter = myCounter + 1
            ReDim Preserve myList(1 To myCounter)
            myList(myCounter) = myDir & "\" & myFile.Name

Suprisingly, this approach only took 4 seconds to open the same .xlsx file of 10.9MB. 3 seconds less than the database connection method.

Is anyone aware of a faster way to gain access to a files data? I am extracting from hundreds of files so every second counts!!

Thanks
Nate

This is an interresting subject, when I loop through excel workbooks I like to "hide" the application (when I know the macro works).

Like so

fName = Dir(fPath & "*.xlsx")
SearchName = Range("B2").Value
    Portfolio = Range("A3").Value
    [COLOR=#ff0000]Excel.Application.Visible = False[/COLOR]
    Do
        fName = Dir
        If fName = "" Then
Errorhandling:
        MsgBox "Error/Nothing found, Will exit macro"
        [COLOR=#ff0000]Excel.Application.Visible = True[/COLOR]
        Exit Sub

        End If
        
            Set wbk = Workbooks.Open(fPath & fName)
            On Error Resume Next
            Findings = Sheets(1).Range("L:L").Find(SearchName)
            On Error GoTo 0
            If Findings = "" Then
            ActiveWorkbook.Close
            GoTo Nextloop
            Else
            [COLOR=#ff0000]Excel.Application.Visible = True[/COLOR]
            Exit Sub
            End If
Nextloop:
    Loop While fName <> ""

And I have always found that the Dir approach is faster. Approvements on my code are also welcome =)

 

[NatetheGreat]

Hi thanks for the tip, but setting application.visible= false made almost no effect on my code. For 40 files...

Workbooks.open method: 216 seconds
dbconnection.open method: 217 seconds

I cannot simply use the DIR function as I am drilling up and down across various folders, but I believe that is irrelevant anyway as it is the workbooks.open line which is the most time consumptive. The question therefore is workbooks.open vs. dbconnection. open & potential alternative method performances.

Thanks