Writing event code to an Excel sheet

[AndrewMozley]

Not sure whether I should post this here or on the VBA forum.

My VFP application is creating and populating an Excel sheet; I let the user edit certain fields which I then read back - and use to update a database. I would like to know which rows of the sheet have been edited by the user

There is an event worksheet_change into which I can put some code to set a flag in column 1 when a cell in that row is changed.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Cells(Target.Row, 1) = -1
Application.EnableEvents = True
End Sub

If I am working in Excel I can code up this procedure. So what I need to know is how (within my VFP code which creates the Excel sheet) can I specify this code which is to be executed when the worksheet_change event fires. I can alter the properties of the Excel object from VFP but I do not know how to subclass its methods.

 

[Olaf Doschke]

There are ways to programmitacally create VBA makros, the point is, this will eventually trigger security warnings, that a program is trying to create makros. The better way is to automate excel to load a template XLT or XLTX file and have makros in there.

Then simply load the template via oExcel.Workbooks.Add("d:\templates\some.xlt(x)")

In the bigger picture it might also be easier to verify changes within vfp, comparing original and read back data. You should export primary keys of your data anyway and protect the cells containing them fom changes.

Bye, Olaf.

 

[Mike Lewis]

Andrew,

Another possibility would be to use VFP's EVENTHANDLER() function to bind the Excel's Worksheet_Change event to a custom VFP event. That way, you could execute whatever code you like when the user changes a cell.

I've not had much experience of this myself, but no doubt other folk here can give help you with the details if you decide to take this route.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Olaf Doschke]

That's a valid idea, Mike. But it strongly depends on vfp holding the excel reference not only while generating the excel sheet and file, but also while the user enters data or changes data exported.

If that is assumed, the question would be, why not to use a vfp form to let users edit data, much easier than going through execl.

So it's very likely the excel file is used by users not having the foxpro app, this is kind of an offline editing of data. Either such users only have excel or were used to it before a vfp application was added or the excel files are sent to them. Everything in that range is more probable than users using the vfp app, exporting to excel, being the same users modifying the excel files for later reimport.

Bye, Olaf.

 

[JRB-Bldr]

My VFP application is creating and populating an Excel sheet; I let the user edit certain fields which I then read back - and use to update a database. I would like to know which rows of the sheet have been edited by the user

Despite the 'security blanket' imagined by users who are familiar with using Excel, having the users make changes in an Excel file to be read back into your VFP system is NOT a good idea.

I have clients who have tried this and, while the VFP code can work as exactly intended, it has always created problems.

The cell entry into Excel is too free-form and allows users to make a whole host of entry errors into a worksheet.
The users can make typos, or enter the data into the wrong place, the cells can be formatted wrong, etc. which cause the VFP data retrieval to not work as expected.

If the data is going to reside primarily in VFP anyway, it would be far better to set up a VFP Form to support user input, etc.

From your VFP application it would be far better to use Excel as an Output Report option, but not rely on it in any way as an Input Tool.

Good Luck,
JRB-Bldr

 

[AndrewMozley]

Thanks for your thoughts, Olaf & jrb, which I take note of.

However the user finds that data entry in this case for multiple records is easier using an Excel sheet. I appreciate that fields need to be validated when updating VFP tables, so I am applying the same validation to each field as I do in the original VFP application.

(Olaf) In the matter of putting event handler code into an Excel sheet, I realise that I could copy a template spreadsheet as you suggest, and may do it that way - that is the way I have done it in the past.

I just hoped there might be an easy way of setting the code up from my VFP application (if you have gone down that path before, an example would be most helpful)

Again, thanks for your help and the time you have given to this.

 

[Olaf Doschke]

No, I have tried to go that path before and it's hurdles are not worth to come over, if there is an easier way. And a template can once and for all be verified and trusted, which is a difference to trusting an app writing any code makros into an excel workbook.

I would strongly consider creating a better usable vfp form for your needs. It's untrue you can't easily edit multiple rows in fox, we have the grid for one, and you can add much more intelligent browsing and navigation in records, selecting child records etc. in a vfp form than in a excel sheet.

Last not least, the option Mike suggests is possible in your case. You need a read on the IMPLEMENTS clause of the DEFINE CLASS command. That enables you to write classes in prgs, that implement a COM interface, of which excel has several including WorkbookEvents, which has the SheetChange Event.

The VFP class implementing that interface can then be coupled to the COM workbook object of the workbook you create via the EVENTHANDLER() function. This in short makes any event happening in the COM object trigger the method in your VFP object, so you can react to excel events with predefined foxpro code.

You find a detailed exaple on how to use IOMPLEMENTS and EVENTHANDLER here:

http://support.microsoft.com/kb/305471/en-us

This sample reacts to Microsoft (Help) Agent events rather than Excel, but the principle is the same.

Bye, Olaf.

Bye, Olaf.

 

[JRB-Bldr]

the user finds that data entry in this case for multiple records is easier using an Excel sheet.

That is the 'security blanket' that I was referring to.

And it is NOT a realistic, justifiable reason to compromise your data entry/processing.

You can create a Grid with empty records (or populated records with empty field 'cells') within a VFP Form which will make data entry equally easy for the user.

You need to 'educate' the user(s) that just because it seems 'easier' for them now, corrupted data WILL occur and its time to change methods.

Good Luck,
JRB-Bldr

 

[AndrewMozley]

Thanks for your note, jrb. We clearly disagree!

There will be no compromising of data processing. All input will be validated (as mentioned) exactly as in the rest of the VFP application, but thank you for your concern.