webquery and solver macros

hi,

recently, i have been dabbling with recording webqueries and solver procedures.

i have observed the following.

webquery:

when i record (macro) a webquery from a website, for example - yahoo finance, i can assign (or define) a range name or reference for that table (viz, currency). when i execute the webquery macro over and over again, every time it runs - it creates a new name viz, currency_1, currency_2, currency_3, and this goes on. i would have imagined it would overwrite the existing name or reference. thats what i intend it to do.

is there a way around it? i have not been able to figure out, first, how to avoid this, and second, how to delete a reference name for which i know the first few letters will be 'currency' (in my case).

solver:

solver is defined with a set of constraints (without which there is no purpose of using a solver, duh). when i record a solver macro, and run it over and over again, probably on 7th or 8th turn the solver will refuse to solve it for you. here is what happens. it would have added the same constraint(s) as many number of times as you have run the solver macro. as a denoument, the solver will not be able to spit out a solution.

now, is there a way to avoid this, or delete constraints? i know that we can record the process of deleting a constraint. if it were the first time you were running the macro, and there were no constraints, the program would err.

i would appreciate any help that comes this way.

thanks,

mac.

> now, is there a way to avoid this, or delete constraints?

Hi. One way is to just run the macro, and after each solve, then delete the
constraints.
One can delete constraints in a loop, but I have found "Resetting"
everything to be an easier solution. On does this with SolverReset.
Here's an example..

For j = 1 To 20
SolverReset
SolverOk [A1], 1, [B1]
'...etc
Next j

--
HTH. :>)
Dana DeLouis
Windows XP, Office 2003


"icestationzbra"
<icestationzbra.23nlem_1140649801.0463@excelforum-nospam.com> wrote in
message news:icestationzbra.23nlem_1140649801.0463@excelforum-nospam.com...
>
> hi,
>
> recently, i have been dabbling with recording webqueries and solver
> procedures.
>
> i have observed the following.
>
> webquery:
>
> when i record (macro) a webquery from a website, for example - yahoo
> finance, i can assign (or define) a range name or reference for that
> table (viz, currency). when i execute the webquery macro over and over
> again, every time it runs - it creates a new name viz, currency_1,
> currency_2, currency_3, and this goes on. i would have imagined it
> would overwrite the existing name or reference. thats what i intend it
> to do.
>
> is there a way around it? i have not been able to figure out, first,
> how to avoid this, and second, how to delete a reference name for which
> i know the first few letters will be 'currency' (in my case).
>
> solver:
>
> solver is defined with a set of constraints (without which there is no
> purpose of using a solver, duh). when i record a solver macro, and run
> it over and over again, probably on 7th or 8th turn the solver will
> refuse to solve it for you. here is what happens. it would have added
> the same constraint(s) as many number of times as you have run the
> solver macro. as a denoument, the solver will not be able to spit out a
> solution.
>
> now, is there a way to avoid this, or delete constraints? i know that
> we can record the process of deleting a constraint. if it were the
> first time you were running the macro, and there were no constraints,
> the program would err.
>
> i would appreciate any help that comes this way.
>
> thanks,
>
> mac.
>
>
> --
> icestationzbra
> ------------------------------------------------------------------------
> icestationzbra's Profile:
> http://www.excelforum.com/member.php...fo&userid=4580
> View this thread: http://www.excelforum.com/showthread...hreadid=515550
>

hi dana,

thanks for that one! the reset button was right there and i never even noticed it! duh me...

mac.