[SOLVED] how do I update stock quotes using automation with vb

[John F]

I'm using VB 6 with Win XP Pro and Office 2002. I have a spreadsheet with msn
stock quotes that updates successfully using the keyboard (alt+u) or the
mouse (click on update quotes).

However, sending the same keys using sendkeys from VB 6 produces the "#Name"
error in Excel, with no prices updated.

Any ideas on how to use sendkeys to accomplish the quote update? Or another
method?

Thanks

[Myrna Larson]

What routine is called when you click the button from the worksheet? Why not
call that routine from your VB program?

BTW, MS has a stock quotes add-in that you can download from their web site.


On Tue, 4 Oct 2005 13:03:21 -0700, John F <JohnF@discussions.microsoft.com>
wrote:

>I'm using VB 6 with Win XP Pro and Office 2002. I have a spreadsheet with msn
>stock quotes that updates successfully using the keyboard (alt+u) or the
>mouse (click on update quotes).
>
>However, sending the same keys using sendkeys from VB 6 produces the "#Name"
>error in Excel, with no prices updated.
>
>Any ideas on how to use sendkeys to accomplish the quote update? Or another
>method?
>
>Thanks

[John F]

I am using the MSN stock quotes addin to update the quotes. I have tried
creating a macro to click the Update button, but the macro doesn't recognize
the click event. Is there a macro setting that prevents some click events
from being recognized?

Thanks

"Myrna Larson" wrote:

> What routine is called when you click the button from the worksheet? Why not
> call that routine from your VB program?
>
> BTW, MS has a stock quotes add-in that you can download from their web site.
>
>
> On Tue, 4 Oct 2005 13:03:21 -0700, John F <JohnF@discussions.microsoft.com>
> wrote:
>
> >I'm using VB 6 with Win XP Pro and Office 2002. I have a spreadsheet with msn
> >stock quotes that updates successfully using the keyboard (alt+u) or the
> >mouse (click on update quotes).
> >
> >However, sending the same keys using sendkeys from VB 6 produces the "#Name"
> >error in Excel, with no prices updated.
> >
> >Any ideas on how to use sendkeys to accomplish the quote update? Or another
> >method?
> >
> >Thanks
>

[Bill Martin]

John F wrote:
> I'm using VB 6 with Win XP Pro and Office 2002. I have a spreadsheet with msn
> stock quotes that updates successfully using the keyboard (alt+u) or the
> mouse (click on update quotes).
>
> However, sending the same keys using sendkeys from VB 6 produces the "#Name"
> error in Excel, with no prices updated.
>
> Any ideas on how to use sendkeys to accomplish the quote update? Or another
> method?
>
> Thanks
>

If you right click on the button and follow your nose through the menus a bit
you can see the name of the macro assigned to that button. Then from your macro
you're writing you can just directly call that macro by name rather than
screwing around with the button.

Bill

[John F]

I tried this but the MSN commands don't show up in the Commands list.
Thanks

"Bill Martin" wrote:

> John F wrote:
> > I'm using VB 6 with Win XP Pro and Office 2002. I have a spreadsheet with msn
> > stock quotes that updates successfully using the keyboard (alt+u) or the
> > mouse (click on update quotes).
> >
> > However, sending the same keys using sendkeys from VB 6 produces the "#Name"
> > error in Excel, with no prices updated.
> >
> > Any ideas on how to use sendkeys to accomplish the quote update? Or another
> > method?
> >
> > Thanks
> >
>
> If you right click on the button and follow your nose through the menus a bit
> you can see the name of the macro assigned to that button. Then from your macro
> you're writing you can just directly call that macro by name rather than
> screwing around with the button.
>
> Bill
>

[Bill Martin]

John F wrote:
> I tried this but the MSN commands don't show up in the Commands list.
> Thanks
>
> "Bill Martin" wrote:
>
>
>>John F wrote:
>>
>>>I'm using VB 6 with Win XP Pro and Office 2002. I have a spreadsheet with msn
>>>stock quotes that updates successfully using the keyboard (alt+u) or the
>>>mouse (click on update quotes).
>>>
>>>However, sending the same keys using sendkeys from VB 6 produces the "#Name"
>>>error in Excel, with no prices updated.
>>>
>>>Any ideas on how to use sendkeys to accomplish the quote update? Or another
>>>method?
>>>
>>>Thanks
>>>
>>
>>If you right click on the button and follow your nose through the menus a bit
>>you can see the name of the macro assigned to that button. Then from your macro
>>you're writing you can just directly call that macro by name rather than
>>screwing around with the button.
>>
>>Bill
>>
------------------------

There are no command lists involved.

Right click on the button, then on "Assign Macro" and you should see the name of
the called macro in the little box at the top of the panel. Cancel out of
there, then create a new macro as follows:

sub ExecuteButton()
Call ButtonNameMacro()
end sub

You should see the button macro executed when you run this new macro.

Bill

[John F]

Bill;
If I right-click on the Update button I get a menu of toolbars, as if
clicking on the View-Toolbars menu.

However, I have found a workaround - if I manually open Excel and leave it
running the VB Sendkeys method is successful. If VB starts Excel, then the
Sendkeys method fails.

Thanks to all for helping.

John F.

"Bill Martin" wrote:

> John F wrote:
> > I tried this but the MSN commands don't show up in the Commands list.
> > Thanks
> >
> > "Bill Martin" wrote:
> >
> >
> >>John F wrote:
> >>
> >>>I'm using VB 6 with Win XP Pro and Office 2002. I have a spreadsheet with msn
> >>>stock quotes that updates successfully using the keyboard (alt+u) or the
> >>>mouse (click on update quotes).
> >>>
> >>>However, sending the same keys using sendkeys from VB 6 produces the "#Name"
> >>>error in Excel, with no prices updated.
> >>>
> >>>Any ideas on how to use sendkeys to accomplish the quote update? Or another
> >>>method?
> >>>
> >>>Thanks
> >>>
> >>
> >>If you right click on the button and follow your nose through the menus a bit
> >>you can see the name of the macro assigned to that button. Then from your macro
> >>you're writing you can just directly call that macro by name rather than
> >>screwing around with the button.
> >>
> >>Bill
> >>
> ------------------------
>
> There are no command lists involved.
>
> Right click on the button, then on "Assign Macro" and you should see the name of
> the called macro in the little box at the top of the panel. Cancel out of
> there, then create a new macro as follows:
>
> sub ExecuteButton()
> Call ButtonNameMacro()
> end sub
>
> You should see the button macro executed when you run this new macro.
>
> Bill
>
>

[Bill Martin]

John F wrote:
> Bill;
> If I right-click on the Update button I get a menu of toolbars, as if
> clicking on the View-Toolbars menu.
>
> However, I have found a workaround - if I manually open Excel and leave it
> running the VB Sendkeys method is successful. If VB starts Excel, then the
> Sendkeys method fails.
>
> Thanks to all for helping.
>
> John F.
>
> "Bill Martin" wrote:
>
>
>>John F wrote:
>>
>>>I tried this but the MSN commands don't show up in the Commands list.
>>>Thanks
>>>
>>>"Bill Martin" wrote:
>>>
>>>
>>>
>>>>John F wrote:
>>>>
>>>>
>>>>>I'm using VB 6 with Win XP Pro and Office 2002. I have a spreadsheet with msn
>>>>>stock quotes that updates successfully using the keyboard (alt+u) or the
>>>>>mouse (click on update quotes).
>>>>>
>>>>>However, sending the same keys using sendkeys from VB 6 produces the "#Name"
>>>>>error in Excel, with no prices updated.
>>>>>
>>>>>Any ideas on how to use sendkeys to accomplish the quote update? Or another
>>>>>method?
>>>>>
>>>>>Thanks
>>>>>
>>>>
>>>>If you right click on the button and follow your nose through the menus a bit
>>>>you can see the name of the macro assigned to that button. Then from your macro
>>>>you're writing you can just directly call that macro by name rather than
>>>>screwing around with the button.
>>>>
>>>>Bill
>>>>
>>
>>------------------------
>>
>>There are no command lists involved.
>>
>>Right click on the button, then on "Assign Macro" and you should see the name of
>>the called macro in the little box at the top of the panel. Cancel out of
>>there, then create a new macro as follows:
>>
>> sub ExecuteButton()
>> Call ButtonNameMacro()
>> end sub
>>
>>You should see the button macro executed when you run this new macro.
>>
>>Bill
>>
>>
-------------------

Ah -- apparently your button in question was placed into the tool bar rather
than onto the spreadsheet itself. That's typically done from within the VBA
code of the application. You'd have to look into the VBA to understand what
they're calling.

Bill