>>********************************************************************** >>* Program....: VFP2EXCEL.PRG >>* Version....: >>* Author.....: Cetin Basoz >>********************************************************************** >>FUNCTION VFP2Excel >>#include foxpro.h >>LPARAMETERS tcCursorName, toSheet, tcFieldNames >>tcCursorName = IIF(EMPTY(m.tcCursorName),ALIAS(),m.tcCursorName) >>LOCAL loConn AS AdoDB.CONNECTION, loRS AS AdoDB.Recordset, ; >> lcTempRs, lcTemp, oExcel >>LOCAL lnFields AS INTEGER, ix AS INTEGER >>LOCAL ARRAY laFields[1] >>lcTemp = FORCEPATH(SYS(2015)+'.dbf',SYS(2023)) >>lcTempRs = FORCEPATH(SYS(2015)+'.rst',SYS(2023)) >>SELECT (m.tcCursorName) >>COPY TO (m.lcTemp) >>loConn = CREATEOBJECT("Adodb.connection") >>loConn.ConnectionString = "Provider=VFPOLEDB;Data Source="+SYS(2023) >>loConn.OPEN() >>loRS = loConn.Execute("select * from " + m.lcTemp) >>loRS.SAVE(m.lcTempRs) >>loRS.CLOSE >>loConn.CLOSE >>ERASE (m.lcTemp) >>loRS.OPEN(m.lcTempRs) >> >>WITH m.toSheet >> IF NOT EMPTY(m.tcFieldNames) >> >> lnFields = ALINES(laFields,m.tcFieldNames,.T.,"|") >> FOR ix = 1 TO m.lnFields >> .Cells(1,m.ix).VALUE = laFields[m.ix] >> NEXT >> ELSE >> FOR ix=1 TO loRS.FIELDS.COUNT >> .Cells(1,m.ix).VALUE = PROPER(loRS.FIELDS(m.ix-1).NAME) >> ENDFOR >> ENDIF >> >> .RANGE('A2').CopyFromRecordSet(loRS ) >> .UsedRange.ROWS(1).FONT.Bold = .T. >> .UsedRange.COLUMNS.AUTOFIT() >>ENDWITH >>loRS.CLOSE >>*loConn.CLOSE >>ENDFUNC>>
>>>LOCAL oExcel,lcCursorName >>> >>>Select CrsToExcel1 >>> >>>sheetNames1 = "Megye_eseti" >>> >>>oExcel = Createobject("Excel.Application") >>>With oExcel >>> .DisplayAlerts = .F. >>> .Workbooks.Add >>> .Visible = .T. >>> With .ActiveWorkBook >>> lcCursorName = 'crsToExcel1' >>> .WorkSheets(1).Name = sheetNames1 >>> VFP2Excel(m.lcCursorName, .WorkSheets(1),"A1" ) >>> .WorkSheets(1).Activate >>> Endwith >>>Endwith >>> >>>Function VFP2Excel >>> Lparameters tcCursorName, toSheet, tcTargetRange >>> tcCursorName = Iif(Empty(m.tcCursorName),Alias(),m.tcCursorName) >>> tcTargetRange = Iif(Empty(m.tcTargetRange),'A1',m.tcTargetRange) >>> Local loConn As AdoDB.Connection, loRS As AdoDB.Recordset,; >>> lcTempRs, lcTemp, oExcel >>> lcTemp = Forcepath(Sys(2015)+'.dbf',Sys(2023)) >>> lcTempRs = Forcepath(Sys(2015)+'.rst',Sys(2023)) >>> Select (m.tcCursorName) >>> Copy To (m.lcTemp) >>> loConn = Createobject("Adodb.connection") >>> loConn.ConnectionString = "Provider=VFPOLEDB;Data Source="+Sys(2023) >>> loConn.Open() >>> loRS = loConn.Execute("select * from "+m.lcTemp) >>> loRS.Save(m.lcTempRs) >>> loRS.Close >>> loConn.Close >>> Erase (m.lcTemp) >>> loRS.Open(m.lcTempRs) >>> >>> With toSheet >>> .QueryTables.Add( loRS, .Range(m.tcTargetRange)).Refresh() >>> Endwith >>> loRS.Close >>> Erase (m.lcTempRs) >>>