Several interactive programming methods of VFP and Excel
First, EXECL drives VFP
EXECL's built-in VBA Language (Visual Basic for Application) provides a convenient means for Execl feature, and users can use this language to directly drive VFP completion of data search.
The program first became a VFP object, and then execute the VFP exploration command string with the VFP's DOCMD method, and its exploration result is copied to the clipboard with the VFP's DateToclip method, and finally VBA pastes it to the correct position of the worksheet.
SUB FOXTEST ()
DIM OFOX As Object
Dim Slesson As String
DIM Scommand as string
Set ofox = createObject ("VisualfoxPro.Application") 'Start VFP, generate VFP objects
Sheets ("inquiry" .select
Slesson = Range ("Course Name) 'gets the course name to query in a cell named" Course Name "
Sheets.add 'Generates a new work form
Activeesheet.name = Slesson 'The name of the working form is the same as the course name.
Scommand = "SELECT Learning, Language, Mathematical from D: / VFP / Student Selection Where" Slesson "<60 INTO CURSOR TEMP" Forming VFP Query Command Strings
Ofox.docmd scommand 'Execute a VFP command string
Ofox.DataToclip "Temp", 3 'Copy the search results to the clipboard
Range ("A1: A1"). SELECT 'Points to the upper left corner unit of the copy target area
Activeesheet.paste 'Paste search results
End Sub
For ease of use, the author has self-made a "search" toolbar and a button "Start Search" in Execl, and the above macro block is associated with the homemade button, and the program can be run according to this button. And the required data is obtained in Excel.
The method of making toolbars and buttons is as follows:
1. Select the "Tool" / "Custom" menu, the custom dialog box;
2. Select the Toolbar page box and press the "New" button;
3. Enter "Search" as the name of the new toolbar in the Toolbar dialog.
4. Select the "Command" page, select "Macro" in the Category list, select "Custom Button" in the Command list and drag and drop it to the newly created "Search" toolbar;
5. Press the "Update the selected content" button first, first fill in the button name "Start Search" in the "Name" field, then select "Specify Macro", select the above macro FoxTest in the "Specify Macro" list that appears. () You can specify a toolbar button for the macro.
Second, VFP uses OLE function to drive EXCL
OLE (Object Linking and Embedding) Object Links and embedding, is an effective way to deliver and share data between Windows applications. The VFP can simply share the data of other applications, but also directly control the operation of other applications, but also directly control the operation of other applications. The VFP support is created, used, and control OLE objects directly in the program to implement OLE automation. As an OLE customer VFP and the Excel as an OLE server have a good programming interface, the following block implements the desired function with the OLE mode.
The program first became an Excel's OLE object OLEAPP to operate, and then use the OLE function to get the course name from the Excel form, and control Excel generation new worksheet, the VFP query results still use the shear board. Pass to the Excel worksheet. OLEAPP = CREATEOBJECT ("Excel.Application" && Opens Excel to generate OLE objects
OLEAPP.Application.caption = "VFP Interaction Program" && Specifies Title Bar Name
OleApp.Application.visible = .t. && placed Excel visible
OLEAPP.Application.Workbooks.open ("D: / VFP / VFP Interaction .xls") && Opens Excel Workbook
Do while .t.
With oleapp.Application
NanSwer = MessageBox ("Start Search?", 32 4, "Search Specified Data") && Generating Information Box
If (.not. (Nanswer = 6)) && If the "YES" button, start search, revertion
Exit
ENDIF
.Sheets ("Query" .select && Select "Query" work form
Slesson = oleapp.Application.range ("course name" .Value && gets the course name to query
.Sheets.add && new work form
.Activeesheet.name = Slesson && specifies the name of the work form
Scommand = "SELECT Learn, Language, Mathematical from D: / VFP / Student Selection Where" Alltrim (Slesson) "<60 INTO CURSOR TEMP" && forming VFP query command string
& Scommand && executes VFP command strings
_VFP.DataToclip ("Temp", 3) && copies the search results to the clipboard
. Range ("A1: A1"). Select && points to copy the upper left corner unit of the target area
.Activeesheet.paste && paste search results
ENDWITH
Enddo
OleApp.quit && Close Excel, save the updated workbook file
Third, VFP uses DDE function to drive EXECL
DDE (Dynamic Data Exchange) Dynamic Data Exchange, is another effective way to deliver and share data between Windows applications, and DDE switches data between applications with shared memories. DDE session occurs between DDE customers and DDE server applications, request data and services to the server application to the server application, and the server responds to the request of the client application to data and services. DDE's data exchange can be divided into three ways:
* Cold link: When the client application requests data, the server application sends data to the client application;
* Warm link: The server application sends an advertisement to the client application when the value of each data item is changed, but it does not directly send values to the client application, and the client application determines whether the data is obtained;
* Hot link: The server application sends a new value of the data item to the client application when each value changes.
All VFPs and Excel support DDE clients and servers. The following program segment is implemented as a DDE customer's VFP application with the Excel as a DDE server. The program first starts Excel, and then forms "hot link" between the "Course Name" unit of the VFP application and the "Query" work form. When the "curriculum name" cell content changes, it will be automatically executed.
"GetData" process. The DDE data is first obtained during this process. If the data is "empty", turn off the Excel, the program ends; Shear plates send Excel to form a worksheet.
Public Excelchan
Public Sheetchan
Run / N3 C: / Program Files / Microsoft Office / Office / Excel.exe && Launches Excel with "Activity" and "Maximization]
Excelchan = Ddeinitiate ("Excel", 'System') && Initialization DDE channel Excelchan
= Ddeexecute (Excelchan, '[Open ("D: / VFP / VFP interaction .xls")]') && opens "VFP interaction .xls"
Sheetchan = Ddeinitiate ('Excel', 'Query') && Initialization DDE Channel Sheetchan
= Ddeadvise (Sheetchan, 'course name ",' getdata ', 2) && In the" Curriculum Name "cell of the VFP application and the" Query "work form! If the unit value changes, the "getData" process is executed.
Procedure getData
Parameters Channel, Action, Item, Data, Format, Advise
If an action = 'advise' .and. Item = 'course name' && server provided by the link named "Course Name"
Slesson = Data && directly gains data provided by DDE servers
Slesson = Left (Slesson, Len (Slesson) && Removes the format character in the raw data tail
IF (Len (Slesson) = 0) && If the content is specified as "empty", exit
= Ddetenate (Sheetchan) && termination DDE channel Sheetchan
= Ddeexecute (Excelchan, '[quit]') && exits Excel
= Ddetenate (Excelchan) && termination DDE channel Excelchan
Else
= Ddeexecute (Excelchan, '[Formula.goto ("Course Name)]') && Specify named" course name "
The cell is active cell
= Ddeexecute (Excelchan, '[Copy]') && to copy the specified cell content to the shearing board
SKEY = "'% i% ohr ^ v {enter}'" && keyboard command string, representation "insert (i) | Worksheet (W); Format (O) | Worksheet (H) | Rename (R) Paste "
= Ddeexecute (Excelchan, & SKey) && through the DDE channel to send an Excelwait Window Timeout 2 && waiting for 2 seconds to implement the above command string
Scommand = "SELECT Learn, Language, Mathematical from D: / VFP / Student Selection Where" Alltrim (Slesson) "<60 INTO CURSOR TEMP" && forming VFP query command string
& Scommand && executes VFP command strings
_VFP.DataToclip ("Temp", 3) && copies the search results to the clipboard
Sheetchan1 = Ddeinitiate ("Excel", '& Slesson') && initialization DDE channel Sheetchan1
Pointing newly generated worksheet
= Ddeexecute (Sheetchan1, '[Paste]') && Paste Search Results
= Ddetenate (Sheetchan1) && terminates DDE channel Sheetchan1
ENDIF
