Performance: What is the best "method" to pull data from Several WorkBooks using VBA

Luthius

Active Member
Joined
Apr 5, 2011
Messages
324
Guys
I know that there is more than one way to skin a cat, but what is the best way to pull down data from more than 01 workbook taking into consideration the performance of the code?


  1. Pull data by a recordset?
  2. Pull data by Copying range
  3. Another Method/Approach?

The codes below I developed as a matter of testing its performance and was developed to pull data of 01 Workbook only. I am disappointed with the results in both codes.

The Source Workbook contains less than 900k records along (43587 rows and 22 Columns). In my opinion it is not a huge number of records nowadays.

Does anyone know some better solution that gives a "faster" response?

Code:
[COLOR=#0000ff]Public Const[/COLOR] strDataSource [COLOR=#0000ff]As String[/COLOR] = "C:\Users\luthius\Documents\MyData.xlsx"
    [COLOR=#008000]'By Recordset[/COLOR]
[COLOR=#0000ff]Sub[/COLOR] PullDataByRecordset()
    [COLOR=#008000]'Reference to Microsoft ActiveX Data Objects x.x Library[/COLOR]
    [COLOR=#0000ff]Dim[/COLOR] wbADOCn [COLOR=#0000ff]As New[/COLOR] ADODB.Connection
    [COLOR=#0000ff]Dim[/COLOR] strCn [COLOR=#0000ff]As[/COLOR] String
    [COLOR=#0000ff]Dim[/COLOR] rst [COLOR=#0000ff]As [/COLOR][COLOR=#0000ff]New[/COLOR] ADODB.Recordset
    [COLOR=#0000ff]Dim[/COLOR] strSQL [COLOR=#0000ff]As[/COLOR] String
    [COLOR=#0000ff]Dim [/COLOR]wbDestination [COLOR=#0000ff]As[/COLOR] Workbook


    [COLOR=#0000ff]Set[/COLOR] wbDestination = ActiveWorkbook

    strCn = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
            "Data Source=" & strDataSource & _
            ";Extended Properties=""Excel 12.0 Xml;HDR=Yes"";"
    strSQL = "Select * from [Sheet1$]"

    wbADOCn.Open strCn
    rst.Open strSQL, wbADOCn

    wbDestination.Sheets("Sheet2").Range("A2").CopyFromRecordset rst

    rst.Close
    wbADOCn.Close


    [COLOR=#0000ff]Set [/COLOR]wbADOCn = [COLOR=#0000ff]Nothing[/COLOR]
    [COLOR=#0000ff]Set[/COLOR] rst = [COLOR=#0000ff]Nothing[/COLOR]
[COLOR=#0000ff]End Sub[/COLOR]

    [COLOR=#008000]'By Copying range[/COLOR]
[COLOR=#0000ff]Sub[/COLOR] pullDataByCopyingRange()
    [COLOR=#0000ff]Dim[/COLOR] wbSource [COLOR=#0000ff]As[/COLOR] Workbook
    [COLOR=#0000ff]Dim[/COLOR] rngSource [COLOR=#0000ff]As[/COLOR] Range
    [COLOR=#0000ff]Dim [/COLOR]wbDestination [COLOR=#0000ff]As[/COLOR] Workbook

    [COLOR=#0000ff]Set[/COLOR] wbDestination = ActiveWorkbook
    [COLOR=#0000ff]Set[/COLOR] wbSource = Workbooks.Open(strDataSource)
    [COLOR=#0000ff]Set[/COLOR] rngSource = wbSource.Worksheets("Sheet1").UsedRange

    rngSource.Offset(1).Copy

    wbDestination.Sheets("Sheet2").Range("A2").PasteSpecial Paste:=xlPasteValues

    wbSource.Close [COLOR=#0000ff]False[/COLOR]
    [COLOR=#0000ff]Set[/COLOR] wbSource = [COLOR=#0000ff]Nothing[/COLOR]
    [COLOR=#0000ff]Set[/COLOR] rngSource = [COLOR=#0000ff]Nothing[/COLOR]
   [COLOR=#0000ff] Set[/COLOR] wbDestination = [COLOR=#0000ff]Nothing[/COLOR]
[COLOR=#0000ff]End Sub[/COLOR]

    [COLOR=#008000]' Or by sweeping data using some array or another approach?[/COLOR]
[COLOR=#0000ff]Sub[/COLOR] pullDataByArray()
[COLOR=#008000]'????[/COLOR]
[COLOR=#0000ff]End Sub[/COLOR]

Ps.: The DisplayAlerts/ScreenUpdate/Calculation were not turned off because I wanted to study the behaviour/performance of the code - "Raw code".
 
Last edited:

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,233
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
Thanks for that Mark, didn't realise it was limited in that way.

Your welcome (especially for interpreting as I put addition rather than Edition). Just for completeness you need Office 2010 Professional Plus.
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Luthius

Active Member
Joined
Apr 5, 2011
Messages
324
My purpose is understand the limitations and also the pros and cons.
Just Like Collection is faster than Dictionary, what would be the best options when bearing with a big amount of data (different files).




Thanks for your comments so far. I really appreciate it.
 

Luthius

Active Member
Joined
Apr 5, 2011
Messages
324
Thanks for the website post.
But testing with a straightforward code the result is different.
See below
Code:
Option Explicit

Sub Collection_VS_Dictionary()
    Const iterations = 500000
    Dim i As Long
    Dim clc As Collection
    Dim sngDuration As Single


    Set clc = New Collection


    sngDuration = Timer
    For i = 1 To iterations
        clc.Add i, CStr(i)
    Next i
    Debug.Print "Collection: " & Timer - sngDuration
    Set clc = Nothing


    Dim dic As Object  'Scripting.Dictionary
    Set dic = New Scripting.Dictionary
    sngDuration = Timer
    For i = 1 To iterations
        dic.Add CStr(i), i
    Next i
    Debug.Print "Dictionary: " & Timer - sngDuration
    Set dic = Nothing


End Sub
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
64,110
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

what would be the best options when bearing with a big amount of data (different files)
In my opinion there is no such thing as a "Best" option, just different ways of doing things.
What is "Best" depends on your exact situation, your knowledge (especially if you want to be able to modify later), what tools you have available, etc etc.

Another possibility might be to pull everything into a database.
 
Last edited:

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,233
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
Thanks for the website post.
But testing with a straightforward code the result is different.
See below
Code:
    For i = 1 To iterations
        dic.Add CStr(i), i

From the article....

Based on my testing, the "Exists" approach is slightly faster than the "Item" approach, and both are significantly faster than the "Add" approach. (Please see Performance: Dictionary vs. Collection for more details.)
 

Luthius

Active Member
Joined
Apr 5, 2011
Messages
324
In my opinion there is no such thing as a "Best" option, just different ways of doing things.
What is "Best" depends on your exact situation, your knowledge (especially if you want to be able to modify later), what tools you have available, etc etc.
When I wrote best I mean for peformance sake. Just like I wrote before

My purpose is understand the limitations and also the pros and cons.
For instance, we know that Collection is faster than Dictionary. But Dictionary has more features that Collection. So we can choose what is the best as per need and per limitation as well.

CStr(i) was used on both

clc.Add i, CStr(i)
dic.Add CStr(i), i
 
Last edited:

Forum statistics

Threads
1,144,629
Messages
5,725,384
Members
422,622
Latest member
Paranthem

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