COM Automation with Excel  
Author Message
thereisnomike





PostPosted: Visual FoxPro General, COM Automation with Excel Top

Here's my problem...I have the program writing to Excel and then opening just fine. What I want it to do though is save the spreadsheet in a newer version (I used "copy to 'filename.xls' type xl5") without having to actually see a prompt from Excel. The reason I'm doing this is for workbook consolodation (I'm writing to several workbooks and want to put them together). You can read more about it in my previous post here: http://www.hide-link.com/ ;SiteID=1


Visual FoxPro2  
 
 
CetinBasoz





PostPosted: Visual FoxPro General, COM Automation with Excel Top

Once again off the top off my head:)

with oExcel
.DisplayAlerts = .f.
*...
.ActiveWorkbook.SaveAs("fullpath of new file here", xlWorkbookNormal)
.ActiveWorkbook.Save
* .Quit
endwith


 
 
thereisnomike





PostPosted: Visual FoxPro General, COM Automation with Excel Top

thanks again - that worked....

However...I'm stuck yet again...it's starting to become funny...This is what I have so far:

oExcel = createobject([Excel.Application])
with oExcel
.SheetsInNewWorkbook = lnDivCount

.Workbooks.Add
for li = 1 to lnDivCount &&lnDivCount is the number of WorkBooks (separate files) I wish to copy over
lcFile = laFile(li)&&laFile is the array with the file names in it
.WorkBooks(lcFile).WorkSheets(1).UsedRange.Copy(.ActiveWorkbook.WorkSheets(li).Range("A1"))

*I tried this just to see what happened - it just copied what I had on my clipboard (whatever I had ctl+c'd earlier)
* .ActiveWorkbook.WorkSheets(li).Paste
endfor

.Visible = .T.
endwith

Unfortunately this doesn't do me much good. All it's really doing is creating a workbook with the correct number of sheets in it...but it's not pasting anything. I'm really tryin here...it's just not workin out too well for me. Is there any place that someone knows of where I can find a general help file or something on Automation with Foxpro for stuff like this I really hate to be taking up so much of your guys' time on the same stupid subject (although I'm extremely appreciative of all the help - especially you Cetin Basoz - thank you)



 
 
CetinBasoz





PostPosted: Visual FoxPro General, COM Automation with Excel Top

First where you go wrong. You need to open source workbook before copying.

oSource = .Workbooks.Open(m.lcFile)

.WorkBooks.Add

oSource.WorkSheets....Copy( ... )

Tomorrow I'll post a series of codes doing that in different ways (all were data transferring tests). As I remember the fastest 2 ways were:

1) Open parts of it (which would be different sheets in a single workbook) in different workbooks and visually tile their windows. In other words do not collect in a single wb.

2) Copy parts tab delimited. FileTostr() and place in clipboard. Paste onto target sheets in a single workbook.

All the other ways, though sounded more elegant codewise, were simply much more slower. There are faster ones in this group (like CopyFromRecordset) but still the fastest one of them were at least 30 times slower as I remember. IMHO excel is terrible copying cells from somewhere. It's slow and gets somewhat logarithmically slower as number of spreadsheets increase.

Anyway tomorrow if I can locate test codes I'll post them so you can test yourself and maybe find where I go wrong in those codes and discover a fast way. The one thing I haven't tried nor did find time to try is to create whole Excel file in code as XML. I expect it to be the fastest way ever but not sure (HTML might be an option too).


 
 
CetinBasoz





PostPosted: Visual FoxPro General, COM Automation with Excel Top

OK here are the samples as I promised. Keep in mind that they were 'test' codes. I'll post them 1 per message so it'd be easier to follow. Benchmark yourself.

Clear All
* Create a test cursor
Create Cursor testcursor (Rcno i,cType c(10), nType i, dType d)
Rand(-1)
For ix = 1 To 200000 && Create 200000 recs cursor
Insert Into testcursor Values ;
(Recco()+1,Sys(2015), Int(Rand()*1000), Date()-Int(Rand()*100))
Endfor

Set Sysformats On
Set Safety Off

lnTotal = Reccount()
Wait Window Nowait "Pls wait sending data to Excel..."
Start = Seconds()
oExcel = Createobject("Excel.application")
With oExcel
With .workbooks.Add && Add a new workbook - copy onto this one
lnMaxRows = .ActiveSheet.Rows.Count && Get max row count
lnNeededBooks = Ceiling( m.lnTotal / (m.lnMaxRows - 1) ) && 1 row header
"Total Books : "+Padr(m.lnNeededBooks,3," ")
If .Sheets.Count < m.lnNeededBooks
.sheets.Add(,.sheets(.Sheets.Count),;
m.lnNeededBooks - .Sheets.Count) && Add new sheets after old ones
Endif
Endwith
oTargetBook = .ActiveWorkBook
For ix = 1 To m.lnNeededBooks
lnStart = ( m.ix - 1 ) * (m.lnMaxRows-1) + 1
Copy To ("temp"+Padl(m.ix,2,"0")) ;
for Between(Recno(),m.lnStart,m.lnStart+m.lnMaxRows-2) ;
type Fox2x
.workbooks.Open(Sys(5)+Curdir()+"temp"+Padl(m.ix,2,"0"))
.ActiveWorkBook.ActiveSheet.UsedRange.Copy( oTargetBook.WorkSheets(m.ix).Range('A1') )
.ActiveWorkBook.Close(.F.)
"Book no : "+;
padl(ix,3," ")+;
"/"+;
padr(m.lnNeededBooks,3," ")+ ;
" Elapsed : ",Seconds() - m.start
Endfor
.Visible = .T.
Endwith
"Total Elapsed : ",Seconds() - Start


 
 
CetinBasoz





PostPosted: Visual FoxPro General, COM Automation with Excel Top

In this one note that you might skip 'VFP copy part' but just kept that to signify it's not VFP slowing this down.

lnTotal = 200000
* Create a test cursor
Create Table (ADDBS(SYS(2023))+'xltest') (Rcno i,cType c(10), nType i, dType d)
Rand(-1)
For ix = 1 To m.lnTotal && Create 200000 recs cursor
Insert Into xlTest Values ;
(Recco()+1,Sys(2015), Int(Rand()*1000), Date()-Int(Rand()*100))
ENDFOR
USE IN 'xlTest'

Start = Seconds()
lnMaxRows = 65536
lnNeededSheets = Ceiling( m.lnTotal / (m.lnMaxRows - 1) ) && 1 row header

Local lcConStr,lcPrefix,loRS As 'ADODB.RecordSet'
lcConStr = "Provider=VFPOLEDB;Data Source="+Sys(2023)
Local Array aRS[m.lnNeededSheets]
For ix = 1 To m.lnNeededSheets
lnStart = ( m.ix - 1 ) * (m.lnMaxRows-1) + 1
aRS[m.ix] = Forcepath(Textmerge('xlPart<<m.ix>>.rs'),Sys(2023))
SaveAsRS( m.lcConStr, aRS[m.ix], ;
TEXTMERGE('select * from xlTest'+;
' where RECNO() between <<m.lnStart>> and <<m.lnStart+m.lnMaxRows-2>>'))
Endfor
"VFP saved recordsets",Seconds()-m.start


loRS = Createobject("AdoDb.RecordSet")
oExcel = Createobject("Excel.Application")
With oExcel
.WorkBooks.Add
.Visible = .T.
With .ActiveWorkBook
If .Sheets.Count < m.lnNeededSheets
.sheets.Add(,.sheets(.Sheets.Count),;
m.lnNeededSheets - .Sheets.Count) && Add new sheets after old ones
Endif
For ix = 1 To m.lnNeededSheets
.WorkSheets(m.ix).Activate
loRS.Open( aRS[m.ix] )
.WorkSheets(m.ix).QueryTables.Add( loRS, ;
.WorkSheets(m.ix).Range('A1')).Refresh()
loRS.Close
Erase (aRS[m.ix])
"Excel sheet",m.ix,Seconds()-m.start
Endfor
Endwith
Endwith
"Total",m.ix,Seconds()-m.start


Function SaveAsRS(tcConnString, tcRSFileName, tcSQL)
Local loConn,loRS
loConn = Createobject("Adodb.connection")
loConn.ConnectionString = m.tcConnString
loConn.Open
loRS = loConn.Execute(m.tcSQL)
loRS.Save(m.tcRSFileName)
loRS.Close
loConn.Close
Endfunc


 
 
CetinBasoz





PostPosted: Visual FoxPro General, COM Automation with Excel Top

Similar to previous one.

lnTotal = 200000
* Create a test cursor
Create Table (ADDBS(SYS(2023))+'xltest') (Rcno i,cType c(10), nType i, dType d)
Rand(-1)
For ix = 1 To m.lnTotal && Create 200000 recs cursor
Insert Into xlTest Values ;
(Recco()+1,Sys(2015), Int(Rand()*1000), Date()-Int(Rand()*100))
ENDFOR
USE IN 'xlTest'

Start = Seconds()
lnMaxRows = 65536
lnNeededSheets = Ceiling( m.lnTotal / (m.lnMaxRows - 1) ) && 1 row header

Local lcConStr,lcPrefix,loRS As 'ADODB.RecordSet'
lcConStr = "Provider=VFPOLEDB;Data Source="+Sys(2023)
Local Array aRS[m.lnNeededSheets]
For ix = 1 To m.lnNeededSheets
lnStart = ( m.ix - 1 ) * (m.lnMaxRows-1) + 1
aRS[m.ix] = Forcepath(Textmerge('xlPart<<m.ix>>.rs'),Sys(2023))
SaveAsRS( m.lcConStr, aRS[m.ix], ;
TEXTMERGE('select * from xlTest'+;
' where RECNO() between <<m.lnStart>> and <<m.lnStart+m.lnMaxRows-2>>'))
Endfor
"VFP saved recordsets",Seconds()-m.start


loRS = Createobject("AdoDb.RecordSet")
oExcel = Createobject("Excel.Application")
With oExcel
.WorkBooks.Add
With .ActiveWorkBook
If .Sheets.Count < m.lnNeededSheets
.sheets.Add(,.sheets(.Sheets.Count),;
m.lnNeededSheets - .Sheets.Count) && Add new sheets after old ones
Endif
For ix = 1 To m.lnNeededSheets
.WorkSheets(m.ix).Activate
loRS.Open( aRS[m.ix] )
.WorkSheets(m.ix).Range('A1').CopyFromRecordSet( loRS )
loRS.Close
Erase (aRS[m.ix])
"Excel sheet",m.ix,Seconds()-m.start
Endfor
Endwith
.Visible = .T.
Endwith
"Total",m.ix,Seconds()-m.start


Function SaveAsRS(tcConnString, tcRSFileName, tcSQL)
Local loConn,loRS
loConn = Createobject("Adodb.connection")
loConn.ConnectionString = m.tcConnString
loConn.Open
loRS = loConn.Execute(m.tcSQL)
loRS.Save(m.tcRSFileName)
loRS.Close
loConn.Close
Endfunc


 
 
CetinBasoz





PostPosted: Visual FoxPro General, COM Automation with Excel Top

One of the fast ones:

clear
Clear All
* Create a test cursor
Create Cursor testcursor (Rcno i,cType c(10), nType i, dType d)
Rand(-1)
For ix = 1 To 200000 && Create 200000 recs cursor
Insert Into testcursor Values ;
(Recco()+1,Sys(2015), Int(Rand()*1000), Date()-Int(Rand()*100))
Endfor
lcHeader = Chrtran('Rcno,cType,nType,dType',',',Chr(9))+Chr(13)+Chr(10)

#Define xlDelimited 1
Set Sysformats On
Set Safety Off

lnTotal = Reccount()
locate
Wait Window Nowait "Pls wait sending data to Excel..."
Start = Seconds()
oExcel = Createobject("Excel.application")
With oExcel
.workbooks.Add && Add a new workbook
lnMaxRows = .ActiveWorkBook.ActiveSheet.Rows.Count && Get max row count
lnNeededSheets = Ceiling( lnTotal / (lnMaxRows - 1) ) && 1 row header
"Total Pages : "+Padr(lnNeededSheets,3," ")
.Visible = .T.
With .ActiveWorkBook
lnCurrentSheetCount = .sheets.Count
If lnNeededSheets > lnCurrentSheetCount
.sheets.Add(,.sheets(lnCurrentSheetCount),;
lnNeededSheets - lnCurrentSheetCount) && Add new sheets after old ones
Endif
For ix = 1 To m.lnNeededSheets
With .sheets.Item(ix)
.Name = "Page "+Padl(ix,3,"0")
.Activate
lcTempFile = Sys(2015)+".txt"
Copy Next m.lnMaxRows-1 To (m.lcTempFile) Delimited With Tab
_Cliptext = m.lcHeader + Filetostr(m.lcTempFile)
Erase (lcTempFile)
.Range("A1").PasteSpecial()
IF !EOF()
SKIP
endif
"Page no : "+;
padl(m.ix,3)+;
"/"+;
padr(m.lnNeededSheets,3)+ ;
" Elapsed : ",Seconds() - Start
Endwith
Endfor
.sheets.Item(1).Activate
Endwith
Endwith
"Total Elapsed : ",Seconds() - Start


 
 
CetinBasoz





PostPosted: Visual FoxPro General, COM Automation with Excel Top

Slow but not bad:

Clear
* Create a test cursor
Create Cursor testcursor (Rcno i,cType c(10), nType i, dType d)
Rand(-1)
For ix = 1 To 200000 && Create 200000 recs cursor
Insert Into testcursor Values ;
(Recco()+1,Sys(2015), Int(Rand()*1000), Date()-Int(Rand()*100))
Endfor
Locate

Start = Seconds()
lnRowsPerCopy = Floor(65000/Fcount())
Dimension aHeader[1,FCOUNT()]
For ix = 1 To Fcount()
aHeader[1,m.ix] = Proper(Field(m.ix))
Endfor

Wait Window Nowait "Pls wait sending data to Excel..."
oExcel = Createobject("Excel.application")
With oExcel
.workbooks.Add && Add a new workbook
.Visible = .T.
lnMaxRows = .ActiveWorkBook.ActiveSheet.Rows.Count && Get max row count
lnNeededSheets = Ceiling( Reccount() / (m.lnMaxRows - 1) ) && 1 row header

"Total Pages : "+Padr(lnNeededSheets,3)
With .ActiveWorkBook
If m.lnNeededSheets > .sheets.Count
.sheets.Add(,.sheets(.sheets.Count),;
m.lnNeededSheets - .sheets.Count) && Add new sheets after old ones
Endif
For ix =1 To m.lnNeededSheets
.WorkSheets(m.ix).Name = "Page "+Padl(m.ix,3,"0")
Endfor
For ix=1 To m.lnNeededSheets
With .WorkSheets(m.ix)
.Activate
.Range(.cells(1,1), .cells(1,Fcount())).Value = GetArrayRef('aHeader')
Endwith

For jx = 1 To Ceiling((m.lnMaxRows - 1) / m.lnRowsPerCopy)
lnRowStart = Recno() - (m.ix - 1) * (m.lnMaxRows - 1) + 1
lnRowEnd = Min(m.lnRowStart + m.lnRowsPerCopy-1, m.lnMaxRows)

Dimension aTrans[m.lnRowEnd - m.lnRowStart + 1,FCOUNT()]
Copy Next (m.lnRowEnd - m.lnRowStart + 1) To Array aTrans
lnCopied = _Tally
Dimension aTrans[m.lnCopied,FCOUNT()]
With .WorkSheets(m.ix)
.Range(.cells(m.lnRowStart,1), .cells(m.lnRowStart + m.lnCopied-1,Fcount())).Value = GetArrayRef('aTrans')
Endwith

If !Eof()
Skip
Else
Exit
Endif
Endfor
"Page no : "+;
padl(m.ix,3)+;
"/"+;
padr(m.lnNeededSheets,3)+ ;
" Elapsed : ",Seconds() - Start
Endfor
Endwith
.sheets.Item(1).Activate
Endwith
"Total Elapsed : ",Seconds() - Start

Function GetArrayRef(tcArrayName)


 
 
CetinBasoz





PostPosted: Visual FoxPro General, COM Automation with Excel Top

One of the fastest (in multiple workbooks, visually arranged):

Clear all
* Create a test cursor
Create cursor testcursor (Rcno i,cType c(10), nType i, dType d)
Rand(-1)
For ix = 1 to 200000 && Create 200000 recs cursor
Insert into testcursor values ;
(recco()+1,sys(2015), int(rand()*1000), date()-int(rand()*100))
Endfor

Set sysformats on
Set safety off

lnTotal = reccount()
Wait window nowait "Pls wait sending data to Excel..."
Start = seconds()
oExcel = createobject("Excel.application")
#Define xlNormal -4143
With oExcel
.workbooks.add && Add a new workbook
lnMaxRows = .ActiveWorkBook.ActiveSheet.Rows.Count && Get max row count
.ActiveWorkBook.Close(.f.) && Close no save
lnNeededBooks = ceiling( lnTotal / (lnMaxRows - 1) ) && 1 row header
"Total Books : "+padr(lnNeededBooks,3," ")
For ix = lnNeededBooks to 1 step - 1
&& Do a reverse pass so we wouldn't need an extra command to arrange windows
lnStart = ( ix - 1 ) * (lnMaxRows-1) + 1
Copy to ("temp"+padl(ix,2,"0")) ;
for between(recno(),lnStart,lnStart+lnMaxRows-2) ;
type fox2x
.workbooks.Open(sys(5)+curdir()+"temp"+padl(ix,2,"0"))
.ActiveWindow.WindowState = xlNormal
"Book no : "+;
padl(ix,3," ")+;
"/"+;
padr(lnNeededBooks,3," ")+ ;
" Elapsed : ",seconds() - start
Endfor
#Define xlTiled 1
.Windows.Arrange(xlTiled)
.visible = .t.
Endwith
"Total Elapsed : ",seconds() - start


 
 
CetinBasoz





PostPosted: Visual FoxPro General, COM Automation with Excel Top

You'd see why I said terrible:) 4th page has few data left to copy. 3rd is interesting. Timing of previous pages is almost summed up just to create that one. One day I hope I'll test XML version and possibly same thing from .Net.