Re: automating Metastock

[Thomas Pfluegl <thomas.pfluegl@xxxxxxxxxxxxxxxxx>]

Hi Ton, Walter et al,

Before I turn to the NumLock-Key problem I'd like to say that I've also
attempted to automate MetaStock with Excel-VBA.
For example a couple of months ago I wrote a sub which runs the first
exploration available in the MetaStock "Explorer Dialog" for a Specific Date.
	The difficult part is to trigger events when something happens, e.g. when
the window "Exploration Completed" appears, a VBA API call
(FindWindow("#32770")) assumes the exploration completed. You need a spy
program (e.g. Spy++, SPYXX.EXE) to find out the correct titles or numbers
of a specific window.
	The VBA routine waits until the exploration is done and adds one day to
the exploration date (Specific Date). If "Specific Date" is a Saturday or
Sunday it will add another day, so that no time will be wasted. You can run
the exploration even for a week (extra PC doesn't hurt) and the results
will be pasted into an Excel sheet.

	To prevent the list from the avalanche of those very interesting and
educational answers like "Yes I am interested", "I'd like a copy", etc., I
suggest that anybody interested should drop me a message privately if (s)he
wants the spreadsheet.
I will then put the spreadsheet on my website (download section) for a while.

>Re: your NumLock-Key problem ...
>Why is, at the end of one of my VBA macro's, the KB's 
>NumLock-Key automaticaly been switched OFF?
>(eg each time an excel macro is run, and it needs a "manual
>corrective" ON press later, after a macro is finished)

Usually the NumLock status is manipulated when using the XL "SendKeys" method.

The code to manipulate the keyboard status requires some API calls.
This code works in my software environment (Win98, MSOffice 97 Professional
Edition SR-2).
Put this from
''''''''''''''''''''''START'''''''''''''''''''''''''''''''''''''''''''''''''
''''''''' to
''''''''''''''''''''''''''''END'''''''''''''''''''''''''''''''''''''''''''''
''''''''''''' in a new module of your "Personal.xls" or as "AutoStart".
After the procedure which turns the numlock off, run the "NUMLock()"
procedure like this:

	Sub NUMLock_Switch_Tons_NUMLock_On()
	    Call NUMLock
	End Sub

With the macro Num_Lock_Check() you can check the status of the keyboard
and run other subs if desired.

''''''''''''''''''''''START'''''''''''''''''''''''''''''''''''''''''''''''''
'''''''''
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''''''''
Option Explicit
Dim RecordNumber2 As Integer
'   Declare constant for keys
     Const VK_CAPITAL As Integer = &H14
     Const VK_NUMLOCK = &H90
'   Declare Windows API functions
    Declare Sub GetKeyboardState Lib "USER32" (lpKeyState As Any)
    Declare Sub SetKeyboardState Lib "USER32" (lpKeyState As Any)

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''''''''

Property Get NUMLOCK_ON() As Boolean
    Dim lpbKeyState(128) As Integer
    GetKeyboardState lpbKeyState(0)
    If lpbKeyState(VK_NUMLOCK / 2) Then
         NUMLOCK_ON = True
            Else:  NUMLOCK_ON = False
    End If
End Property
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''''''''
Property Let NUMLOCK_ON(State As Boolean)
    Dim lpbKeyState(128) As Integer
    GetKeyboardState lpbKeyState(0)
    If State And 128 Then
        lpbKeyState(VK_NUMLOCK / 2) = 1
            Else: lpbKeyState(VK_NUMLOCK / 2) = 0
    End If
    SetKeyboardState lpbKeyState(0)
End Property

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''''''''
Sub NUMLock()
    Dim blnCapsState As Boolean
    ' use the  NUMLOCK_ON Property to GET the state of the capslock key
    blnCapsState = NUMLOCK_ON

    If blnCapsState = True Then
        Exit Sub
    Else
    ' use the NUMLOCK_ON Property to SET the state of the capslock key
     NUMLOCK_ON = Not blnCapsState

    blnCapsState = NUMLOCK_ON
    End If
End Sub

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''''''''
Sub NUMLock_Switch_Tons_NUMLock_On()
    Call NUMLock
End Sub

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''''''''
Sub Num_Lock_Check()
Dim iReturn As Integer
    iReturn = GetKeyState(VK_NUMLOCK)
    If iReturn = 1 Then
        MsgBox "Num Lock is on", vbInformation, "Hi Ton!"
    Else
        MsgBox "Num Lock is off", vbInformation, "Hi Ton!"
    End If
End Sub
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''''''''
''''''''''''''''''''''''''''END'''''''''''''''''''''''''''''''''''''''''''''
'''''''''''''

HTH & have a nice weekend,
Thomas Pfluegl 

>Date: Tue, 6 Jun 2000 22:44:43 -0400
>From: "Walter Lake" <	>
>Subject: Re: automating Metastock
>
>
>Hi Ton
>
>
>Thanks for your emails ... you've given me lots of stuff to track down and
>to download and learn.
>
>
>Will get back to you on your various suggestions.
>
>
>Re: your NumLock-Key problem ... Did you record your VBA macro or write it
>in the VBE (Alt + F11) as a module?
>
>
>I tried recording a series of actions switching it on and off and using the
>numeric keypad etc. .. nothing showed up in the code?
>
>
>When I stepped through the code using F8 the NumLock stayed on all the time
>and the code repeated all the actions and entered all the data accurately??
>
>Best regards as always
>Walter.
----------------------------------------------------------------------------
Thomas Pfluegl,  Rudersdorf 8,  A - 4212 Neumarkt
Austria               Tel.  ++ 43 - (0) 7941 - 8106
http://keplerweb.oeh.uni-linz.ac.at/trading/index.html
----------------------------------------------------------------------------
Austria/Europe --> high mountains --> Mozart --> no kangaroos
----------------------------------------------------------------------------