Chapter 8: Client Programming by Example: PixieExcel

PixieExcel  is an example of a visual front-end for a MV system, in this case from within MS Excel, when connecting to MV system via PixieWeb.  PixieExcel needs the supplied PICKBASIC program, "PX.BACKSRV" loaded compiled and catalogued in your PICK-etc Server. Ref Chapter 1

How do I know if this "backserver" is present?
Just try running PixieExcel, if no "backserver", PWExcel will give you that message.

Source Code and product status of PixieExcel
PixieExcel is sold as a single-user client product.  This almost identical version (PixieExcelW.xls) to work with PixieWeb is supplied as source code and comes with the same licensing arrangement as PixieWeb: ie server/site licensing with unlimited client installations for use with the nominated server.  So regard this PixieExcel as part of the bulk-buy bonus deal of the PixieWeb product.

To view the source code, select Menu:Tools --> Macros --> Visual Basic Editor

In the following commentary, each Step from the user guide is followed by notes on the relevant source code.

Step 1 - Connect to MV

  1. Open "PixieExcelW.xls"
    Howto:
    > *  Start Button:Programs --> PixieWeb --> PWExcel
    *  OR WIndows Explorer --> C:\Program Files\PixieWeb\PWExcel\PWExcel.xls
    *  OR MS Excel --> C:\Program Files\PixieWeb\PWExcel\PWExcel.xls
  2. Click on "PICKetc_QUERY" button from within spreadsheet as this starts the MV system connection process.

Code Commentary
Excel buttons on sheets can not make forms popup directly.  Instead they call "macros" which are BASIC routines in blocks of code called "modules" with no visible interface.  The macro for this button is therefore a minimal fragment of code to call up the form which then takes over.

Sub MV_Query_Show()
MV_Query.Show
End Sub

Step 2 - Login to MV system

  1. The following screen is displayed after clicking "PICKetc_QUERY" button.
  2. Enter UserName - PICKetc user-name.
  3. Enter Password - PICKetc password.
  4. Server, Port and Timeout are remembered from previous session. Changes required to these settings when you need to point Excel at different server or port. Timeout can be increased if system login response is slow.
  5. The "First Setup/Adv Config" button will be used by the Installer or Systems Administrator if unusual Login configurations need to be set-up prior to the first connection attempt. This procedure will usually only need to done once. 
    The image below shows what you see when "First Setup/Adv Config" is clicked.  The table sets out the steps needed to automate Login to PICKetc. 
    The Left-hand-fields show login steps, as you would enter them in a traditional terminal session. 
    The right-hand-fields contain text to identify successful expected responses.  This is usually a colon prompt or other text from near the end of the usual PICKetc response message. 
    {USERID} and {PASSWORD} are placeholders ie variables.
  6. Click Login to proceed. Click Cancel to exit back to .xls spreadsheet.

Code Commentary
The HELP file is an HTML file (this document minus the code commentaries) so the button needs to call up the default browser to display it.  A point here is that Excel spreadsheets can and should be moved to convenient folders, and PixieExcelW is free to copy and move like any other.  So we need a reliable fixed location for the Help files, and this is a subfolder branching off from where "PixieWMA.dll" is installed.  "PixieExcelW.xls" may move around but can always query "PixieWMA.dll" for the original path and reference its help from there: ...

'Advanced technique here: ShellExecute is a direct "Windows API call".
Private Declare Function ShellExecute Lib "shell32.dll" _
Alias "ShellExecuteA" _
(ByVal hwnd As Long, ByVal lpOperation As String, _
ByVal lpFile As String, ByVal lpParameters As String, _
ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long

....
Private Sub cmdHelp_Click()
Dim lSuccess As Long, sPath As String
'
'-- Shell to default browser
sPath = Pixie.ExecutePS("PATH") & "Help\PixieExcel.htm"
lSuccess = ShellExecute(0, "Open", sPath, 0&, 0&, 3&)
End Sub

Login/Logon procedures vary among PICK-etc setups, so simplifying this to a UserName/Password login is a challenge.  The approach taken here is a script, hidden by default, consisting of pairs of fields, each request/response pair representing a step in the original terminal login conversation. 

Sub cmdLogin_Click is the routine where this is applied

Pixie.TimeOut = txtTimeout
Pixie.Host = txtServer.Text
Pixie.Port = txtPort.Text
Pixie.ExecuteCompareMethod = 1
...
'Run scripting Loop
For i = 1 To 6 
    'Excel 97 is a bit primitive in not supporting arrays of entry fields
    'so the following Select block is a workaround for that
    Select Case i
    Case 1: sR = txtScript1: sE = txtExpected1
    Case 2: sR = txtScript2: sE = txtExpected2
    Case 3: sR = txtScript3: sE = txtExpected3
    Case 4: sR = txtScript4: sE = txtExpected4
    Case 5: sR = txtScript5: sE = txtExpected5
    Case 6: sR = txtScript6: sE = txtExpected6
    End Select
    '
    'If both fields are blank then we have finished in less than 6 steps 
    If sR = "" And sE = "" Then Exit For 
    '
    'Working with the placeholders ...
    sR = Pixie.SegmentReplace(sR, "{USERID}", txtUID.Text)
    sR = Pixie.SegmentReplace(sR, "{PASSWORD}", txtPWD.Text)
    Call Pixie.ExecuteET(sR, sE)
    If Pixie.State = "ERROR" Then
        Me.MousePointer = 0
        Call ErrorExit("Error with Login at step Script" & i)
        Exit Sub
    End If
Next i '

Step 3 - Select Target Data

  1. The following screen is displayed after a successful login.
  2. Accounts that the User are permitted to access are displayed in column 2a.
    Select (highlight) appropriate account and click "Next" (between columns 2a and 2b).
  3. Select appropriate file from displayed list. Click "Next" between columns 2b and 2c.
  4. Select appropriate query from displayed list. Only queries relating to the selected file will appear in column 2c. Options exist to: RUN a selected query, EDIT a selected query, create a NEW query, or exit back to spreadsheet.

 

Code Commentary
Queries consist of a SELECT (or SSELECT) statement paired with a LIST statement.  These are stored in the dictionary of the file of interest.  eg query "Contacts" takes the form of an item "px.qy*Contacts" in DICT CUST"

CT DICT CUST px.qy*Contacts 

    px.qy*Contacts
001 X
002
003 SELECT testpxe1,CUST,
004 LIST testpxe1,CUST, Name Contact Controller Email Tel Fax

The comma syntax for moving between accounts is a PICK syntax.  Sub DisplayAccounts identifies if such syntax is available and if the user has authorisation to navigate between accounts.
To populate the lstQuery, we use the PxRowset function:

'Get query list
'011101 JPC Ultimate-friendly syntax
Call Pixie.PxRowSet("DICT " & msFile, 1 _
, "SSELECT DICT " & msFile & " = ""px.qy*]""" _
, 0, mtTimeout)
'
lstQuery.AddItem "<default>"
For i = 1 To Pixie.SelectUBound(2)
    lstQuery.AddItem Mid$(Pixie.SelectResult(0, i), 7)
Next i
lstQuery.Enabled = True

The Pixie.Rowset function takes as arguments:

Pixie.SelectUbound(2) returns how many rows.
(Pixie.SelectUbound(1) gives the highest column number, which is one less than the number of columns)
Data is returned via Pixie.SelectResult(column_number, row_number)

 

Step 4 - Display Target Data

  1. Results of the selected query are displayed on the spreadsheet similar to the screen following. The data can now be used in any manner that is available to MS Excel. Please note that data from the spreadsheet cannot be written back to the source MV file with PixieExcel. Other PixieWare software products may be employed for this purpose.
  2. The query can be modified and run again.
  3. When running large queries, a line counter runs in the top-right-hand corner of the form.  If your query is taking too long (eg "Oops, I didn't mean to download that file with a million items!") then the "Run Query" button has turned into a "STOP" button for you to click.  We recommend you use SELECT ... WITH against large files to download a subset of interest.

Code Commentary
The easiest way to download data is via the Pixie.Rowset function, and an earlier version of PixieExcel did work this way.  But the "SelectResult" property needs to completely fill with the downloaded data before transfer to the visible display can start.  Users tended to want to download large spreadsheets of entire files rather than making selections as we expected them to, so the code for Private Sub RunQuery needed to go more low-level.  Program PX.BACKSRV feeds out large data orders in chunks of 20 items, so we can stream these through to the spreadsheet display 20 at a time and run a progress counter.  Conversation with PX.BACKSRV goes more direct low-level, via statements of the form: 
   Pixie.ExecuteTag( request, "<TXA>","</TXA>")

This takes place in a master "Do ... Loop" structure.
What we also build in is an "escape" so that users can click a "STOP" button to get out of this loop. 
Separate button events, eg Private Sub cmdGo_Click(), set variable mbQueryStop to "True".

' 
Do
    '011107 JPC STOP
    If mbQuerySTOP = True Then
        mbQuerySTOP = False
        sResponse = Pixie.ExecuteTag("QUIT", "", "")
        Me.MousePointer = 0
        If sResponse = "ROWSET EXIT" Then
            MsgBox "Query stopped OK" 
        Else
            MsgBox "ERROR. Server has failed to respond to STOP.  " _
            & "PixieExcel will now attempt automatic recovery, but if your " _
            & "next query does not work then you may need to login again."    
            Call Pixie.ExecuteTag("", "", "")
        End If 
        Exit Do 
    End If
    ...
    ...
    DoEvents
Loop

Note the "DoEvents" included in the loop. This is required to "rest" the loop to let other events proceed, especially to allow any STOP request to be processed, so mbQueryStop can change value.

Step 5 - Edit a Query

  1. Parameters for a selected query are displayed in the following example template screen.
  2. The query can be modified, run again and saved.  Variations on a theme can be saved under different names.
  3. Four parameters are available: Query Name, SELECT statement, LIST statement, ITEMLIST selection (via check boxes). The ITEMLIST is a graphical way of building and editing the LIST statement.
  4. The SELECT and LIST statements can be used as if being executed from TCL. .
    Note that sorting, selection and ordering using BY and WITH need to be handled in SELECT or SSELECT.
    The LIST statement is a plain list of required fields.
    This means for example that there is no SORT offered here.  Use SSELECT and LIST instead.
    eg SSELECT CUST BY Contacts / LIST CUST Name Contacts Fax Tel
  5. A new query starts with a blank template. The query is associated with the selected file and will be subsequently displayed when that file is again selected.
    The query is stored as an "X" item in the dictionary of the selected file.

Chapter 1    Chapter 2    Chapter 3    Chapter 4    Chapter 5    Chapter 6    Chapter 7    **Top**    Appendix   

Email: sales@pixieware.com