Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    Join Date
    Nov 2011
    Location
    Australia
    Posts
    221
    Thanks
    80
    Thanked 3 Times in 2 Posts

    Macro possible for this sequence of tasks?

    Can a VBA, ( not sure where it will sit ) Macro do the following sequence ?

    Wrkbook A is running in Loop doing real-time web queries.

    It will have a counter, so after a Variable Count of 500 it then,
    Opens Wkbook B.

    Wrkbook B has macro to Save and Close Wrkbook A
    ----,

    Wkbook B will then Open Wrkbook A, Activate Main_Macro to Close Wrkbook B and resume web query till the next 500 count.

    or
    Would a Wrkbook C be a better option to overlook both A and B ?

    or
    a BAT file that is activated by Wrkbook A to Save/Close then Open/Activate Main_Macro

    Anyone has done this before or know how to code the better sequence ?
    or, something I don't know yet

    Thanks

  2. #2
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,180
    Thanks
    47
    Thanked 983 Times in 913 Posts
    I'd do the whole lot in a single workbook, then the macro is always running. The code would do something like this:
    Do 500 web queries.
    Save the workbook.
    Loop.

    Is that what you want to achieve?

    cheers, Paul

  3. #3
    3 Star Lounger
    Join Date
    Nov 2011
    Location
    Australia
    Posts
    221
    Thanks
    80
    Thanked 3 Times in 2 Posts
    Thanks Paul,

    No, I want to save/close and re-open and resume the web query, but it's not part of the Loop within the web query.
    The "trigger" is the #500 in a Cell Value.

    It's as if I stop the process manually using the Esc key, save/close and re-open and resume Macro manually.

    The reason for this is because Excel seems to "bloat", when I do my tests and tweaks, pressing the F8 key to check out the codes, it may take a few seconds to Toggle through the codes.
    But when I save/close and re-open manually and go to F8 to check things, it seems to have "unbloated", or free something up.

    This has been a ongoing thing for ages and have tried all sorts of things, maybe it's
    the nature of Excel VBA ?

  4. #4
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,180
    Thanks
    47
    Thanked 983 Times in 913 Posts
    How much memory is Excel consuming and how much do you have on your system?

    cheers, Paul

  5. #5
    3 Star Lounger
    Join Date
    Nov 2011
    Location
    Australia
    Posts
    221
    Thanks
    80
    Thanked 3 Times in 2 Posts
    I knew it would come to this, :-)

    XP Service Pack 3

    Intel Pentium
    processor 1.600Hz
    797MHz, 1.00GB of RAM

    ( auto updates, OFF and staying OFF )

    I am not sure how to accurately check how much memory Excel consumes
    during the continuous run/Loop of the Web Query.

  6. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    XPS,

    I'd go with Paul's suggestion of having a single "macro only" workbook that opened and closed the "Data" workbooks. I say this because it is my guess that it may be the undo feature that is clogging up memory and that would be cleared by opening and closing the Data workbooks. HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  7. #7
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,180
    Thanks
    47
    Thanked 983 Times in 913 Posts
    To check how much memory Excel consumes run Task Manager and select the Processes tab. (Right click on the taskbar.)

    You can run a macro from workbook A, then it can open, test and close workbook B.

    cheers, Paul

  8. #8
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,635
    Thanks
    115
    Thanked 649 Times in 592 Posts
    XP,

    This can be all done from within Workbook A. Integrate the first routine into your exiting code and call the second whatever you would like:

    Add to standard module:
    Code:
    Sub MacroName()
    
    '----EXISTING LOOP CODE----
    
    'SAVE AND CLOSE WORKBOOK. SCHEDULE RE-OPEN
        Application.OnTime Now + TimeValue("00:00:1"), "OpenWorkbook"  'CHANGE NAME TO MATCH MACRONAME
        ThisWorkbook.Close True
    End Sub
    
     
    Sub OpenWorkbook()
        [a1].Select
        'ADD CODE TO RUN WHEN WORKBOOK RE-OPENS
    End Sub
    HTH,
    Maud

  9. #9
    3 Star Lounger
    Join Date
    Nov 2011
    Location
    Australia
    Posts
    221
    Thanks
    80
    Thanked 3 Times in 2 Posts
    I haven't tried any suggestions yet, been figuring out what I've uploaded.

    The Folder is made to go in C:\
    Open PSUDO-BAT.xlsm and press the macro button shape, that will fire it.
    The real project, once it gets to it's end of the data querys, it goes to a message box anyway and it's just a manual "kill", after about 10 hours of a continuous Run

    It sort of works, it still displays alerts even though I put in AplicationDisplayAlerts = False twice before Save.

    I'll try your code to Maud, see what happens

    Thanks
    Attached Files Attached Files

Posting Permissions

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