Detecting Mouse Click from Excel in VFP 2

[AndrewMozley]

I have a VFP application which creates an Excel spreadsheet using CREATEOBJECT("Excel.Application") then populates it. It all works fine and looks pretty.

I would now like, in my VFP application, to detect a mouse click on the Excel spreadsheet. So that if I know which cell was clicked on, I could drill down into my VFP data and either populate Sheet 2 of the spreadsheet with the accounting transactions which made up the clicked-on cell, or possibly display a grid in my VFP application

If anyone has tried this sort of think I would much appreciate advice.

regards. Andrew Mozley

 

[Mike Lewis]

Andrew,

This is possible, but not easy.

Essentially, you need to create a VFP class with methods that map to the events in the COM object that you want to trap (in the case, the Click event). In the Click method of your VFP class, you write the code that responds to the Click in Excel.

You then use EVENTHANDLER() to bind the VFP class to the COM object.

For further details, see the Help on EVENTHANDLER() and on the IMPLEMENTS clause in DEFINE CLASS.

Hope this helps.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[white605]

This is possible, but not easy."

Mike, would another way to approach it be thru vba with xcel, putting a button on the spreadsheet to trigger an action
ie: a button on each line or column that passes the line or column number to act on. I know this doesnt trap the cell clicked but depending on the circumstance might be helpfull?
wjwjr

 

[Mike Lewis]

White,

You may be right. It depends on what exactly Andrew is trying to achieve. Possibley he doesn't need to trap the mouse click at all, but simply find out which cell is selected.


Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[AndrewMozley]

Thank you Mike L and wjwjr. Sorry to be so long in acknowledging. I have experimented and it is indeed possible to pick up a cell which has been clicked using the EVENTHANDLER() interface. As you pointed out, Mike, it is a pretty hefty interface which Microsoft offer you in VFP to pick up the events from Excel - you have to mimic all the events, even if you are not interested in them, which is pretty cumbersome.

In the matter of your suggestion wjwjr (thank you). If I don't particularly need to respond to ANY cell being clicked, you suggested I put an action in vba on a button in an Excel cell. How do I do that (must admit I have never used vba in Excel - have not tracked down a guide!). And can I write vba code to get this back to my VFP program, if that is what you are suggesting? Or is it back to writing an EVENTHANDLER() interface in vfp.

thanks again for your valuable time.

 

[Mike Lewis]

Andrew,

Can you explain what your end goal is? The reason I ask is that I'm wondering if you really need to detect a mouse click, or if your main aim is finding out which cell is currently selected.

If you could describe your goal, we might be able to suggest a simpler way of achieving it.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[AndrewMozley]

Mike

Sorry if I have been crptic. My VFP application has taken a template XL file (sample.xlt), prompted the user for a few details, like period number. I then copy that to sample.xls and populate the cells with values, e.g. cell B7 might be the name of account 123 (legal and professional) and cell B8 might be the total £765 posted to that account in period 9. That works fine. At present I make the Excel file visible and my VFP program exits back to its menu.

What I thought I might offer is that if the user clicked on (e.g.) cell B8, I would pick that up in my VFP program (which therefore would not have exited). It would know that the basis of that £765 was three transactions posted in period 9. It would then implement a drill-down facility to show (perhaps on sheet 2 of the spreadsheet) a table with these 3 transactions).

Following your guidance I see that I can do this (slightly heavyweight) in VFP8. It happens that my application is at present in VFP6 which does not offer EVENTHANDLER, although I may well convert it to VFP8 or 9. But if there is a simpler way of communicating between XL and my VFP8, I would like to know.

regards. Andrew M.

 

[Mike Lewis]

Andrew,

Your explanation makes things much clearer. I can see now why you do need to trap the click.

One approach might be to do the whole thing in Excel. You could perhaps create a toolbar button in the template file. The button would run VBA code. The code would use the ActiveCell property to determine which cell the user has selected, then perform the drill-down calculations and insert the result into some other part of the sheet.

If you prefer a VFP solution (and that would by my personal choice), then I think EVENTHANDLER() would be the best approach. You said earlier that you found it cumbersome to mimic all the events. Are you aware that you don't actually have to write very much of the code to do that, as the VFP object browser will generate it for you? (Open the Excel application object in the browser, drill down to Interfaces.Application, and drag that item into a code editing window.)

Hope this is of some help - or that someone else can suggest a better approach.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[white605]

Andrew,
You seem to be describing something similar to the drill down capability that is most frequently seen in Quickbooks software. I have limited vfp capabilities in SBT accounting similar to this but do not have the source code for it. What i can see working is that the SBT people use grids to start with instead of xcel to present the data to the user and then place a button on the grid and drill from there all in foxpro code. I like Mike's ideas best. Looking at the vba forum you can execute a sql connection from xcel back to vfp and get the data you want to present in another spreadsheet but it looks difficult to me.
wjwjr

 

[baltman]

Excel's VBA can call VFP dlls.

Brian

 

[AndrewMozley]

Thanks Brian and wjwjr

I am certainly interested in being able to write some vba code to access my vfp database.

Although I have written programs in Visual Basic, I do not know how to invoke Visual Basic from Excel. If you could point me towards a worked example or any documentation about how to do that, it would be really useful.

And if you have (in particular) executed an sql connection from Excel back to vfp that would be a great help. I'm also not sure about how the ability of Excel's VBA to call VFP dll's comes into this. Sorry about this ignorance, but as you see I do not know where to start, but would be grateful for any guidance.

Thanks very much. Andrew Mozley

 

[baltman]

Try this.

Brian

CLOSE ALL
CLEAR ALL
RELEASE ALL
SET DEFAULT TO JUSTPATH(SYS(16))

cDLL=(ADDBS(ADDBS(GETENV("SystemRoot"))+"system32")+"vfp_excel.dll")
IF FILE(cDLL)
 DECLARE integer DllUnregisterServer IN vfp_excel.dll
 DllUnregisterServer()
 CLEAR DLLS
 ERASE (cDLL)
ENDIF

SET TEXTMERGE TO vfp_excel.prg
SET TEXTMERGE ON NOSHOW
 \DEFINE CLASS vfp_excel AS Session OLEPUBLIC
 \PROCEDURE ExcelVal 
 \parameter MyParm1, MyParm2, MyParm3
 \ RETURN (MyParm1+MyParm2)/MyParm3
 \ ENDPROC
 \ENDDEFINE
SET TEXTMERGE TO

BUILD PROJECT vfp_excel FROM vfp_excel
BUILD DLL vfp_excel FROM vfp_excel
CLOSE ALL

COPY FILE vfp_excel.dll TO &cDLL
RUN /n REGSVR32.exe &cDLL

RETURN 
&&put below into an Excel macro
Sub dllTest()
    Set ws = ActiveSheet
    Set loDll = CreateObject("vfp_excel.vfp_excel")
    ws.Range("A1").Value = 900
    ws.Range("A2").Value = 330
    ws.Range("A3").Value = 20
    ws.Range("A4").Value = loDll.ExcelVal(ws.Range("A1").Value, ws.Range("A2").Value, ws.Range("A3").Value)
End Sub

 

[white605]

Brian,
The example works fine, a STAR
Can we put a vfp select statement and return multiple records to the worksheet where the "action" or the dll is
\ RETURN (MyParm1+MyParm2)/MyParm3

or should we execute vba code similar to:
<untested> from vba forum
Sub Connect_to_foxpro_DBf_and_execute_SQL_Query()
databaseName = "test"
queryString = _
"SELECT * FROM product.dbf WHERE (product.ON_ORDER<>0)"
chan = SQLOpen("DSN=" & databaseName)
SQLExecQuery chan, queryString
Set output = Worksheets("Sheet1").Range("A1")
SQLRetrieve chan, output, , , True
SQLClose chan
End Sub

:to get multiple records into the sheet?
wjwjr

 

[Mike Lewis]

Andrew,

If you still have your copy of my Stage 3 manual, you'll find an example there of how you can create a VFP DLL (an automation server) and call it from Excel. The DLL accepts a customer ID as a parameter and returns the customer's name.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[AndrewMozley]

Brian

Thanks very much for your time. I have run the first bit of code from VFP and it does indeed create a DLL. I also see that the dll is intended to be called from outside and that it returns a calculated value. In this case it is just a self-contained calculation from the input parameters, but I appreciate that it might involve further retrieval of data from the VFP application.

Is it intended that the way that I get my VFP application to re-populate cells on a spreadsheet with transaction details (which is what I want) is to define a macro, initiated by (e.g.) Ctrl-Shift-A? So that when the user presses Ctrl-Shift-A the dll retrieves the current cell from the spreadsheet, extracts the data from the VFP database (which is the bit that I CAN do in VFP!) and populates the cells?

I would need the dll to know about the cells on the Excel spreadsheet, so that it can determine which transactions need to be retrieved from the database. So I imagine that I would need to put into my dll the data of my original VFP application, so that, when the Excel user presses Ctrl-Shift-A, the DLL knows what it is required to do. Or is there a way for the DLL to pass the request back to my original VFP application to deal with?

Finally, I imagine that I will need to delete the vfp_excel (which is quite compact) at the end of the session.

In case there are details which would clutter this thread, my address is andrewtt1 (at) heckfieldcs (dot) co (dot) uk

 

[baltman]

I have no doubt that Mike would be more helpful than I for what you are trying to achieve. I primarily use Excel for reporting output.

I simply wanted to demonstrate that you could utilize VFP code while driving the application with Excel (VBA) but am in no way an expert in that area.

Good luck,
Brian