Page 1 of 2 12 LastLast
Results 1 to 10 of 18

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

  1. #1
    Board Regular
    Join Date
    Apr 2011
    Posts
    253
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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:
    Public Const strDataSource As String = "C:\Users\luthius\Documents\MyData.xlsx"
        'By Recordset
    Sub PullDataByRecordset()
        'Reference to Microsoft ActiveX Data Objects x.x Library
        Dim wbADOCn As New ADODB.Connection
        Dim strCn As String
        Dim rst As New ADODB.Recordset
        Dim strSQL As String
        Dim wbDestination As Workbook
    
    
        Set 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
    
    
        Set wbADOCn = Nothing
        Set rst = Nothing
    End Sub
    
        'By Copying range
    Sub pullDataByCopyingRange()
        Dim wbSource As Workbook
        Dim rngSource As Range
        Dim wbDestination As Workbook
    
        Set wbDestination = ActiveWorkbook
        Set wbSource = Workbooks.Open(strDataSource)
        Set rngSource = wbSource.Worksheets("Sheet1").UsedRange
    
        rngSource.Offset(1).Copy
    
        wbDestination.Sheets("Sheet2").Range("A2").PasteSpecial Paste:=xlPasteValues
    
        wbSource.Close False
        Set wbSource = Nothing
        Set rngSource = Nothing
        Set wbDestination = Nothing
    End Sub
    
        ' Or by sweeping data using some array or another approach?
    Sub pullDataByArray()
    '????
    End Sub
    Ps.: The DisplayAlerts/ScreenUpdate/Calculation were not turned off because I wanted to study the behaviour/performance of the code - "Raw code".
    Last edited by Luthius; Jun 16th, 2019 at 10:07 AM.

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    23,796
    Post Thanks / Like
    Mentioned
    401 Post(s)
    Tagged
    42 Thread(s)

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

    How long did those methods take?
    What version of Xl are you running?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #3
    Board Regular
    Join Date
    Apr 2011
    Posts
    253
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

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

    Using Matlab: 0.8s

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    23,796
    Post Thanks / Like
    Mentioned
    401 Post(s)
    Tagged
    42 Thread(s)

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

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

    Have you looked into PowerQuery?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  5. #5
    Board Regular MARK858's Avatar
    Join Date
    Nov 2010
    Location
    Southern England
    Posts
    10,872
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    2 Thread(s)

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

    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.
    Test VBA on a copy of your data (remember you can't normally reverse the action)

    Please follow the forum Rules and Guidelines & please use Code tags around your code i.e. [CODE]your code[/CODE]

    To post a screenshot try one of these links
    MrExcel HTML Maker, RoryA addin (Win & Mac) or Borders-Copy-Paste

  6. #6
    Board Regular
    Join Date
    Apr 2011
    Posts
    253
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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.
    Last edited by Luthius; Jun 16th, 2019 at 12:45 PM.

  7. #7
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    23,796
    Post Thanks / Like
    Mentioned
    401 Post(s)
    Tagged
    42 Thread(s)

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

    As far as I know, the PowerQuery add-in works with 2010
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  8. #8
    Board Regular MARK858's Avatar
    Join Date
    Nov 2010
    Location
    Southern England
    Posts
    10,872
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    2 Thread(s)

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

    Quote Originally Posted by Fluff View Post
    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).
    Test VBA on a copy of your data (remember you can't normally reverse the action)

    Please follow the forum Rules and Guidelines & please use Code tags around your code i.e. [CODE]your code[/CODE]

    To post a screenshot try one of these links
    MrExcel HTML Maker, RoryA addin (Win & Mac) or Borders-Copy-Paste

  9. #9
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    23,796
    Post Thanks / Like
    Mentioned
    401 Post(s)
    Tagged
    42 Thread(s)

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

    Thanks for that Mark, didn't realise it was limited in that way.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  10. #10
    Board Regular
    Join Date
    Apr 2011
    Posts
    253
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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.
    Last edited by Luthius; Jun 16th, 2019 at 01:27 PM.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •