Fastest Code to Open a workbook

NatetheGreat

Active Member
Joined
Nov 18, 2013
Messages
268
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.
Code:
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


Code:
  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
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Arithos

Well-known Member
Joined
Aug 14, 2014
Messages
598
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.
Code:
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


Code:
  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
Code:
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

Active Member
Joined
Nov 18, 2013
Messages
268
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,651
Messages
5,838,576
Members
430,557
Latest member
MK15

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top