Speed up my workbook
Find bottlenecks in your Excel workbooks
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

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. Thats still 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?

    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

  2. #2
    MrExcel MVP Anne Troy's Avatar
    Join Date
    Feb 2002
    Location
    Westwood NJ
    Posts
    2,581
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Your better bet might be to clear the clipboard at the end of each loop (I'm not a real coder, so I'm not sure as to the terminology here--maybe procedure?)

    I do know that I've seen a lot of clearing the clipboard stuff.

    Here, I looked some up:

    Office 97 (Windows clipboard)

    Public Sub ClearClipboard()
    Dim MyDataObj As New DataObject
    MyDataObj.SetText ""
    MyDataObj.PutInClipboard
    End Sub


    Office 2000 + up (Office clipboard)

    Public Sub ClearClipItems()
    On Error Resume Next
    Application.CommandBars("Clipboard").Controls(4).Execute
    On Error Goto 0
    End Sub

    Hope it helps. I have not tested either of them.
    ~Anne Troy

  3. #3
    MrExcel MVP Anne Troy's Avatar
    Join Date
    Feb 2002
    Location
    Westwood NJ
    Posts
    2,581
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Well, I did not see that you posted this question twice.
    ~Anne Troy

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

    Default

    Dreamboat,
    Your approach is worth trying because so far I didn't get any satisfying results with anything else. Will keep you posted.
    Thanks
    Hans

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

    Default

    Hi

    some cool code from dreamboat there ill have some fun with them thanks Dreamboat

    Can i ask, wht you have not opyed for the same methods you noramll advise ie Disk Clean ups?

    Running slow would fall into this, im ever ready to learn, in lost to why addressing clipbaod issue was your choice, just feel this could be a cool tip i should remember.


    Free Excel based Web Toolbar available here.

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

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

    Default

    Hi

    Thios post is been knocking about a lot and sad not resolved sorry if i have not helpped.

    reading you post and passed post i would be surprised the coed is the issue and you seem to be competent at this level.

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

    This looks at OP resources, i would have a feeling.

    Our MVP Dreamboat is assisting you and i know she will take perfect care of you.

    Can i ask i dont recall exactly what OP your running and is E2000 patches as with Windows.

    This will help
    Free Excel based Web Toolbar available here.

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

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

    Default

      
    HI

    On feed http://www.mrexcel.com/board/viewtop...4234&forum=2&7

    all seem OK good stuff
    Free Excel based Web Toolbar available here.

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

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