home account info subscribe login search My ITKnowledge FAQ/help site map contact us


 
Brief Full
 Advanced
      Search
 Search Tips
To access the contents, click the chapter and section titles.

Visual Basic 6 Programming Blue Book: The Most Complete, Hands-On Resource for Writing Programs with Microsoft Visual Basic 6!
(Publisher: The Coriolis Group)
Author(s): Peter G. Aitken
ISBN: 1576102815
Publication Date: 08/01/98

Bookmark It

Search this book:
 
Previous Table of Contents Next


A Front-End Demonstration

We now have the tools we need to create a Visual Basic front end. We know how to start another Windows program and send it commands and data. The demonstration program developed here is fundamental, but you’ll be surprised at how easily you can create even a full-featured front end. This program is designed for use with Excel, but the same techniques will apply to other applications as well. FRONTEND’s data-entry form is shown in Figure 9.3.


Figure 9.3  A Visual Basic program for data entry into an Excel spreadsheet.

Here are the tasks we want it to perform:

  Start Excel.
  Display three Text Box controls for data entry.
  Poke data in the three Text Boxes to columns A, B, and C in the Excel spreadsheet, starting in the first row.
  Poke subsequent entries into the same columns in row 2, row 3, and so on.
  Display an updating label on the form that informs the user of the current record number.
  On exit, prompt for a file-name entry and send commands to Excel to save the spreadsheet under that name; then terminate both itself and Excel.

The program’s single form contains three Text Boxes, four Labels, and two Command Buttons. The Text Boxes and Command Buttons are control arrays. Starting with this project, I will be using a new method to describe a form’s properties and controls. Rather than describe the steps to you, I will present a list of forms, controls, and properties in the form shown in Listing 9.3. This is, in fact, the same format that Visual Basic uses in a FRM file; you can use WordPad or any other text editor to open a FRM file to see what I mean. The format used is hierarchical, as follows:

Begin VB_Form FormName
    [form properties here in alphabetical order]
    Begin VB_CommandButton CommandButtonName
        [CommandButton properties here in alphabetical order]
    End
    Begin VB_TextBox TextBoxName
        [TextBox properties here in alphabetical order]
    End
End

An actual FRM file lists all of the properties, but I will include only those you need to explicitly change. For the FRONTEND demonstration program’s form, the objects and properties are shown in Listing 9.4. For convenient data entry, ensure that the tab order has the three Text Box controls in the 1, 2, and 3 positions, and the Next button in the 4 position.

Listing 9.4 Objects and properties in FRONTEND.FRM.

Begin VB.Form Form1
   Caption         =   “Data entry form”
   LinkTopic       =   “Form1”
   Begin VB.CommandButton Command1
      Caption          =   “Done”
      Index            =   1
      TabIndex         =   8
   End
   Begin VB.CommandButton Command1
      Caption          =   “Next”
      Index            =   0
      TabIndex         =   7
   End
   Begin VB.TextBox Text1
      Index            =   2
      TabIndex         =   6
   End
   Begin VB.TextBox Text1
      Index            =   1
      TabIndex         =   5
   End
   Begin VB.TextBox Text1
      Index            =   0
      TabIndex         =   4
   End
   Begin VB.Label Label4
      Alignment        =   1  ‘Right Justify
      Caption          =   “Cost each:”
   End
   Begin VB.Label Label3
      Alignment        =   1  ‘Right Justify
      Caption          =   “Quantity:”
   End
   Begin VB.Label Label2
      Alignment        =   1  ‘Right Justify
      Caption          =   “Item:”
   End
   Begin VB.Label Label1
   End
End

The program’s code is presented in Listing 9.5, and you can see that it is rather short. A flag named ExcelRunning is maintained: True if Excel has been started, and False otherwise. This flag is used to prevent the program from trying to send commands to Excel if a DDE link has not been established (which occurs when the user “saves” the first record). This would cause an error. The remainder of the code is straightforward and should be clearly understandable from the comments.

Listing 9.5 Code in FRONTEND.FRM.

Option Explicit

Const NONE = 0, MANUAL = 2
Dim ExcelRunning As Boolean

Private Sub Command1_Click(Index As Integer)
Dim Cmd As String
Dim Filename As String
Dim Prompt As String
Dim X As Long
Dim Item As String
Static Row As Integer

On Local Error GoTo Errorhandler

Select Case Index
    Case 0      ‘ Next
        If Not ExcelRunning Then
            ‘ Start Excel minimized without focus. Be sure to put
            ‘ the path to your copy of Excel here.
            X = Shell(“D:\MSOFFICE97\OFFICE\EXCEL.EXE”, vbMinimizedNoFocus)
            ExcelRunning = True
        End If
      
        ‘ Poke the data to the Excel spreadsheet. Loop once
        ‘ for each Text Box in the control array. Excel always
        ‘ starts with the default spreadsheet name SHEET1.
        Row = Row + 1

        For X = 0 To 2
            Text1(X).LinkMode = NONE
            Text1(X).LinkTopic = “EXCEL|SHEET1”
            Item = “R” & Right$(Str$(Row), Len(Str$(Row)) - 1)
            Item = Item & “C” & Right$(Str$(X + 1), Len(Str$(X + 1)) - 1)
            Text1(X).LinkItem = Item
            Text1(X).LinkMode = MANUAL
            Text1(X).LinkPoke
        Next X

        ‘ Clear the Text Boxes.
        For X = 0 To 2
            Text1(X).TEXT = “”
        Next X

        ‘ Set focus to the first Text Box.
        Text1(0).SetFocus

        ‘ Update the counter label.
        Label1.Caption = “Entering record” & Str$(Row + 1)
  
    Case 1      ‘ Done
  
        ‘ If Excel is running, save workbook and close.
        If ExcelRunning Then
            ‘ Get the name for the Excel file.
            Prompt = “Name for Excel file (no extension)”
            Filename = InputBox(Prompt, “File name”, “”)
            Filename = Filename & “.XLS”
      
            ‘ Save the spreadsheet. CHR$(34) is the double quote character.
            Cmd = “[SAVE.AS(” & Chr$(34) & Filename & Chr$(34) & “)]”
            Text1(2).LinkExecute Cmd
  
            ‘ Close Excel.
            Text1(2).LinkExecute “[Quit()]”
        End If
        End
End Select

Exit Sub

Errorhandler:
    Resume Next

End Sub

Private Sub Form_Load()

‘ Display record number.
Label1.Caption = “Entering record 1”

ExcelRunning = False

End Sub

Note that I have used the On Local Error statement: When an error is detected, an error-handling routine is executed that contains simply a Resume Next statement. This tells Visual Basic to ignore errors without reporting them (you will learn details of Visual Basic error handling in Chapter 25). This step was necessary because the program would sometimes report a “DDE time out” error, meaning that Excel had not responded to the DDE request within the allotted time limit. In fact, Excel was responding and everything was working fine—just a bit slowly. You can prevent this type of error by increasing the destination control’s LinkTimeOut property.

This demonstration program omits most of the error handling and other “idiot proofing” code that a program designed for distribution should include. Even so, it’s a good illustration of how Visual Basic and DDE can be teamed up with commercial applications to provide the best solution to a customer’s needs.


Previous Table of Contents Next


Products |  Contact Us |  About Us |  Privacy  |  Ad Info  |  Home

Use of this site is subject to certain Terms & Conditions, Copyright © 1996-2000 EarthWeb Inc.
All rights reserved. Reproduction whole or in part in any form or medium without express written permission of EarthWeb is prohibited. Read EarthWeb's privacy statement.