Tutorials to .com

Tutorials to .com » Software » Vfp » VFP with Excel Interactive Programming

VFP with Excel Interactive Programming

Print View , by: iSee ,Total views: 42 ,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


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"


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


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



. 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


Where1 = Where1 + "" + condition + "" + cell

& & Here the value of condition check and or or


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


next for

if not found1

. ActiveSheet.Name = "search results 1"

& & Designation of the name of the form


. activesheet.name = "search results" + str (n +1,2)

& & Be the only form of work


SCommand = "SELECT * FROM d: \ vfp \ student grades WHERE" + ALLTrim (where1) +


& & The formation of VFP command string query

& Scommand & & implementation of VFP command string

_VFP.DataToClip ( "TEMP",, 3)

& & The 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 the search results




& & Shut down Excel, save the updated workbook file

Concluding remarks

VFP and Excel interactive capability is strong, the user can use the VFP database to deal with some operations, such as inserting, sorting, merging, selection of the results by a number of Excel in the treatment of late, and even some of the data collected conditions directly into Excel in the conditions of the region, from VFP to read the conditions of the screening data region. In short, as long as they can take full advantage of the benefits of their good understanding of the interface methods of interaction, it is necessary to enable the development of a more appropriate procedure to meet the needs of actual work.

Visual FoxPro Tutorial Articles

Can't Find What You're Looking For?

Rating: Not yet rated


No comments posted.