set talk off
set safe off
set near off
set exact on
clea all
clos all
clea
public xtabla
MyFile = GETFILE("Excel *.xls:XLS,XLSX","Importar fichero excel:","Importar",0,"Seleccione Archivo a Importar XL5")
if empty(MyFile)
=messagebox('No ha seleccionado ningun Archivo')
return
endif
MyNewFile = MyFile
*MyNewFile = JUSTPATH(MyFile)+"\"+JUSTFNAME(MyFile)
if right(allt(MyNewFile),4)='XLSX'
MyNewFile = JUSTPATH(MyFile)+"\"+left(JUSTFNAME(MyFile),len(JUSTFNAME(MyFile))-1)
endif
oExcel = CreateObject("Excel.Application")
if vartype(oExcel) != "O"
return .F.
ELSE
oWorkbook = oExcel.Application.Workbooks.Open(MyFile)
if val(oExcel.Version) > 7
oWorkbook.SaveAs(MyNewFile, 39) && xlExcel5
else
oWorkbook.SaveAs(MyNewFile)
ENDIF
oExcel.quit() && Primer ejemplo Excel
ENDIF
oExcel = .Null. && Para evitar dejar abierta ejemplo excel ...
*************************************************************************
close table all
create table Rolo (real c(2), cambio c(10))
import from &MyNewFile type xl8
ytabla = alias()
go 1
dimension xcambia(fcount(),2)
for rolo=1 to fcount()
ycamp = field(rolo)
xcambia(rolo,1) = allt(ycamp)
xcambia(rolo,2) = allt(&ycamp)
xcambia(rolo,2) = left(chrtran(xcambia(rolo,2),' .-/',''),10)
insert into rolo (real,cambio) values (xcambia(rolo,1),xcambia(rolo,2))
endfor
set exact on
sele rolo
cambios='cambio'
recall all
index to borr on &cambios
go top
do while !eof()
zz=&cambios
skip 1
if &cambios=zz
dele
skip -1
dele
skip 1
endif
enddo
repla cambio with iif(len(allt(cambio))<=7,allt(cambio)+allt(str(recno())),left(allt(cambio),7)+allt(str(recno()))) for dele()
recall all
go top
do while !eof()
xreal = allt(rolo.real)
xcambio = allt(rolo.cambio)
if type(xcambio) # 'N'
alter table &ytabla rename column &xreal to &xcambio
endif
sele rolo
skip 1
enddo
use in rolo
erase rolo.dbf
sele &ytabla
go 1
dele
pack
clea all
clos all
erase temp.bak
erase borr.idx
set safe on
set talk on
set near on
clea all
clos all
return