+ Reply to Thread
Results 1 to 32 of 32

Consolidating data from multiple workbooks running very slowly

  1. #1
    Forum Contributor
    Join Date
    01-05-2010
    Location
    New York
    MS-Off Ver
    Excel 2016
    Posts
    747

    Consolidating data from multiple workbooks running very slowly

    I have the following code that copies the same data range, J8:Q8, from 50 different workbooks in a folder and pastes those ranges to one new workbook. This process takes an extremely long amount of time and I am wondering if there is another way to write the code which would run faster.

    Please Login or Register  to view this content.
    Last edited by rhudgins; 11-02-2010 at 12:48 PM.

  2. #2
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Consolidating data from multiple workbooks running very slowly

    Hello rhudgins,

    How long does it take?
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)

  3. #3
    Forum Contributor
    Join Date
    01-05-2010
    Location
    New York
    MS-Off Ver
    Excel 2016
    Posts
    747

    Re: Consolidating data from multiple workbooks running very slowly

    About 15 minutes for that one procedure. However, I run the same code in 10 different versions. The second procedure copies J9:Q9 to the second worksheet in the destination workbook, the third copies J10:Q10 to the third worksheet in the destination workbook and so on..So in all the entire procedure takes a few hours to run.

  4. #4
    Forum Expert
    Join Date
    01-03-2006
    Location
    Waikato, New Zealand
    MS-Off Ver
    2010 @ work & 2007 @ home
    Posts
    2,243

    Re: Consolidating data from multiple workbooks running very slowly

    hi,

    I've only glanced quickly at this & am off for dinner now, but you may find an improvement if you

    1) move the "ReDim Preserve MyFiles(1 To FNum)" out of the loop. I'm doubtful that you'll get much improvement but redim preserve is relatively time intensive ie it may be quicker to loop through all the files once & get the total count, redim once & then perform the loop again to add each file as an element to Myfiles.

    2) Make sure that the opened files don't update themselves (if you will still get the correct results?) by changing the open code to
    Please Login or Register  to view this content.
    3) Here's a list if links with a lot of similarity to your code & some slight differences that you could try out. I've seen it mentioned that you can read from a closed file using ADO, but I've never tried it. Your code looks like it was probably sourced from Ron DeBruin's site but I'll include it in this list as you may find another approach there...
    http://www.rondebruin.nl/copy7.htm
    http://www.erlandsendata.no/english/...adfromclosedwb

    http://www.visualbasic.happycodings....ff/code18.html

    4) How long is an extremely long time?
    Are you on a slow network?
    You could try using windows explorer to copy all the files (when they are closed), paste them to a temp folder on your c drive, then run the code over these copies of the files & then delete the copies.

    5) Another longshot... do files normally take a long time to open manually?
    If so, try talking to I.T. & getting your network connection/bandwidth improved.

    hth
    Rob
    Rob Brockett
    Kiwi in the UK
    Always learning & the best way to learn is to experience...

  5. #5
    Forum Expert
    Join Date
    01-03-2006
    Location
    Waikato, New Zealand
    MS-Off Ver
    2010 @ work & 2007 @ home
    Posts
    2,243

    Re: Consolidating data from multiple workbooks running very slowly

    I've just seen Leith's post after submitting my reply - he'll take good care of you

    btw, I believe the slowest part of the code will be the opening & closing of files, so I suggest you merge all 10 versions into a single version that gets all the info from each file as it processes them so the code doesn't "double handle" any of the files.

    Rob

  6. #6
    Forum Contributor
    Join Date
    01-05-2010
    Location
    New York
    MS-Off Ver
    Excel 2016
    Posts
    747

    Re: Consolidating data from multiple workbooks running very slowly

    It appears as though making sure the files don't update themselves improves the speed slightly

    I tried to move "ReDim Preserve MyFiles(1 To FNum)" out of the loop but I am getting an error subscript out of range. Is there a specific location that I should move it to?

  7. #7
    Forum Expert
    Join Date
    01-03-2006
    Location
    Waikato, New Zealand
    MS-Off Ver
    2010 @ work & 2007 @ home
    Posts
    2,243

    Re: Consolidating data from multiple workbooks running very slowly

    hi,

    Have you tried any of the other suggestions?
    I think your main constraint is going to be the network speed.

    1) (Re)moving the Redim Preserve may not help, but here's my untested suggestion which requires a second use of the loop.
    Please Login or Register  to view this content.
    4) This is very unlikely, but again, it depends on your network...
    The copying of files to your local drive can be done in code using the "FileCopy sourcefile, destinationfile" syntax. For more details refer to the VBE Help for FileCopy.


    Leith,
    I didn't mean to cut in - can you please add anything I've missed?
    Or, if necessary, laugh out loud & tell me I'm talking nonsense re saving time with the redim preserve...
    - I haven't done any testing in this case, but it has seemed to help me in different circumstances in the past (although that wasn't with dir).


    hth
    Rob

  8. #8
    Forum Contributor
    Join Date
    01-05-2010
    Location
    New York
    MS-Off Ver
    Excel 2016
    Posts
    747

    Re: Consolidating data from multiple workbooks running very slowly

    Thanks for your help but your suggestion did not speed up the process. I am going to try and use the code from one of the links you sent that covers copying code from closed workbooks. Thank you and I will keep you updated if I figure this out.

  9. #9
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Consolidating data from multiple workbooks running very slowly

    This code should speed up the process dramatically:

    Please Login or Register  to view this content.



  10. #10
    Forum Contributor
    Join Date
    01-05-2010
    Location
    New York
    MS-Off Ver
    Excel 2016
    Posts
    747

    Re: Consolidating data from multiple workbooks running very slowly

    Thanks. This part of the code is giving me the error subscript out of range

    Please Login or Register  to view this content.

  11. #11
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Consolidating data from multiple workbooks running very slowly

    So you'd better produce 15 sheets in thisworkbook.

  12. #12
    Forum Expert
    Join Date
    01-03-2006
    Location
    Waikato, New Zealand
    MS-Off Ver
    2010 @ work & 2007 @ home
    Posts
    2,243

    Re: Consolidating data from multiple workbooks running very slowly

    hi all,

    Snb & RomperStomper,
    Just to let you know that I think you are both working towards very similar goals - see the respective links for the flip side of the coin... http://www.excelforum.com/excel-prog...e-to-code.html & http://www.excelforum.com/excel-prog...ry-slowly.html

    Rhudgins,
    I think your various threads are so inter-related that they should all be in one place. Especially considering my below post which relates exactly to your question in this separate thread (http://www.excelforum.com/excel-prog...e-to-code.html).
    Quote Originally Posted by broro183 View Post
    Rhudgins,
    What does the "RunResults" macro do?
    To use someone else's words...
    Application.Run is synchronous. The call to Application.Run("Macro_1") will not return until the macro has finished executing. That said, if the macro starts an asynchronous process, then you might get the effect you describe. What do the macros do, and what evidence do you have that the macros are running in parallel? – Gary McGill Nov 9 '09 at 20:44
    Romper, should/can these threads be merged?
    Or is this linkage enough/too much ?
    (I've posted the same response in each of the threads.)

    Rob

  13. #13
    Forum Contributor
    Join Date
    01-05-2010
    Location
    New York
    MS-Off Ver
    Excel 2016
    Posts
    747

    Re: Consolidating data from multiple workbooks running very slowly

    Why would I need 15 worksheets? The code is supposed to consolidate the data into one worksheet one row at a time

  14. #14
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Consolidating data from multiple workbooks running very slowly

    Because you told us so:

    'About 15 minutes for that one procedure. However, I run the same code in 10 different versions. The second procedure copies J9:Q9 to the second worksheet in the destination workbook, the third copies J10:Q10 to the third worksheet in the destination workbook and so on..'

  15. #15
    Forum Contributor
    Join Date
    01-05-2010
    Location
    New York
    MS-Off Ver
    Excel 2016
    Posts
    747

    Re: Consolidating data from multiple workbooks running very slowly

    I am extremely confused to exactly what your written code is performing. I tried running your code and I have no idea where it is getting the data that it is copying.

    Here is a better description with new ranges that I intend to achieve to consolidate my multiple workbooks. The consolidated workbook will be called "Summary of Consolidated Results"

    On the 16th worksheet in every workbook I would like to copy the range J8:S8 to a new workbook's second worksheet starting in the second row . On the 16th worksheet in every workbook I would like to copy the range J9:S9 to that same new workbook's third worksheet starting in the second row. On the 16th worksheet in every workbook I would like to copy the range J10:S10 to that same new workbook's fourth worksheet starting in the second row. I would like to complete this same procedure for the 11th and 12th row as well.

    So in total there will be 5 new worksheets created in "Summary of Consolidated Results" Does this make sense?

  16. #16
    Forum Contributor
    Join Date
    01-05-2010
    Location
    New York
    MS-Off Ver
    Excel 2016
    Posts
    747

    Re: Consolidating data from multiple workbooks running very slowly

    I forgot to mention that the ranges J:S should be copied in column B of every row in the consolidated workbook. And is it possible to put the file name where the data came from in column A of every row?

  17. #17
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Consolidating data from multiple workbooks running very slowly

    I think you got enough suggestions to adapt to your specific wishes.

  18. #18
    Forum Contributor
    Join Date
    01-05-2010
    Location
    New York
    MS-Off Ver
    Excel 2016
    Posts
    747

    Re: Consolidating data from multiple workbooks running very slowly

    Last night I figured out how to edit the ranges in your code. The data now begins in the second column and second row of every worksheet.

    One last question- Is there a way copy the file name into the first column? If I dont have the file name it is extremely difficult to know which workbook each row of data is sourced from.

    Please Login or Register  to view this content.

  19. #19
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Consolidating data from multiple workbooks running very slowly

    change
    sn1 = ThisWorkbook.Sheets(1).Cells(2, 2).Resize(100, 10)
    into
    sn1 = ThisWorkbook.Sheets(1).Cells(1, 1).Resize(100, 11)
    change:

    For j = 1 To UBound(sq)
    Select Case j
    Case 1
    sn1(x, 1) = c02
    Case 2
    sn2(x, 1) = c02
    Case 3
    sn3(x, 1) = c02
    Case 4
    sn4(x, 1) = c02
    Case 5
    sn5(x, 1) = c02
    End Select

    For jj = 2 To 11
    Select Case j
    Case 1
    sn1(x, jj) = sq(j, jj)
    ----------------------

    -----------------------
    End Select
    Next
    Next
    Last edited by snb; 11-02-2010 at 11:13 AM.

  20. #20
    Forum Contributor
    Join Date
    01-05-2010
    Location
    New York
    MS-Off Ver
    Excel 2016
    Posts
    747

    Re: Consolidating data from multiple workbooks running very slowly

    This part of the code is giving the error subscript of of range

    Please Login or Register  to view this content.

  21. #21
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Consolidating data from multiple workbooks running very slowly

    sn1(x, jj) = sq(j, jj-1)

  22. #22
    Forum Contributor
    Join Date
    01-05-2010
    Location
    New York
    MS-Off Ver
    Excel 2016
    Posts
    747

    Re: Consolidating data from multiple workbooks running very slowly

    Thank you very much for all of your help. The code is perfect now!

  23. #23
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Consolidating data from multiple workbooks running very slowly

    You wanted to speed up the process.
    Can you inform us on this ?

  24. #24
    Forum Contributor
    Join Date
    01-05-2010
    Location
    New York
    MS-Off Ver
    Excel 2016
    Posts
    747

    Re: Consolidating data from multiple workbooks running very slowly

    I have not tested it on the entire database yet but I can tell you that it is MUCH FASTER

  25. #25
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Consolidating data from multiple workbooks running very slowly

    Just to inform you on the why:

    If possible open workbooks hidden (using the method Getobject); so no screenupdating will take place.
    In my code all 50 workbooks will be read only once.
    Avoid writing actions as much as possible.
    So you will notice that only 5 writing actions will take place in the macro.
    Store values into matrices (instead of cells)

    I think therefore that you can omit
    Please Login or Register  to view this content.

  26. #26
    Forum Contributor
    Join Date
    01-05-2010
    Location
    New York
    MS-Off Ver
    Excel 2016
    Posts
    747

    Re: Consolidating data from multiple workbooks running very slowly

    How do I open workbooks hidden?

    This is what my final code looks like.

    Please Login or Register  to view this content.

  27. #27
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Consolidating data from multiple workbooks running very slowly

    Using this macro you open workbooks 'hidden' (because of Getobject(c01 & c02) )

    I think this will suffice:
    Please Login or Register  to view this content.
    Last edited by snb; 11-02-2010 at 05:15 PM.

  28. #28
    Forum Contributor
    Join Date
    01-05-2010
    Location
    New York
    MS-Off Ver
    Excel 2016
    Posts
    747

    Re: Consolidating data from multiple workbooks running very slowly

    I am recieving a Type mismatch error at this line

    Please Login or Register  to view this content.

  29. #29
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Consolidating data from multiple workbooks running very slowly

    If you read the code carefully, you can restore these typos yourself:

    For jj = 1 To UBound(sn1, 2)

  30. #30
    Forum Contributor
    Join Date
    01-05-2010
    Location
    New York
    MS-Off Ver
    Excel 2016
    Posts
    747

    Re: Consolidating data from multiple workbooks running very slowly

    I am going to go with the first code. I am trying to change the code to begin on the 6th worksheet instead of the 1st worksheet of the consolidated workbook and am having trouble for some reason. I know it should be relatively simple but I am stumped. Can you help?
    Please Login or Register  to view this content.

  31. #31
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Consolidating data from multiple workbooks running very slowly

    NB.
    because of
    Please Login or Register  to view this content.
    take care that this range is empty, otherwise the matrix sn1 will contain values you don't want it to have.

    I hope you realize that sheets(1), sheets(2) etc. are not constantly referring to the same sheets. As soon as you add/delete a sheet the indexnumbers point to different sheets.
    But you can start in sheets(6) using:

    Please Login or Register  to view this content.

    Please Login or Register  to view this content.
    Last edited by snb; 11-03-2010 at 05:07 AM.

  32. #32
    Forum Contributor
    Join Date
    01-05-2010
    Location
    New York
    MS-Off Ver
    Excel 2016
    Posts
    747

    Re: Consolidating data from multiple workbooks running very slowly

    Thanks this helps alot. I made the changes and have it working.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1