HMGforum.com

Exclusive forum for HMG, a Free / Open Source xBase WIN32/64 Bits / GUI Development System
http://mail.hmgforum.com/

Excel - error al importar

http://mail.hmgforum.com/viewtopic.php?t=7137

Page 1 of 2

Excel - error al importar

Posted: Tue Dec 14, 2021 4:13 pm
by Mario Mansilla
Hola Amigos :
les adjunto un pequeño proyecto para que puedan ayudarme a encontrar el problema que causa que a veces al importar la planilla excel me da los mensajes de error que muestra el archivo errorlog .
La planilla excel esta en la carpeta importacion , hacen click en la lupa y buscan la planilla y luego aceptar .

Desde ya muchas gracias
Saludos
Mario Rafael Mansilla

Hi friends :
I am attaching a small project so that you can help me find the problem that sometimes when importing the excel spreadsheet it gives me the error messages that the errorlog file shows.
The excel spreadsheet is in the import folder, click on the magnifying glass and look for the spreadsheet and then accept.

From already thank you very much
Greetings
Mario Rafael Mansilla

Re: Excel - error al importar

Posted: Tue Dec 14, 2021 6:19 pm
by SALINETAS24
Mario Mansilla wrote: ↑Tue Dec 14, 2021 4:13 pm Hola Amigos :
les adjunto un pequeño proyecto para que puedan ayudarme a encontrar el problema que causa que a veces al importar la planilla excel me da los mensajes de error que muestra el archivo errorlog .
La planilla excel esta en la carpeta importacion , hacen click en la lupa y buscan la planilla y luego aceptar .

Desde ya muchas gracias
Saludos
Mario Rafael Mansilla

Hi friends :
I am attaching a small project so that you can help me find the problem that sometimes when importing the excel spreadsheet it gives me the error messages that the errorlog file shows.
The excel spreadsheet is in the import folder, click on the magnifying glass and look for the spreadsheet and then accept.

From already thank you very much
Greetings
Mario Rafael Mansilla
Hola Mario, lo puedes solucionar haciendo una copia de los datos a una nueva Hoja de Calculo, solo tienes que copiar los datos, sin cabecera ni ordenaciones. Por lo visto el problema viene dado por tener la hoja de calcula la cabecera fija.

Con esto ya lo tienes.., salud y una cervecita fresquita.

Re: Excel - error al importar

Posted: Wed Dec 15, 2021 8:49 am
by serge_girard
I think there is an error in the sheet. Try to save it as CSV and then edit in your program editor.

Re: Excel - error al importar

Posted: Wed Dec 15, 2021 10:15 am
by SALINETAS24
serge_girard wrote: ↑Wed Dec 15, 2021 8:49 am I think there is an error in the sheet. Try to save it as CSV and then edit in your program editor.
Hola Serge, no creo que la hoja tenga errores, creo en mi humilde opinión que tiene por un lado algo de programación y tambien 3 páginas ocultas que tienen vinculación con la Excel principal..., :evil: todo unido al parecer dificulta la importación. Muestro la imagen.

Si por el contrario se copian los datos a una nueva hoja (cotar y pegar), el funcionamiento es correcto.

Igual alguien (y ahora que tenemos más datos), sabe como se prodria importar una hoja de estas caracteristicas sería estupendo.

Vamos con esa cervecita fresquita!!!

Re: Excel - error al importar

Posted: Wed Dec 15, 2021 11:24 am
by AUGE_OHR
hi,

this Code "crash" when want to "select" (in wrong Way)

Code: Select all

   oExcel:WorkBooks:Open( cArchivo )
   oExcel:Sheets(nHoja):Select() -> crash 
// "Error WINOLE/1009 No exported method SHEETS Parameter : [ 1] = N 1 (DOS Error -2147418111)
   oHoja := oExcel:ActiveSheet()
try it this Way

Code: Select all

   oBook  := oExcel:Workbooks:Open(cExcelFile)
   oSheet := oBook:ActiveSheet
   oRange := oSheet:usedRange
   nCol := oRange:Columns:Count() 
   nRow := oRange:Rows:Count()
now i have to ask : do you want to get hole Sheet :?:

if yes
use nRow /nCol (convert to A-Z) to "select Range" and copy/paste it to Array with "same Size"

Code: Select all

   aExcel := ARRAY(nRow,nCol)
   cEnde := ZAHL2CHR(nCol)
   // now "fill" Array from Sheet Range
   aExcel := oSheet:range( "A1:"+cEnde+LTRIM( STR(nRow) ) ):value

---

you also can "fill" a Excel Sheet this Way

Code: Select all

   oSheet:range( "A1:"+cEnde+LTRIM( STR( nRow ) ) ) :value := aExcel

Re: Excel - error al importar

Posted: Wed Dec 15, 2021 2:17 pm
by edk
@Serge, SALINETAS24, Jimmy
It's not exactly what you suggest.
it is just that the workbook has not yet been fully loaded into Excel and therefore does not yet have sheet objects.

See a simple test:

Code: Select all

...
nHoja := 1
oExcel:WorkBooks:Open( cArchivo )
Sleep ( 1000 )
oExcel:Sheets(nHoja):Select()
oHoja := oExcel:ActiveSheet()
...
@ Mario Mansilla
See this solution:

Code: Select all

...
nHoja := 1
oExcel:WorkBooks:Open( cArchivo )

// catch any errors
bErrBlck := ErrorBlock( { | oError | ExcelError( oError ) } )

lIsExcelFullyLoaded := .F.

DO WHILE .NOT. lIsExcelFullyLoaded
	BEGIN SEQUENCE
		nSheets := oExcel:Sheets:Count
		lIsExcelFullyLoaded := .T.
	RECOVER 
		lIsExcelFullyLoaded := .F.
	END
ENDDO

//Restore the previous ErrorBlock
ErrorBlock( bErrBlck )

oExcel:Sheets(nHoja):Select()
oHoja := oExcel:ActiveSheet()
...

...
**************************************************************
FUNCTION ExcelError( oError )
BREAK oError
RETURN Nil

Re: Excel - error al importar

Posted: Wed Dec 15, 2021 9:55 pm
by AUGE_OHR
hi Edward,

you are right that is is a Problem of "timeing"
thats why i do use "single Step" to get oBook, oSheet and oRange to avoid "sleep"

Re: Excel - error al importar

Posted: Wed Dec 15, 2021 11:17 pm
by edk
AUGE_OHR wrote: ↑Wed Dec 15, 2021 9:55 pm hi Edward,

you are right that is is a Problem of "timeing"
thats why i do use "single Step" to get oBook, oSheet and oRange to avoid "sleep"
+1
I agree, in addition, such a code is more transparent.

Re: Excel - error al importar

Posted: Thu Dec 16, 2021 1:39 pm
by mol
I'm using xlsxclass to read data from Excel in one of my application. It's fast and no Excel is needed

Re: Excel - error al importar

Posted: Fri Jul 14, 2023 1:16 am
by Rathinagiri
Man! You are God sent!
edk wrote: ↑Wed Dec 15, 2021 2:17 pm @Serge, SALINETAS24, Jimmy
It's not exactly what you suggest.
it is just that the workbook has not yet been fully loaded into Excel and therefore does not yet have sheet objects.

See a simple test:

Code: Select all

...
nHoja := 1
oExcel:WorkBooks:Open( cArchivo )
Sleep ( 1000 )
oExcel:Sheets(nHoja):Select()
oHoja := oExcel:ActiveSheet()
...
@ Mario Mansilla
See this solution:

Code: Select all

...
nHoja := 1
oExcel:WorkBooks:Open( cArchivo )

// catch any errors
bErrBlck := ErrorBlock( { | oError | ExcelError( oError ) } )

lIsExcelFullyLoaded := .F.

DO WHILE .NOT. lIsExcelFullyLoaded
	BEGIN SEQUENCE
		nSheets := oExcel:Sheets:Count
		lIsExcelFullyLoaded := .T.
	RECOVER 
		lIsExcelFullyLoaded := .F.
	END
ENDDO

//Restore the previous ErrorBlock
ErrorBlock( bErrBlck )

oExcel:Sheets(nHoja):Select()
oHoja := oExcel:ActiveSheet()
...

...
**************************************************************
FUNCTION ExcelError( oError )
BREAK oError
RETURN Nil
All times are UTC
Page 1 of 2

Powered by phpBB® Forum Software © phpBB Limited