Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Yousfi Benameur
  Where is Yousfi Benameur?
 El Bayadh
 Algeria
 Yousfi Benameur
 To: Prajeesh Nair
  Where is Prajeesh Nair?
 Chennai
 India
 Prajeesh Nair
 Tags
Subject: RE: Export to Excel
Thread ID: 189131 Message ID: 189231 # Views: 1 # Ratings: 0
Version: Visual FoxPro 6 Category: Databases, Tables and SQL Server
Date: Thursday, August 14, 2008 1:28:29 AM         
   


> Dear All
>
> I have table call customer.dbf which contains more than 2,00,00 recoreds. while tring to convert this table to Excel first 65,000 record only getting copied to Excel
>
> Copy to employee.xls type XLS
>
> Can you help me to convert all record into excel format.
>
> Can i convert to this into different pages of same work book?
>
> Is any example(code) available for this?
>
> Prajeesh

Hi Prajeesh
I give you a code for copying a big table of 500000 records for ex to Excel, assuming Excel capacity cannot over 65000 rows max (excel97-95...2003).
I create a cursor with data of 500000 records and split it into many excel files.
If you have a table open it and jump this etape.
The list of Excel filenames created is in clipboard at the end.
copy this code below and try it....adapt it for your situation(filenames,folders,..)
*Yousfi Benameur  Aug 13 2008
=ykill()       &&terminate any process of excel (blocked in windows task manager
**********
create cursor ycurs (num n(6.0) ,design c(50),observ c(40))
for i=1 to 500000    &&table of 500000 records for ex
appe blan
repl num with i, design with repli("A",50)
repl observ with repli("B",40)
endfor
brow
********if you have a table open it an begin here***************************
xr=i-1   &&reccount()
go top
xmax=65000
xdiv=ceiling(xr/xmax)            && theorically 65536
*messagebox(trans(xr)+"     "+trans(xdiv))
**********
t0=seconds()
cr=chr(13)
#define xlExcel9795  43       &&(&H2B) for excel 97-95 change constant is another excel version
set safe off
xdeb=1
mess="   List of excel files created :  "+cr
for i=1 to xdiv
aa="c:\asup"+trans(i)
copy to &aa for recno()>=xdeb and recno()<xdeb+xmax
xdeb=xdeb+xmax
***********
*convert any table with memo or not to excel
afile=aa     
af1=justfname(afile)
path=justpath(afile)

oxls="c:\asup"+trans(i)+".xls"
mess=mess+oxls+cr
SET BELL TO (ADDBS(GETENV('windir')))+"MEDIA\DING.WAV"
?? CHR(7)

wait window ("WAIT...Copying file  "+oxls+ "....operation underway !"+cr+"Table of "+trans(xr)+" records - "+trans(xdiv)+" Excel files") at srows()/2,scols()/2-20 nowait
************
LOCAL oExcel AS Excel.APPLICATION
  LOCAL oBook AS Excel.Workbook
  LOCAL oSheet AS OBJECT
  oExcel = CREATEOBJECT("Excel.Application")
  oBook = oExcel.Workbooks.ADD
  oSheet = oBook.Worksheets(1)
  
 *Create the QueryTable object.
  LOCAL oQryTable AS OBJECT, strData AS STRING
  strData = path                    
  xx= 'oSheet.QueryTables.ADD("OLEDB;Provider=VFPOLEDB.1;Data Source="+ strData+";", oSheet.RANGE("A1"),"Select * from '+af1+'")'
  oQryTable=evaluate(xx)
  oQryTable.RefreshStyle = 2 && xlInsertEntireRows = 2
  oQryTable.REFRESH(.F.)
  oBook.saveas(oxls,43)
  oExcel.quit
  endfor

=ykill()

&&cleaning
for i=1 to xdiv
aa="c:\asup"+trans(i)+".dbf"
dele file &aa
endfor
set safe on
tcalcul=trans((seconds()-t0)/60 )+" mn"

mess=mess+cr+cr+"List in the clipboard."+cr+"Time calculate="+tcalcul
messagebox(mess,0+32+4096)
_cliptext=mess
set bell off
clea all
*******************
function ykill()
LOCAL loWMI, lcQuery, loResult, loProcess
loWMI = GetObject("winmgmts://")
lcQuery = "select * from win32_process where name='excel.exe'"
loResult = loWMI.ExecQuery(m.lcQuery)
FOR EACH loProcess IN loResult
	loProcess.Terminate(0)
NEXT
endfunc


regards
Yousfi Benameur

ENTIRE THREAD

Export to Excel Posted by Prajeesh Nair @ 8/13/2008 1:51:33 PM
RE: Export to Excel Posted by Anders Altberg @ 8/13/2008 2:10:26 PM
RE: Export to Excel Posted by David Kurniawan @ 8/13/2008 2:24:54 PM
RE: Export to Excel Posted by Anders Altberg @ 8/13/2008 2:34:08 PM
RE: Export to Excel Posted by Anders Altberg @ 8/13/2008 2:15:35 PM
RE: Export to Excel Posted by Yousfi Benameur @ 8/14/2008 1:28:29 AM
RE: Export to Excel Posted by Debra McNeill @ 10/29/2008 9:56:45 PM
RE: Export to Excel Posted by Yousfi Benameur @ 10/30/2008 1:31:19 AM
RE: Export to Excel Posted by Anders Altberg @ 10/30/2008 9:01:34 AM