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

[Luthius]

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?

[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".

 

[MARK858]

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.

 

[Luthius]

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.

 

[MARK858]

Just Like Collection is faster than Dictionary,

Interesting... might be worth scrolling to near the end of the link below and testing with the sample workbooks

https://www.experts-exchange.com/articles/3391/Using-the-Dictionary-Class-in-VBA.html

 

[Luthius]

Thanks for the website post.
But testing with a straightforward code the result is different.
See below

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]

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.

 

[MARK858]

Thanks for the website post.
But testing with a straightforward code the result is different.
See below

    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]

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

 

[NdNoviceHlp]

This whole thread here ending with using arrays of ranges collected from separate wbs then inserted in 1 wb may be of interest/ HTH, Dave
https://www.mrexcel.com/forum/excel-questions/1077924-loop-through-formula-macro-7.html