I do it the other way and use vfp to 'push' data to Excel

If anyone's interested here is how I do it:

My main form has a dataObj with properties for the database connection and

the name of a view or table of de-normalised data (I'm using a backend

database but the same techniques would work for fox data).

An option on the main form (menu, button, click event, w.h.y.) calls a

modal form with a vfp field picker object on it, the user selects the

fields they want and click a button to create a spreadsheet from the data.

* **** de.beforeopentables() of fieldpicker form

* ** It took me a while to work out how to do this - the 'trick' is

* ** to realise that the calling form is still in scope here

(_screen.activeform).

LOCAL cSQL2 as String, cSt as string, nOK as Integer, nHndl as Integer,

parm as String

nHndl = _screen.ActiveForm.oData.nHndl && the connection handle

parm = _screen.ActiveForm.parm2send && the table / view name

cSQL2 = [select * from ]+ALLTRIM(parm)+[ order by 1]

nOK = SQLEXEC(nHndl, cSQL2, 'mycur')

SELECT mycur

**************************

* **** fieldpicker form button.click() code:

LOCAL cStr,cFile,i,aSelections

LOCAL oXL as Excel.Application

LOCAL oRange as Excel.Range

LOCAL oColumn as excel.Column

cStr = ""

DIMENSION aSelections[1]

aSelections=""

THIS.PARENT._FIELDMOVER1.getselections(@aSelections)

for i = 1 to ALEN(aSelections)

cStr = cStr+aSelections[m.i]+","

endfor

if len(cStr) > 0

cStr = left(cStr, len(cStr)-1) && remove last comma

ENDIF

THIS.PARENT.xrtnvar = cStr && xrtnvar is a property of my modal form class

for returning results

SELECT &cStr. FROM mycur INTO CURSOR my4xls

SELECT my4xls

IF RECCOUNT() > 0 THEN

cFile = PUTFILE('What name?',ALIAS(),'XLS')

COPY TO (cFile) TYPE XL5

* Open the Spreadsheet with nice column widths

WAIT WINDOW 'Creating Spreadsheet' TIMEOUT .5

oXL = CREATEOBJECT("excel.application")

DO WHILE NOT FILE(cFile)

INKEY(.5)

FLUSH

ENDDO

* oXL.displayalerts = .F.

oXL.workbooks.open (cFile)

oRange = oXL.Range([A1], oXL.ActiveCell.SpecialCells(11))

FOR EACH oColumn IN oRange.Columns

IF ISNULL(oColumn.NumberFormat) && future enhancement -

actually use afields values on source cursor for proper formatting

oColumn.NumberFormat = "Dd-Mm-yyyy"

ENDIF

oColumn.EntireColumn.AutoFit

ENDFOR

oXL.Visible = .t.

oRange = .NULL.

oXL = .NULL.

ELSE

WAIT WINDOW 'Unable to execute Query'

ENDIF

thisform.release

Andrew Davies  MBCS CITP

  - AndyD        8-)#

**********************************************************************

Manchester City Council supports Smokefree England - 1 July 2007

This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the system manager.

This footnote also confirms that this email message has been swept by MIMEsweeper for the presence of computer viruses.

Please contact internet.administrators@manchester.gov.uk with any queries.

**********************************************************************