I need to put get data from VFP and put it into multiple sheets in an Excel file. I found this code on Internet. I have not tested it so far.
--------- Original link and post: ---------
https://social.msdn.microsoft.com/Forums/en-US/a9b81c52-2969-4403-81e2-b5ce891f1a2f/copy-to-excel-and-adding-worksheets?forum=visualfoxprogeneral
I have this function that we use to combine individual xls files:
************************************************************
* Function CombineExcelFiles
************************************************************
* Created...........: Craig Boyd 3/6/2006 23:55:50
*) Description.......:
* Calling Samples...: DIMENSION aXLSFiles(3)
*!* aXLSFiles(1) = "C:\temp1.xls"
*!* aXLSFiles(2) = "C:\temp2.xls"
*!* aXLSFiles(3) = "C:\temp3.xls"
*!* CombineExcelFiles(@aXLSFiles, "C:\XLSCombined.xls")
* Parameter List....:
* Major change list.:
function CombineExcelFiles (taXLSFiles, tcDestination, tlDeleteOriginal)
external array taXLSFiles
local loExcel as Excel.application, ;
loWorkBook as Excel.Worksbook, ;
loWorkSheet , ;
lnCounter, lcWorkSheetCaption, lcError, ;
lcValidChars
lcError = ""
try
lcValidChars = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789 "
loExcel = newobject("Excel.Application")
with loExcel
.ScreenUpdating = .f.
.DisplayAlerts = .f.
.WorkBooks.add()
lnCounter = 0
** Delete all existing worksheets except 1
for each loWorkSheet in .WorkBooks(1).WorkSheets
lnCounter = m.lnCounter + 1
if m.lnCounter > 1
loWorkSheet.delete
endif
endfor
for lnCounter = 1 to alen(taXLSFiles,1)
if file(taXLSFiles[m.lnCounter])
lcWorkSheetCaption = juststem(taXLSFiles[m.lnCounter])
loWorkBook = .WorkBooks.open(taXLSFiles[m.lnCounter])
loWorkBook.WorkSheets(1).copy(null, ;
.WorkBooks(1).WorkSheets(.WorkBooks(1).WorkSheets.count))
.WorkBooks(1).ActiveSheet.name = ;
right(alltrim(chrtran(m.lcWorkSheetCaption, ;
chrtran(m.lcWorkSheetCaption,m.lcValidChars,"")," ")), 31) &&loWorkBook.Name
loWorkBook.close(.f.) && Do not save changes
if m.tlDeleteOriginal
erase (taXLSFiles[m.lnCounter])
endif
endif
endfor
** Remove the first original sheet from (Sheet1)
.WorkBooks(1).WorkSheets(1).delete
.WorkBooks(1).saveas(m.tcDestination)
.ScreenUpdating = .t.
.DisplayAlerts = .t.
endwith
catch to loError
lcError = Log_Error(m.loError)
finally
if vartype(m.loExcel) = 'O'
with loExcel
.ScreenUpdating = .t.
.DisplayAlerts = .t.
.quit()
endwith
endif
endtry
return m.lcError
endfunc
Naomi Nosonovsky, Sr. Programmer-Analyst
