VFP function that combines individual xls files

VFP function that combines individual xls files

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