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

 

[Fluff]

How long did those methods take?
What version of Xl are you running?

 

[Luthius]

Excel Version: 14 64-bit
Windows: 7 Enterprise
1st Code:18s
2nd Code: 16s

Using Matlab: 0.8s

 

[Fluff]

The 2nd code only took me 3s for a sheet with 46710 rows & 32 columns

Have you looked into PowerQuery?

 

[MARK858]

Using Matlab: 0.8s

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

You are comparing it to Matlab but you don't state how you are importing it with Matlab, do you know that DisplayAlerts/ScreenUpdate/Calculation aren't turned off with the method you are using with Matlab for the comparison?

PS. obviously see Fluff's suggestion with Power Query.

 

[Luthius]

Interesting performance.

Well, what about several workbooks (100, 1000, +).
Using the method of Open/Close workbooks causes a crash. I tested it, by looping through 20 Workbooks with the same amount of data.

I was wondering what is the most reliable approach when is necessary to work with more than 10 files?!
What would be the alternative for the codes I presented (RecordSet/ Range)??

Related to Matlab, the main purpose of this post is to develop the most reliable and fast code when pulling data from different files using excel. The comparison was not actually "fair" due different technologies. Nevermind about Matlab

Ps.:I'm using excel 2010. Powerquery can be a interesting idea, but will be necessary to buy a new version of Excel, and it is not in my plans/budget.

 

[Fluff]

As far as I know, the PowerQuery add-in works with 2010

 

[MARK858]

As far as I know, the PowerQuery add-in works with 2010

Only the professional addition officially (although I did get it to work for a short time with Home/Sudent).

 

[Fluff]

Thanks for that Mark, didn't realise it was limited in that way.

 

[Luthius]

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.

If you guys can give more suggestion to pull a big amount of data (not only for a single file/source) taking into consideration performance and reliability.

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