Speed up my workbook
Speed up my workbook
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Speed up my workbook

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Location
    Philippines
    Posts
    55
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    Hi there
    I am using Excel 2000.
    I created a workbook with several macros which do the following:
    From sheet 1, using a web query it extracts a table of data from an external website and puts it in a range of cells on sheet 1.
    It then copies one by one 9 different cells from that range and pastes it into a different order on sheet 3.
    Then using a list of keywords stored on sheet 2, it repeats the procedure but going to a different page on the website.
    There are alltogether 26 cut or copy or paste actions involved in one run, which apparently slows everything down.
    When I first started to run that thing, it extracted about 40 sets of data a minute. Then, after about 1000 runs, it slowed down to 1 set in 3 minutes!
    I changed the code by adding a macro to cut the accumulated data on sheet 3 and pasted it into a second workbook. It helped a little, now I extract about 3 sets per minute. The speed is now constant. But not good enough. I have to extract 130000 sets of data, that would take me about a month of permanent running to extract them all!!!
    Any idea how to speed that thing up?
    If needed I would send you the complete code.
    Hans

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Posts
    3,169
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi

    Always best to post up codes so programers can have a once over..

    resources are the key in windows OP enviorments and this will be much of the issues..

    check my posts previously from in for on these, or re post on this feed if perticula issues you need to ask
    Free Excel based Web Toolbar available here.

    Jack in the UK
    J & R Excel Solutions
    "making Excel work for you"

  3. #3
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Hans,

    Without seeing your code, it would be hard to determine exactly how to speed the routine.

    The standards for calculation speed include:

    1. Turn off screen updating
    Application.ScreenUpdating = False

    2. Turn off auto calc
    Application.Calculation = xlCalculationManual

    be careful with this one, though and be sure to turn it back to auto when necessary
    Application.Calculation = xlCalculationAutomatic

    3. Make your code as efficient as possible and never select anything unless required.

    Search http://www.decisionmodels.com for a bunch of details about code efficiency.

    I agree with Jack, though, on this one in that you'll get a better response if you post the code you are using.

    HTH,
    Jay

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Location
    Philippines
    Posts
    55
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I found out that I get the best speed when I first open the workbook and then extract about 20 addresses. After that it starts slowing down. So if I close the workbook again and open it again, it starts with the same efficient speed. I suppose that the many copy/cut and paste operations will be written somewhere to a back memory and once this is getting filled up the speed slows down. Any idea how I can prevent that or do I have to add a macro to close and reopen the workbook after lets say 20 - 25 runs, and how to do that?
    Hans

  5. #5
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Hans

    I would guess your problem could be due to NOT bypassing the ClipBoard in Excel and hence filling it up..

    Sheet1.Range("A1:A100").Copy Desination:=Sheet2.Range("A1")

    Is the preffered way of copying/cutting in Excel.

    I have many ways and means that can (and should) be used found here:

    http://www.ozgrid.com/VBA/SpeedingUpVBACode.htm

  6. #6
    Board Regular
    Join Date
    Mar 2002
    Location
    Philippines
    Posts
    55
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Dave
    Thanks, it works fine. One more line I need though:
    Cut and paste into a worksheet in another open workbook by avoiding the clipboard.
    Hans

  7. #7
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Hans

    Try:

    Code:
    Sheet1.Range("A1:A100").Cut Destination:= _
    Workbooks("Book1.xls").Sheets("Sheet2").Range("A1")
    _________________
    Kind Regards
    Dave Hawley
    OzGrid Business Applications
    Microsoft Excel/VBA Training


    [ This Message was edited by: Dave Hawley on 2002-04-07 06:35 ]

  8. #8
    Board Regular
    Join Date
    Mar 2002
    Location
    Philippines
    Posts
    55
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks Dave,
    It works again.
    Let me run it overnight (it's now 11:30 PM in the Philippines), and I will see tomorrow whether the speed stay's constant.
    Hans

  9. #9
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Hans

    If it doesn't help, just post your entire code.




  10. #10
    Board Regular
    Join Date
    Mar 2002
    Location
    Philippines
    Posts
    55
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Hi Guys
    Anybody who helped me on this one, THANKS.
    Actuall, the solution was to delete the worksheet where the data where coming in after 100 runs, add a new sheet and do the next 100 runs.
    It's running now with a speed of 40 sets of data per minute and thats beautiful.
    Thanks again
    Hans

User Tag List

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
  •  

 

 
DMCA.com