Automatizacion a Excel desde VFP
Enviado por Jorgeldp • 13 de Mayo de 2021 • Tutoriales • 3.367 Palabras (14 Páginas) • 784 Visitas
*************************
* AUTOMATION VFP CON EXCEL
***************************
SELECT TmpCuotas
Go Top
If Bof()
MessageBox("No Existen Cuotas de Ptmo.")
Return 0
EndIf
LOCAL cErrores, lExcel,vlLinea,vlGrupo,oExcel,vlTipoBono
Release oExcel
* BUSCO UNA SESION DE EXCEL YA ACTIVA:
cErrores = ON("ERROR")
ON ERROR lExcel = .F.
oExcel = CreateObject('Excel.Application')
ON ERROR &cErrores
IF !TYPE('oExcel')="O" && * NO ESTABA ACTIVA. PREPARO UNA NUEVA SESION DE EXCEL:
oExcel = CREATEOBJECT("Excel.Application")
ELSE
* Si existe el archivo y se esta utilizando lo cierro
FOR EACH x IN oExcel.Workbooks
NameFileTMP = x.Name
IF NameFileTMP = "CuotaPre.XLS"
x.CLOSE
ENDIF
ENDFOR
ENDIF
oExcel.VISIBLE = .t. && VISUALIZO EXCEL
oExcel.Workbooks.ADD && PREPARO UN NUEVO TRABAJO DE EXCEL
oExcel.sheets.Add
*oExcel.sheets.Add &&Si queremos agregar más hojas
vrNumHojas = oExcel.sheets.count() &&Cantidad de hojas en el libro
IF vrNumHojas > 1
oExcel.sheets(1).select
ELSE
oWorksheet1 = oExcel.Worksheets[1]
oExcel.Worksheets.Add(,oWorksheet1,1,)
ENDIF
vrNumHojas = oExcel.sheets.count()
IF vrNumHojas = 1
oWorksheet1 = oExcel.Worksheets[2]
oExcel.Worksheets.Add(,oWorksheet1,1,)
ENDIF
vrNuPag = 1
***************
oExcel.sheets(vrNuPag).select &&Seleccionamos la hoja 1
oExcel.ActiveSheet.name = "Cuotas de Ptmo" &&Ponemos título a la hoja
***************
WITH oExcel
.Cells(1,7) = [FECHA Y HORA: ]
.Cells(2,1) = [CAMPAÑA CREDITICIA]
.Cells(2+3,1) = [MONTO SOLICITADO]
.Cells(2+3,5) = [TOTAL INTERES]
.Cells(3+3,1) = [PLAZO]
.Cells(4+3,5) = [TOTAL GASTOS]
.Cells(4+3,1) = [TEA %]
.Cells(5+3,1) = [TEM %]
.Cells(5+3,5) = "T.G.Administ. S/ " + Allt(Str(Thisform.TxtGasAdm.Value,8,2))
.Cells(6+3,5) = "T.F.Desgrav. S/ " + Allt(Str(Thisform.TxtFonDes.Value,8,2))
.Cells(6+3,1) = [COMISION FLAT %]
.Cells(7+3,5) = [CUOTA MENSUAL]
.Cells(7+3,1) = [G. Administ. %]
.Cells(8+3,1) = [F. Desgrav. %]
.Range("A" + Allt(Str(2)) + ":" + "A" + Allt(Str(2))).Font.Bold = .T.
.Range("A" + Allt(Str(2)) + ":" + "A" + Allt(Str(2))).Font.Size = 14
.Range("A" + Allt(Str(2)) + ":" + "H" + Allt(Str(2))).HorizontalAlignment = -4108
.Range("A" + Allt(Str(2)) + ":" + "H" + Allt(Str(2))).VerticalAlignment = -4107
.Range("A" + Allt(Str(2)) + ":" + "H" + Allt(Str(2))).Merge
.Range("A" + Allt(Str(2+3)) + ":" + "A" + Allt(Str(6+3))).Font.Bold = .T.
*!* Combinar Celdas en Bloque
For wIndCel = 1 To 6
*!* .Range("A" + Allt(Str(wIndCel)) + ":" + "D" + Allt(Str(wIndCel))).HorizontalAlignment = -4108
.Range("A" + Allt(Str(wIndCel+3)) + ":" + "C" + Allt(Str(wIndCel+3))).VerticalAlignment = -4107
.Range("A" + Allt(Str(wIndCel+3)) + ":" + "C" + Allt(Str(wIndCel+3))).Merge
EndFor
.Range("D" + Allt(Str(2+3)) + ":" + "D" + Allt(Str(8+3))).HorizontalAlignment = -4108
.Range("D" + Allt(Str(2+3)) + ":" + "D" + Allt(Str(8+3))).VerticalAlignment = -4107
For wIndCel = 2 To 8
If InList(wIndCel,2,3,7,8)
.Range("E" + Allt(Str(wIndCel+3)) + ":" + "F" + Allt(Str(wIndCel+3))).HorizontalAlignment = -4108
.Range("E" + Allt(Str(wIndCel+3)) + ":" + "F" + Allt(Str(wIndCel+3))).VerticalAlignment = -4107
.Range("E" + Allt(Str(wIndCel+3)) + ":" + "F" + Allt(Str(wIndCel+3))).Merge
.Range("E" + Allt(Str(wIndCel+3)) + ":" + "F" + Allt(Str(wIndCel+3))).Font.Bold = .T.
If InList(wIndCel,2,7)
.Range("E" + Allt(Str(wIndCel+3)) + ":" + "F" + Allt(Str(wIndCel+3))).Interior.Color = RGB(0,128,192) && RGB(192,192,192) && Light Gray
.Range("E" + Allt(Str(wIndCel+3)) + ":" + "F" + Allt(Str(wIndCel+3))).Font.ThemeColor = 1 && Letra Blanca
Else
.Range("E" + Allt(Str(wIndCel+3)) + ":" + "F" + Allt(Str(wIndCel+3))).Font.Size = 14
...