VFP with Excel Interactive Programming
Print View , by: iSee ,Total views: 37 ,Word Count: 3397 ,Date: Sat, 18 Apr 2009 Time: 11:09 AM
◆ Dr Yongming (djkhym@netease.com)
VFP (Visual Foxpro) is a relational database management system, because of its powerful data processing capability and good compatibility, making it a database application developers a powerful tool and widely used; and Excel is an excellent electronic forms processing software, the compatibility, user interface, the formula computing, graphics and other unique advantages have become the majority of office applications software of choice for essential personnel. These two software applications in their respective fields has been widely used, while the two software also has a good capacity of interactive programming for the two complement each other, learn from each other to lay a good foundation.
In this paper, combined with vfp and Excel examples of interactive programming methods, in addition to the use of VFP in OLE technology, may also be used with external DDE server technology for data exchange, this paper on the VFP in Excel using OLE technology and exchange of data, Excel in With built-in VBA to use the application object to provide VFP to call some of the functions of VFP. Its functions can be summarized as follows: VFP Data Sheet "student achievement. DBF" containing "study number, name, language, mathematics," such as field, examples of procedures from the Excel workbook "VFP interaction. XLS" work table "query "with" conditions "area (generally as a rectangular area of the data, the name of the region designated as" conditions ", data type such as" language> 60 "," Mathematics <90 ", etc.) of data as query conditions, the "connect condition" area (generally for a cell, its value is "or" or "and") to obtain the combination of "conditions" the logic of connections, and the contents of the connection information to the work of the form is displayed, and then VFP calls for a given table in the SQL query to identify the conditions given to the record and display in Excel. The following procedures are in VFP 6.0 and Excel 2000 debug through.
Excel driver VFP
Excel built-in VBA language (Visual Basic For Application) for Excel provides a function to facilitate the expansion of the means, the user can use the language of the completion of direct-drive VFP data retrieval functions. VFP program to generate a first object, then the implementation of VFP method DoCmd the VFP command string search, the search results through the use of VFP and then copy to the clipboard DataToClip methods, and finally paste VBA to the correct position of the worksheet, in order to per - run-time results can be inserted into the worksheet, followed by work on the operation table in order to "search results", "Search Results 1" ID, etc..
Sub exceluseFox ()
Dim oFox As Object 'object to a statement oFox
Dim SCommand As String
'SQL command string of variables corresponding
Dim cell As Variant
Dim choice As String
Dim join As String
Dim first As Boolean
Dim found As Boolean 'search results sign, if there are search results form, for real
Set oFox = CreateObject ( "VisualFoxPro
. Application ")
'Start VFP, generate VFP object
Sheets ( "query"). Select
'Select the worksheet correspond to "query"
join = Range ( "connect condition")
'In a single element in the table, the value of and or or
choice = "" 'home to connect the initial value is empty string
first = True 'under normal circumstances after the connection string to connect the logic of the need to add at and or or, the first exception
For Each cell In Range ( "conditions") 'conditions have to connect to form a sentence where the connection string logic
If first Then
choice = choice + cell 'formation where the first clause appears after the string
first = False
'Marks the first time, to amend the future need to connect the logic to connect with Fu
Else
choice = choice + "" + join + "" + cell
'Join the value is and or is or
End If
Next cell
Sheets.Add 'the work of a new form
'To find a sheet of non-repetition of
found = False
'Worksheet in the first four characters who have a "search results" a sign of the variable
n = 1
For Each cell In Worksheets
If InStr (1, cell.Name, "search results") <> 0 Then
found = True 'to find the corresponding worksheet
If n <Val (Mid (cell.Name + Space (2), 5, 2)) Then
n = Val (Mid (cell.Name + Space (2), 5, 2))
'To form a shape such as search results, search results such as the form of 2
End If
End If
Next cell
If Not found Then
ActiveSheet.Name = "search results"
Else
n = n + 1 'value by 1
ActiveSheet.Name = "search results" & n
'The formation of the work table
End If
SCommand = "SELECT * FROM d: \ vfp \ student grades WHERE" + choice + "INTO CURSOR TEMP" 'command string query VFP form
oFox.DoCmd Scommand 'implementation of VFP command string
oFox.DataToClip "temp",, 3
'Search results in order to copy text to clipboard
Range ( "a1: a1"). Select
'Point to the upper left corner of unit copies of the target region
ActiveSheet.Paste 'paste search results
End Sub
Excel in the region above the name (such as "conditions" and "connect condition" name) is the set in the Excel menu, select "Insert -> Name -> the definition of" to complete the name of a given regional setting, the name can access VBA, such as the aforementioned in the "terms" and "connect the conditions."
VFP using Excel
OLE (Object Linking and Embedding) object linking and embedding, is among Windows applications to share data transmission and an effective method. VFP through OLE share not only data from other applications, but also the way to target other applications to directly control the operation, thereby further expand the functions of VFP. VFP support directly in the process of creation, use and control of OLE objects, OLE automation to achieve. As an OLE client, VFP and Excel as an OLE server has a good programming interface, the following procedure using OLE way above the required functions. First of all, Excel program to generate a OleApp the OLE object to its operation, and then make use of OLE function to obtain a form from Excel For more information on the conditions and controls to generate a new Excel worksheet only by the current operation to find the expression of all the work only the name, VFP to use the query result is still the way clipboard transfer to Excel worksheet.
local condition, where1, first, scommand, cell, newsheet, found1, n
OleApp = CreateObject ( "Excel.Application")
& & Open Excel, have a OLE object
OleApp.Application.Caption = "VFP interactive programming"
& & Specified the name of the title bar
OleApp.Application.Visible =. T.
& & Purchase Excel visible
OleApp.Application.WorkBooks.Open ( "d: \ vfp \ VFP interaction. Xls")
& & Open the Excel workbook, the user can also modify the query or the connection conditions
where1 = ""
& & Save SQL variable in the where clause
first =. t.
& & Purchase the first time into the "Query" table work "conditions" of regional markers
found1 =. f.
n = 1
DO WHILE. T.
WITH OleApp.Application
nAnswer = MessageBox ( "the beginning of the search?", 32 +4, "Search the specified data")
& & Display search information
IF (. NOT. (NAnswer = 6))
& & If the press "Yes" button, then began to search, on the contrary from the
EXIT
ENDIF
. Sheets ( "query"). Select
& & To select the corresponding sample worksheet
condition =. range ( "connect condition"). value
& & Get "connect condition" to connect the logic of the region at
for each cell in. range ( "conditions"). value
& & Will form the region all of the data unit to form a mosaic where the logic of the connection string
If first Then
Where1 = Where1 + cell
& & The first time into the string when the where clause to obviate the need for logical connections at
first =. f. & & non-first time home mark
Else
Where1 = Where1 + "" + condition + "" + cell
& & Here the value of condition check and or or
EndIf
next for
. Sheets.Add & & the new one work form
& & For ... each of the following clause is used to find whether the corresponding worksheet, if 1 in the search results, search results Search Results 2 ... ... n be the largest value of n in order to generate a large 1 than n the new worksheet "search results & (n +1)"
for each newsheet in. worksheets
if "search results" $ newsheet.name
n = max (val (subset (newsheet.name + space (2), 9,2)), n) & & get the maximum value n
found1 =. t. & & home to find a job in the first four characters form a "search results" worksheet
endif
next for
if not found1
. ActiveSheet.Name = "search results 1"