Macro possible for this sequence of tasks?

Topic

New Reply

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

Reply | Quote

Replies

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

Reply | Quote

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 ?

Reply | Quote

How much memory is Excel consuming and how much do you have on your system?

cheers, Paul

Reply | Quote

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.

Reply | Quote

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 :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

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

Reply | Quote

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 [I][B]MacroName[/B][/I]()

[COLOR=”#0000FF”]’—-EXISTING LOOP CODE—-[/COLOR]

[COLOR=”#008000″]’SAVE AND CLOSE WORKBOOK. SCHEDULE RE-OPEN[/COLOR]
    Application.OnTime Now + TimeValue(“00:00:1”), “OpenWorkbook”  [COLOR=”#008000″]’CHANGE NAME TO MATCH MACRONAME[/COLOR]
    ThisWorkbook.Close True
End Sub

 
Sub OpenWorkbook()
    [a1].Select
    [COLOR=”#008000″]’ADD CODE TO RUN WHEN WORKBOOK RE-OPENS[/COLOR]
End Sub

HTH,
Maud

Reply | Quote

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

Reply | Quote