Ricardo,
Here is some code I use to bypass the 16k limitation. Hope this
helps.
*****************************************************************
**********************************************
*----------------------------------------------------------------
----------------------
* Program.....: ISEXCEL.PRG
* Version.....: 1.0
* Author......: Francis I. Coppage, Jr.
* Date........: May 4, 2001
* Notice......: Copyright (c) 2001, All Rights Reserved.
* Compiler....: Visual FoxPro 06.00.8961.00 for Windows
* Abstract....: Simply Opens a dbf into excel and saves it as an
active workbook,
* ............: thus bypassing the limitation of VFP's 16k
records.
* Changes.....: 21MAY01: Added autoformating.
* Notes.......:
*----------------------------------------------------------------
----------------------
#INCLUDE registry.h
LPARAMETERS tcInFile, tcOutFile, tnRows, tnColumns
IF !oApp.IsExcel THEN
RETURN .F.
ENDIF
LOCAL llAutoFormat, lnRows, lcColumns
llAutoFormat = .T.
IF VARTYPE(tnRows) != "N" THEN
llAutoFormat = .F.
ELSE
*$* FIC: Actual Rows in Excel will include the header
information
lnRows = tnRows + 1
ENDIF
IF VARTYPE(tnColumns) != "N" THEN
llAutoFormat = .F.
ELSE
*$* FIC: Ok, the Columns in Excel are letters, and if that
ain't
*$* bad enough, we have to see if there are more than 26
columns ... argh!
lcColumns = GetColumn( tnColumns )
ENDIF
*!* Open up EXCEL ... We'll do the rest of our work there
OleApp = CREATEOBJECT("Excel.Application")
tcInFile = SYS(5) + CURDIR() + FORCEEXT( tcInFile, "DBF" )
COPY TO (tcInFile) TYPE FOX2X
WITH OleApp
*!* dev stuff, So we could see it open
.Visible = .T.
.UserControl = .T.
*!* Loads our new worksheet into EXCEL
.Workbooks.Open( tcInFile )
.Range( "A1:" + lcColumns + ALLTRIM(STR(lnRows)) ).Select
.Selection.Columns.AutoFit
.Selection.AutoFilter
lcXFileName = tcOutFile
IF FILE( lcXFileName ) THEN
ERASE (lcXFileName)
ENDIF
*!* After all this work ... <g> ... we save it.
.ActiveWorkbook.SaveAs( lcXFileName )
.ActiveWorkbook.Saved = .T.
*!* Quit EXCEL
*.QUIT
ENDWITH
*!* Release the pointer from memory.
OleApp = NULL
ERASE (tcInFile)
RETURN
ENDPROC
*----------------------------------------------------------------
----------------------
* Function....: GetColumn
* Author......: Francis I. Coppage, Jr.
* Date........: 30 November 2001, Friday
* Called by...:
*
* Abstract....:
*
* Returns.....:
*
* Parameters..:
*
* Notes.......:
*----------------------------------------------------------------
----------------------
FUNCTION GetColumn( tnColumns )
LOCAL lcColumn, lcColumn2, lnBase, lnPower
DO CASE
CASE tnColumns <= 26
*$* FIC: 'A' = 65
lcColumn = CHR( 64 + tnColumns )
OTHERWISE
*$* FIC: We know that we are going to at least have an 'A'
prefix
*$* i.e. there are greater than 26 columns
lnBase = INT(tnColumns/26)
*$* FIC: The next 26 are the second character.
lnPower = MOD(tnColumns,26)
*$* FIC: Get them!
lcColumn = CHR( 64 + lnBase )
lcColumn2 = CHR( 64 + lnPower )
*$* FIC: Build them!
lcColumn = lcColumn + lcColumn2
ENDCASE
RETURN lcColumn
ENDFUNC
*****************************************************************
***************************************************************
Best,
Francis I. Coppage, Jr.
-----Original Message-----
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] Behalf Of Ricardo Araoz
Sent: Wednesday, August 15, 2007 6:39 PM
To: [EMAIL PROTECTED]
Subject: Excel question
Ok, got 135 ProFox (224 Python, 63 Python Tutor, have to stop
slacking)
mails to be read, so I hope this is not yet a subject.
My reports generate Excel files, if the report is too long (more
than
64000 lines) I use different pages in the same file. I have two
ways to
do it, either I export up to say 15000 lines with a copy to
command to a
temporary excel file, then open it (as an excel object), copy
it's
contents to the clipboard (through excel command) and then copy
the
clipboard to a new page in my multi page excel file. The pro of
this
method is speed, the con is that if the customer uses the
clipboard he
interferes with the report.
The other way is to insert the data cell by cell, but if the file
is too
large it gets slow.
Does any of you know of some other way to optimize this?
TIA
_______________________________________________
Post Messages to: ProFox@leafe.com
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.
_______________________________________________
Post Messages to: ProFox@leafe.com
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.
