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 [EMAIL PROTECTED] with any queries.
**********************************************************************
_______________________________________________
Post Messages to: [email protected]
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/[EMAIL PROTECTED]
** All postings, unless explicitly stated otherwise, are the opinions of the
author, and do not constitute legal or medical advice. This statement is added
to the messages for those lawyers who are too stupid to see the obvious.
