>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()
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])
if .Sheets.Count > 1 and upper(.Sheets(1).Name) # "SHEET001"
.Sheets("Sheet001").Move(.Sheets(1))
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