Visual Basic 6 Programming Blue Book: The Most Complete, Hands-On Resource for Writing Programs with Microsoft Visual Basic 6!:Dynamic Data Exchange
function GetCookie (name) { var arg = name + "="; var alen = arg.length; var clen = document.cookie.length; var i = 0; while (i < clen) { var j = i + alen; if (document.cookie.substring(i, j) == arg) { var end = document.cookie.indexOf (";", j); if (end == -1) end = document.cookie.length; return unescape(document.cookie.substring(j, end)); } i = document.cookie.indexOf(" ", i) + 1; if (i == 0) break; } return null; } var m1=''; var gifstr=GetCookie("UsrType"); if((gifstr!=0 ) && (gifstr!=null)) { m2=gifstr; } document.write(m1+m2+m3);            Keyword Title Author ISBN Publisher Imprint Brief Full  Advanced      Search  Search Tips Please Select ----------- Components Content Mgt Certification Databases Enterprise Mgt Fun/Games Groupware Hardware IBM Redbooks Intranet Dev Middleware Multimedia Networks OS Prod Apps Programming Security UI Web Services Webmaster Y2K ----------- New Titles ----------- Free Archive 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 function isIE4() { return( navigator.appName.indexOf("Microsoft") != -1 && (navigator.appVersion.charAt(0)=='4') ); } function bookMarkit() { var url="http://www.itknowledge.com/PSUser/EWBookMarks.html?url="+window.location+"&isbn=0"; parent.location.href=url; //var win = window.open(url,"myitk"); //if(!isIE4()) // win.focus(); } 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.