MS Stock Quote [RESOLVED]

[GSIV]

Is there a way to update the stock quotes from a macro without having to press the update button on the toolbar?

Also, is there a way to disable the advertisement that pops up after it updates?

Thanks!

[RobDog888]

You may be able to do it using using the Timer function and the DoCmd object to
execute the menu item for updating the stock quote.

From the help file...

Dim PauseTime, Start, Finish, TotalTime
If (MsgBox("Press Yes to pause for 5 seconds", 4)) = vbYes Then
    PauseTime = 5    ' Set duration.
    Start = Timer    ' Set start time.
    Do While Timer < Start + PauseTime
        DoEvents    ' Yield to other processes.
    Loop
    Finish = Timer    ' Set end time.
    TotalTime = Finish - Start    ' Calculate total time.
    MsgBox "Paused for " & TotalTime & " seconds"
Else
    End
End If

[GSIV]

Thanks RobDog for the response. I'm lost. The vba stuff I can find regarding DoCmd gives examples for Access. I've never even heard of this object before, much less know how to use it to pass information to StockQuote. Do you have anything specific?

Thanks for your time.

[RobDog888]

Sorry about that. I had just answered a thread on Access and I was still in Access mode

I post something in a min.

[RobDog888]

Found it. If you record a macro doing your stock quote refresh/update you can then run this macro from the Run command like so.

mySum = Application.Run("MYCUSTOM.XLM!My_Func_Sum", 1, 5)
MsgBox "Macro result: " & mySum

[GSIV]

The macro recorder will not record the process; at least I don't know how to make it do so. I have no problem with recording macros, it just won't record this particular function. The stock quotes add-in is available directly from MS: http://www.microsoft.com/downloads/d...displaylang=EN

In addition, I have also tried the research task pane version and cannot get it to record a macro either.

[RobDog888]

In that case you need to use the CommandBars collection and automate the .Execute method of the
particular button that gets the update.

[GSIV]

RobDog, you are blowing me away. I know a little bit, but you are way above my understanding.

Are you using the MSQuotes add-in yourself? I am hoping that someone who uses it might have already figured out how to automate the update function and clear the advertisement screen.

[RobDog888]

Sorry, I will try better to explain it. The toolbar that is called - ???. You can find it when you right click
the menu bars. It will show a check mark next to the ones that are currently being displayed.
Once we have the name of the toolbar we can find the button that does the update. Then we can click it programmatically.

Or you can use this procedure to print out a listing of all the toolbars in Excel to the immediate window behind Excel in the VBA IDE.

Private Sub PrintToolBars()
    Dim i As Integer
    For i = 1 To Excel.CommandBars.Count
        Debug.Print Excel.CommandBars(i).Name
    Next
End Sub

[GSIV]

Ok- I'm with you again. Thanks for dragging me along!

The toolbar is called "MSN Money Stock Quotes"

[RobDog888]

Ok, since I dont have the add-in (just dont want to install it) you need to debug this, although its
probably very close if not working. I dont know where or how you want to automate this, but the
command button click was just for looks

Option Explicit
'msoControlButton or msoControlCustom in the .FindControl method
'Depends on how they created the toolbar. Try either and check the spelling 
'on the update button, must match exactly.
Private Sub Command1_Click()
    Dim oApp As Excel.Application
    Dim oWB As Excel.Workbook
    Dim oToolBar As Office.CommandBar
    Dim oUpdateBtn As Office.CommandBarButton
    
    Set oApp = New Excel.Application
    oApp.Visible = True
    Set oWB = oApp.Workbooks.Open("C:\YourWorkBook.xls")
    Set oToolBar = oWB.CommandBars.Item("MSN Money Stock Quotes")
    If TypeName(oToolBar) <> "Nothing" Then
        Set oUpdateBtn = oToolBar.FindControl(msoControlButton, oToolBar.Control("Update").ID)
        oUpdateBtn.Execute
    Else
        'Toolbar not loaded
    End If
End Sub

[GSIV]

A couple of snags, but this is promising for sure! This stuff is amazing. Thank God for experts!

1. Runtime error 91: object variable or with block variable not set

Set oToolBar = ActiveWorkbook.CommandBars.Item("MSN Money Stock Quotes")

Same thing with the original code...

Set oToolBar = oWB.CommandBars.Item("MSN Money Stock Quotes")

I verified that my commandbar name was exactly right. I ran your PrintToolBars routine and it is there. I also right-clicked on the commandbar and selected customize | toolbar and it was exactly the same name.


2. I changed the line with 'oToolBar.Control' to 'oToolBar.Controls' because it wouldn't run otherwise.

I am going to run this from within an existing worksheet and call it as a sub when the workbook (or worksheet?) opens. I modified the code to try to reflect my purpose. However, the original code still generates the error 91 message too. Just so you know what I did, here is my modified version:

Public Sub StockUpdate()
    
    Dim oToolBar As Office.CommandBar
    Dim oUpdateBtn As Office.CommandBarButton
    
    Set oToolBar = ActiveWorkbook.CommandBars.Item("MSN Money Stock Quotes")
    
    If TypeName(oToolBar) <> "Nothing" Then
        Set oUpdateBtn = oToolBar.FindControl(msoControlButton, oToolBar.Controls("Update Quotes").ID)
        oUpdateBtn.Execute
    Else
        'Toolbar not loaded
    End If
 
End Sub

Thanks for your review.

[GSIV]

RobDog,

I don't know if it'll help, but I ran the CommandBarDocumenter and CommandBarControlDocumenter subroutines mentioned on the MSDN website:
http://msdn.microsoft.com/library/de...ce05022002.asp. It provides a lot more detail about the command bar than I could ever relay otherwise. Perhaps it will help you to sort this out. I have attached it here if you care to see it.

[RobDog888]

Yes, that was a good link. Seems like we need to make a few corrections, but were on the right track.
I think I may have to break down and install it for a faster solution. Be back in a few minutes with the results.

[RobDog888]

Update button automated!!!

Public Sub StockUpdate()
    
    Dim oToolBar As Office.CommandBar
    Dim oUpdateBtn As Office.CommandBarButton
    
    Set oToolBar = Application.CommandBars("MSN Money Stock Quotes")
    
    If TypeName(oToolBar) <> "Nothing" Then
        Set oUpdateBtn = oToolBar.FindControl(msoControlButton, oToolBar.Controls("Update Quotes").ID)
        oUpdateBtn.Execute
    Else
        'Toolbar not loaded
    End If
 
End Sub

Time taken to figure it out - 5 minutes. Priceless

[RobDog888]

Aww, quotes can only be updated once every five minutes.

[RobDog888]

Though there was something wrong. We were getting the Insert Quote button. I updated the code to get the
correct button. I even thought that if I forced the button to be enabled that we could update it before the 5 min
timeout. Its enabled but doesnt return a quote unless the 5 min timeout has passed

Public Sub StockUpdate()
    
    Dim oToolBar As Office.CommandBar
    Dim oUpdateBtn As Office.CommandBarButton
    
    Set oToolBar = Application.CommandBars("MSN Money Stock Quotes")
    
    If TypeName(oToolBar) <> "Nothing" Then
        Set oUpdateBtn = oToolBar.FindControl(msoControlButton, 1, "RefreshButton:MSN MoneyCentral Stock Quote Add-In", , True)
        oUpdateBtn.Enabled = True 'Enabled but if les then 5 mins. still wont return results :(
        oUpdateBtn.Execute
    Else
        'Toolbar not loaded
    End If
 
End Sub

[GSIV]

Indeed, it is now automating the commandbar; BUT, and I can't figue out why, it is running the insert stock quote function. As soon as I ran the code, the "insert" section of the toolbar highlighted and now it stays that way. I even uninstalled the add-in and reinstalled it. Same thing. If I click on "update" though, it works. How can this be? You told it, Update!!!!

[GSIV]

Crossed paths! LOL!

[RobDog888]

I think we can dismiss the popup ad form that popups after the quote is received. It would take a few APIs and a
timer to check for the form every 250ms or so. The FindWindow and SendMessage APIs are what
you need. I will post something in the morning. I'm tired and going to bed. Later.

[GSIV]

Yes, this seems to work perfectly. You sure put the time in on this one. Thanks for your patience! It's great to know that there are forums like this one to seek out real help. You're the best.

[RobDog888]

Thanks for the Rep and praise

Got to log off, ... cant do it ... must try harder ... "Click" ...................

[kilo94]

This is pretty nifty (found this on a search). Where do you put the code in a spreadsheet (yes, I'm a newbie)?

[RobDog888]

You can place it in a Module or in ThisWorkbook class.

[kilo94]

Thanks, works great.