>I have several excel spreadsheets with numerous sheets. I want to take the sheet named 001 and copy everything to a table. I then want to go to the next spreadsheet and copy the info from sheet 001 to the same table as the previous. What is the best approach to accomplish this. I noticed that the IMPORT command names the table the same name as the spreadsheet. I want the data to go into a table I have designed.

---

Pamela,
Import from first spreadsheet if no table is ready or use your table. Then using automation SaveAs other XLS files as Excel5 format. Append from those saved as XL5. During SaveAs, move target sheet to first position to prevent some headaches.

-SaveAs Excel5 because "Append from .. type XL8" would crash if paste special or just cut&paste was used and cellcount > 350. It's a reported bug.
-Moving to first position and not using SheetName is preferred because it's another bug (don't know if reported). If you try to import into a database with a name clause "Sheet cSheetName" fail.

#define xlExcel5 39
#define XLSPath sys(5)+curdir()
* Assuming you have XLS files named myXLS01,...myXLS05
* in current directory
dimension mySheets[5,2]
for ix = 1 to 5
 mySheets[ix,1] = XLSPath+"myXLS"+padl(ix,2,"0")+".xls"
 mySheets[ix,1] = XLSPath+"myXLS5Format"+padl(ix,2,"0")+".xls"
endfor

oExcel = createobject("Excel.Application")
WITH oExcel
  for ix = 1 to alen(mySheets,1)
    .Workbooks.Open(mySheets[ix,1])
* Sheet "Sheet001" will be imported
    if .Sheets.Count > 1 and upper(.Sheets(1).Name) # "SHEET001"
       .Sheets("Sheet001").Move(.Sheets(1)) && Move to first sheet order
    endif
    .ActiveWorkbook.SaveAs(mySheets[ix,2],xlExcel5)
  endfor
  .quit
ENDWITH
release oExcel

use myTable
for ix = 1 to alen(mySheets,1)
  append from (mySheets[ix,2]) type xl5
endfor

Cetin