Recently I have a project which
requires a grid more powerful than the one comes with VFP. So I
begin to research on 3rd party ActiveX COM grid controls.
Eventually I purchased a copy of FarPoint Spread 7
(http://www.fpoint.com/products/spread/spread.aspx).
How I know of FarPoint Spread 7? A
while back while I was taking up the task of maintaining an existing
VB6 project, I found out the original programmer used an old version
of FarPoint Spread. My client doesn't have the source disk of the
FarPoint Spread any more, so I emailed FarPoint technical support and
asked for it. They sent me the installation files right away! I am
impressed with their technical support because that is a really old
version of the control.
Although it is not cheap, but to me it
certainly worth the price. I don't have any issue using it with VFP,
not a single crash. Having said that, FarPoint grid is not 100%
compatible with VFP. For example, any grid method with an out
parameter in its signature is not compatible with VFP. It is a known
issue (http://support.microsoft.com/kb/177575).
Fortunately there are workarounds so I don't see that as a
showstopper.
Their online documentation is also very
good. Although they have a trial version of the control, I found
that even if I don't download the trial, I can still evaluate the
control based on their online doc.
Well, I guess all COM control vendors
are moving to .NET now. So I don't expect there will be a next
version of FarPoint Spread COM control. But I still find this
control as of what it is now very useful to my present and future VFP
projects.
In short, I am a satisfied user of the
FarPoint Spread 7 control.
To continue the saga from my previous post (Excel Event Binding), the custom application I wrote for my client is now running happily with Excel 2003 after implementing those new event handlers to satisfy Excel 2003. So far so good. But that is not the end of the story.
New computers at my client site come with Office 2007. (I don't know where you can buy Office 2003 anymore.) Excel 2007 again has some new events that need to be handled:
WorkbookRowsetCompleted()
AfterCalculate()
After adding these event handlers, Excel 2007 OLE Automation has no problem.
However, my client application manipulates a lot of DBF files. (Please don't ask me to upgrade the DBF to some sort of SQL databases, as in my client's case it does not make sense -- trust me! :)
I found out some features of the app which automate Excel to save in dBASE III format do not work anymore, and after some googling, I became aware that Excel 2007 cannot save in DBF format any more.
Haha, it comes to a dead end.
On a side note, in the past, when I want to automate Excel, I just start up Excel and record a macro of the actions I like to do, and then view the auto-generated macro to retrofit the code into VFP. Sweat and easy.
I tried to do the same in Excel 2007, say inserting a picture into a doc, and guess what, the recorded macro is empty. Reading the help file it says navigations in the Ribbon Bar will not be recorded. (To tell you the truth, I am a bit shock when I read that :P) Unfortunately, I don't know how to insert a picture into a doc without using the Ribbon Bar.
So I have to record a macro in Excel 2002/2003 and then try the generated code with Excel 2007 to see if it works or not. Is there an easier way?
And of course, the idea of using Excel to repair a DBF I blogged about earlier on does not work any more...
Why things become so difficult in Excel 2007? Or is it just me? :)
Btw, I am using VFP9 SP2.
I do a fair amount of Excel Automation work for my client. This time I would like to use Excel as an external editor to compliment the VFP built in grid, i.e. instead of using the grid to edit data, the user can say click a button to start up Excel and use Excel to edit the data, when they close Excel after editing, somehow the edited data will be wrriten back to the grid. This way the user can use the multi-cell copy and paste feature of Excel among other useful features of Excel.
In order to do that, I need to trap the Excel closing event. That is doable with the EventHandler call. All I have to do is to find out which Excel interface to bind to by loading the Excel type library into VFP object browser. I picked AppEvents in Excel.Application and bind to the AppEvents_WorkbookbeforeClose event. That all works fine until I release it to Production. The EventHandler() call failed complaining an event handler is missing, namely AppEvents_WorkbookSync. I eventually found out that it is because of different version of Excel, I have v2002 in my development PC, and the production PCs have v2003.
Okay, I tried to copy the Excel.EXE from a production PC to my development PC and open it up in object browser to check how different the AppEvents interface is. When I try to ask VFP to create a skeleton class for me by dragging the AppEvents node in the object browser to a code window, it errors out with:
"Member VARTYPEINFO does not evaluate to an object."
If I click ignore, then another error:
"Variable "LCINTCODE" is not found.
No code is generated.
So eventually I have to manually code back all the missing event handler in order to make it work with Excel 2003. These functions do not have to do anything as long as they are defined to satisfy the binding call. These missing event handlers are:
AppEvents_WorkbookSunc()
AppEvents_WorkbookBeforeXMLImport()
AppEvents_WorkbookAfterXMLImport()
AppEvents_WorkbookBeforeXMLExport()
AppEvents_WorkbookBeforeXMLExport()
The exact definition of these functions can be found in MSDB website.
Perhaps I may have to to do all these over again when my client starts to use Office 2007...
Or perhaps there is a simply way to solve this problem...
The language C is behind a lot of fundamental technologies such as Linux, apache, device drivers, internet protocols, etc. The first version of BitTorrent is written in Python and Python is written in C. Ruby on Rails is written in Ruby and Ruby is written in C. Yet most of us is not concerned when will be the next major version upgrade of the Language C. Why?
What makes C so powerful is that we can write libraries (in C) to extend C. Similarly in the case of Perl, it takes a long time to go from Perl 5 to Perl 6, but that doesn't harm the usefulness of Perl 5. Because a vast number of extension modules are written to extend Perl 5 in Perl (and in C).
VFP is no exception. VFP is a mature product that innovation goes sideway. I really appreciate the VFP team to make extensibility and compatibilty the two major goals of VFP beyond version 9. Although closed source, VFP is open enough to be extended by VFP (xBASE) itself, by DLL written in any language, and by FLL written in C. VFP is very friendly with C and I guess only when C demises VFP will demise.
The other day I have some records from a DBF that need to be exported to an Excel file. I also need to add some list-based data validation in Excel via automation. I am using VFP9 and Excel 2002. Below is basically what I do.
export to c:\sheet.xls type xl5
loExcel = createobject('Excel.Application')
loExcel.Workbooks.Open("c:\sheet.xls")
loExcel.Columns("H").Validation.Add(xlValidateList, xlValidAlertStop, ...)
loExcel.Activeworkbook.Save()
loExcel.Quit()
I had verified the above code works in an interactive session in the command window. But when I open the generated spreadsheet, the data validation is gone!
Eventually I found out that VFP exports to Excel v5 format, which does not support Data Validation. However I can add the data validation in the spreadsheet because I am using Excel 2002. The Save() save in the existing format which is v5 which removes the data validation code without warning.
So I have to do a SaveAs() to save the generated spreadsheet in Excel 2002 format which keeps the data validation code:
loExcel.Activeworkbook.SaveAs('c:\sheet2.xls',xlDefault)
It would be better if there is a way for VFP to export DBF to a more update version of Excel without requiring an installed copy of Excel.
We can pass info to and retrieve info from a form like this:
DO FORM WITH arg1,arg2 TO result
which is similar to calling a function:
result = FORM(arg1,arg2)
When I am developing a form, I often need to change the number of input and output arguments, which means I need to constantly adjust the form calling interface.
By using a parameter object as an input/output mechanism simplifies it like below:
local loParam
loParam = createobject('empty')
addproperty(loParam,'input',"input_value")
addproperty(loParam,'output')
do form with loParam
? loParam.output
Also the use of parameter object makes it flexible in when and where to assign the output value, i.e. not restricted to RETURNing a single entity in the UNLOAD event of the form.
I have made a screencast showing that here at ShowMeDo.com.
Often times I need to break out from a loop if user hit the escape key. In order to do that, will have to:
- Save the existing state of the SET ESCAPE
- Set up ON ESCAPE
- Restore SET ESCAPE afterward
To those not familiar with VFP commands and how VFP does things, it may be difficult to figure this logic out. I have encapulated all this into a class to make it easier to understand what is happening.
loEscToAbort = createobject('syEscToAbort')
for i = 1 TO infinity
if loEscToAbort.IsAborted()
exit
endif
endfor
release loEscToAbort
The class is:
define class syEscToAbort as relation
hidden icOldEscape
hidden ilAbort
hidden ilEscapeHit
icOldEscape = ""
ilEscapeHit = sys(2015)
function init
this.icOldEscape = set("Escape")
set escape on
public (this.ilEscapeHit)
this.reset()
execscript([on escape ] + this.ilEscapeHit + [ = .t.])
endfunc
function destroy
release (this.ilEscapeHit)
lcOldEscape = this.icOldEscape
set escape &lcOldEscape
on escape
endfunc
function isAborted()
local llAborted
llAborted = evaluate(this.ilEscapeHit)
return llAborted
endfunc
function reset()
execscript(this.ilEscapeHit + [ = .f.])
endfunc
enddef
Note that:
- The class creates a global variable during its lifetime.
- The original ON ESCAPE command is not preserved.
I use INNO Setup to build the installer for my app. I like to show the version number (from the version resource in the compiled EXE) and the built date in the installer as show below:
Since INNO Setup is script based, that can easily be done as below:
But I don't want to manually update the INNO script every time I have a new build, so I automate it. I already have a make.prg which I run to build my project, all I have to do is to enhance it to update the version and timestamp automatically.
That boils down to the following steps:
1. using AGetFileVersion() and fdate() on the EXE to get the version number and the built date
2. check out the INNO script from SourceSafe
3. stuff it to the INNO script using filetostr(), strextract(), strtran() and strtofile()
4. check in the INNO script back to SourceSafe
3. call the INNO Setup Compiler to build the installer
So by issuing DO MAKE at the VFP command window, I will have the installer automatically built with the correct version and timestamp, and also have the latest INNO setup script in source control. The fact that both INNO Setup and SourceSafe have command line interface make things much easier, not to mention the readily available VFP built-in commands.
Attached is the make.prg in its entirety.
make.prg
I am always fascinated by the backward compatibility of VFP, and that really helps me to do my job with much less headache. I am a solo developer who not only have to build the app, but also have to support it. Being backward compatible make it possible for me to gradually "upgrade" my app at a pace that my client and I can afford. SET BEHAVOIR allows you to control the feature globally, where as individual commands such as REPORT FORM allows fine-grained control.
I "re-use" this idea in my app. Whenever I introduce some controversial changes to my client's app, I condition it with an ini entry, so that i can switch back and forth with or without the change easily. This gives me a peace of mind because if the new feature doesn't work or my client changes thier mind, I don't have to rush to my client's office to fix it. I can fall back instantly without any code change, recompile or testing. This buys me time.
In the code, rather than modifying the original source code to add the new feature, I clone the existing code first and then modify the clone to add the new feature, and then condition which portion of the code to run with the value of the ini entry. This way I know for sure the old code will work as before (because I haven't changed it) in the case I have to fall back to it. This reduces the amount of regression testing. Rolling it back to a previous version from source control will also work but I generally find that more troublesome to do in practice.
In order for this to work, the new feature will have to be designed properly so as to make it compatible with the old feature.
With this approach, both my client and myself are happier because I become less reluctant to accept change requests, and my client is welcomed to change their mind back and forth.
I have two different VFP apps running at a client site. One is a frontend desktop app that the
end-users use, and the other one is a backend maintenance app. The maintenance app runs at night to do a set
of system maintenance jobs. It has no
frontend and if there is anything needing attention, it will email the
technical support personnel for further investigation.
I developed these two apps separately, i.e. they belong to
two different projects. In the
beginning, the maintenance app only did a few things, such as search and delete
temp files, check diskspace, etc. Over
time, more and more functions are added to it, and it became more intimate with
the internal logic of the frontend desktop app.
Rather than duplicate code, I tried to share the common source
files between the two projects but the interdependence among those source files
force me to include more source files, and here and there need to add some
conditional logic to make them compile.
This smells ugly, and I now decide to bite the bullet to
integrate the maintenance app logic into the desktop app, so that all the
library routines in the frontend app are available to the backend app. This integrated app runs dual mode, i.e. as a
frontend app or a backend app, probably depends on a command line switch.
I now begin to see the ease of maintenance - one project to
maintain, and one branch in the source tree!
This will be the way to build future custom apps, at least for me that
is.
Another way to do it is to build a third project housing the
common routines, but it is kind of late at this stage because a great deal of
refactoring may be required in order to extract those common routines into a
separate project.
My app has to deal with a lot of DBF which sometimes get
corrupted. Upon checking, the corruption is most likely due to
some additional bytes after the logical end of the file. The
problem is VFP9 refuses to open it with error 2091:
Table "name" has become corrupted. The table will need to be repaired before using again.
It always puzzle me why VFP does not any built-in DBF repair command
(not even the simple one) although it has a native DBF engine.
Lately the frequency of corruption get to a point becomes
annoying. So I look around the Net to see if there is any DBF
repair utility which I can incorporate into my app to fix the
corruption automatically when it is detected.
There are some but it is not apparent that they support command line
execution or OLE Automation, and they are not cheap. My client
probably doesn't like absorbing the cost of it.
But my client has Excel installed in every workstation, and I noticed
that Excel can open the corrupted DBF okay without any problem.
So I wrote a routine to use Excel to repair the DBF. It simple
asks Excel to open the corrupted DBF and save it again. It
probably will not repair all kinds of corruption, but at least it works
in my case. Please see if it works for you as a DBF Repair
alternative.
*------------------------------------------------------------------------
* ut_RepairDBF()
*
* Call this routine to repair a DBF file reported by VFP to be corrupted.
*
* PASSES:
* tcFile = The full path to the DBF file to be repaired.
* e.g. 'c:\path\table.dbf'
*
* RETURNS:
* .t. if successful, .f. otherwise.
*
* OUTPUT:
* The original DBF is backup with .bak extension.
* The repaired DBF has the same name as the original file.
*
* EXAMPLE:
* ut_RepairDBF('c:\path\table.dbf')
*
* REMARKS:
* Microsoft Excel is used to repair the DBF.
*------------------------------------------------------------------------
function ut_RepairDBF(tcFile)
local loExcel as Excel.Application
local lcBackupFile
local lcFixedFile
if empty(tcFile) or not file(tcFile)
return .f.
endif
loExcel = createobject("Excel.Application")
if vartype(loExcel)<>'O'
return .f.
endif
lcBackupFile = alltrim(tcFile)+'.bak'
lcFixedFile = alltrim(tcFile)+'.tmp'
delete file (lcBackupFile)
delete file (lcFixedFile)
loExcel.Workbooks.Open(tcFile)
loExcel.ActiveWorkbook.SaveAs(lcFixedFile, xlDBF3)
loExcel.ActiveWindow.Close(.f.)
loExcel.Quit()
rename (tcFile) to (lcBackupFile)
rename (lcFixedFile) to (tcFile)
endfunc
I would like to do something (update a table) when a report is printed from the preview windows.
The old way of setting a variable in the report and checking it afterward does not work with an object-assisted report because sys(2040) is not designed to work with the new behavior of the new reporting mechanism of VFP9.
llPrinted = .f.
REPORT FORM xxx TO PRINTER PROMPT OBJECT TYPE 1
IF llPrinted
? "Printed"
ENDIF
and in the report set llPrinted to .t. if sys(2040)="2". llPrinted is always .f. even when the user prints the report.
After a few message exchanges with Borislav Borissov, Cathy Pountney, Colin Nicholls and Agnes Beste in UT, Agnes pointed me to the direction of using the OnPreviewClose event of the reportlistener to get the job done. I have packaged the technique into a generic base class clsReport where different reports can be subclassed from. The following code demonstrates the concept:
if not file('rptCustomers.frx')
select 0
use home(2)+'northwind\customers' shared noupdate again
create report rptCustomers.frx from alias()
use
endif
ox = createobject('clsCustomersReport')
ox.icReportName = 'rptCustomers'
ox.preview()
release ox
define class clsReport as session
icReportName = ""
function preview
local loListener as ReportListener
loListener = newobject('reportlistener')
loListener.ListenerType = 1
bindevent(loListener,'OnPreviewClose', this, 'OnPreviewClose')
report form (this.icReportName) object loListener
unbindevent(loListener,'OnPreviewClose', this, 'OnPreviewClose')
endfunc
function OnPrinted()
endfunc
function OnPreviewClose( tlPrinted )
if tlPrinted
raiseevent(this,'OnPrinted')
endif
endfunc
enddefine
define class clsCustomersReport as clsReport
function OnPrinted()
messagebox('Printed')
endfunc
enddefine
Please note that in this case the report is regarded as printed when it goes to the print spooler, even though it may not necessarily be physically printed (e.g. due to paper jam, user cancel print job, etc)
I use Data Explorer (DX) to browse a SQL Server 2000 database in a project. If I don’t select Sort Objects in the SQL Connection Properties dialog, then I get a list of all the SPROC in random order when I expand the stored procedure node.
If I select Sort Objects, then I get an empty list!
Northwind does not have this problem. Only my database has this problem. So I dig into XSource and narrow down the problem to this line of code in SQLDatabaseMgmt.OnGetStoredProcedures() defined in datamgmt_sql.prg
TEXT TO cSQL TEXTMERGE NOSHOW PRETEXT 7
SELECT * FROM
[<<THIS.DatabaseName>>].INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = 'PROCEDURE'
<<IIF(THIS.SortObjects, "ORDER BY [ROUTINE_NAME]", "")>>
ENDTEXT
After text merged, the sql becomes:
SELECT * FROM [Database].INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = 'PROCEDURE'
ORDER BY [ROUTINE_NAME]
which returns the following error:
Cannot sort a row of size 8295, which is greater than the allowable maximum of 8094.
When I change it to select a subset of the fields instead of all fields:
SELECT Routine_Name, Routine_Schema FROM [Database].INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = 'PROCEDURE'
ORDER BY [ROUTINE_NAME]
It works. So I change the code and recompile the DataExplorer to get it works (Method OnGetFunctions of the same class has the same issue as well).
It didn't work before for my database but works for Northwind may be because my database is originally created in Taiwan locale which makes its INFORMATION_SCHEMA.ROUTINES records having a row size > 8094.
Adel Gharib has submit a tip of the month in Advisor Guide to Visual FoxPro July 2006 issue to create a distributable command window which can be embedded into a compiled VFP exe. I have worked on Adel's tip and package the code into a class (commandprompt of sysadmin.vcx).
I use the ACCEPT command instead of the INPUTBOX command suggested by Ceil because it acts more like a command window that way. And a form is used as the input/output service.
To open the command prompt window, run the following code in the VFP command window or through a menu pad or in a mouse click event handler:
ox = newobject('commandprompt','sysadmin')
Here is a screenshot of it in action.
Click here to download the VCX and below is the source code of it:
DEFINE CLASS commandprompt AS form
Height = 250
Width = 467
ShowWindow = 2
DoCreate = .T.
AutoCenter = .T.
Caption = "Command Prompt"
FontName = "Courier New"
FontSize = 10
BackColor = RGB(255,255,255)
Name = "commandprompt"
*-- Start the command prompt.
PROCEDURE start
#define EXIT_TOKEN 'quit'
LOCAL lcPrompt, lcCmd
LOCAL loEx as Exception
this.Caption = "Type '" + EXIT_TOKEN + "' to finish"
this.Show
? version()
? os(1) + " " + os(7) + " " + sys(17) + " " + strtran(sys(0)," #","")
DO WHILE .t.
lcPrompt = SYS(5)+SYS(2003) + IIF(EMPTY(ALIAS()),""," (" + ALIAS() + ")") + " > "
accept lcPrompt to lcCmd
IF ALLTRIM(LOWER(lcCmd)) == EXIT_TOKEN
EXIT
ELSE
TRY
EXECSCRIPT(lcCmd)
CATCH TO loEx
MESSAGEBOX(loEx.Message,0, "Error " +tran(loEx.ErrorNo))
ENDTRY
ENDIF
ENDDO
ENDPROC
PROCEDURE Init
this.Show
this.start
return .f.
ENDPROC
ENDDEFINE
I haven't done any client/server work with VFP yet but plan to do so. I am thinking of should I use MSDE or MySQL.
MySQL now has GUI frontends similar to Enterprise Manager and Query Analyser.
MySQL Admin
http://www.mysql.com/products/tools/administrator/
MySQL Query Browser
http://www.mysql.com/products/tools/query-browser/
I haven't tried them yet but their screenshots look promising. With these tools, it would be easier to do ad-hoc maintenance job at the client site. This is a plus for MySQL. On the other hand, MSDE does not come with any GUI front end, so it will be more difficult to manage it at the client's site, which is a factor that I will consider which one to pick.