> >Do IMPCLEANFIX > > >*!* *** CLEANFIX ***************************************************************************** >Procedure IMPCLEANFIX >Local mntotal as INT > FECHA([TODOS]) > FECHA([INV]) > FECHA([NotFind]) > FECHA([Crsexiste]) > > > > *!* CURSOR com todos os registos > Create Cursor TODOS (ref c(18), refExcel c(18), Design c(80), newpc N(14,3) , atualiza l, MargRed N(1,0), Arred N(1,0) , DescForn N(4,2), MARGBVND N(4,2), CB c(40) ) > > *!* Cursor das REF onde vai ser efetuada a atualização de Preços > Create Cursor INV ( ref c(18), Design c(60), u_pcustofo N(10,4) , newpc N(10,4), MargRed N(1,0), Arred N(1,0) , DescForn N(4,2), MARGBVND N(4,2), CB c(40) ) > > *!* vai guardar as REf que não encontra > Create Cursor NotFind ( ref c(18), refExcel c(18), Design c(60), newpc N(10,4), MargRed N(1,0), Arred N(1,0) , DescForn N(4,2), MARGBVND N(4,2)) > > > > *------- Data Connectivity ---------------------------- > Local lcXLBook As String, lnSQLHand As Integer, ; > lcSQLCmd As String, lnSuccess As Integer, ; > lcConnstr As String, lcfls As String > Clear > > lcXLBook = Getfile("xlsx") > > IF LIKE(UPPER('*CLEANFIX*'), Juststem(lcXLBook) ) or LIKE(LOWER('*cleanfix*'), Juststem(lcXLBook) ) > Else > msg("O nome do ficheiro tem de conter CLEANFIX") > Return > Endif > > lcConnstr = [Driver=] + ; > [{Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};] + ; > [DBQ=] + lcXLBook > > If !File( lcXLBook ) > ? [Excel file not found] > Return .F. > Endif > > *-- Teste de connexão ao Excel - Folha1$ > lnSQLHand = Sqlstringconnect( lcConnstr ) > > *-- Connect successful if we are here. Extract data... > lcSQLCmd = [Select * FROM "Folha1$"] > > lnSuccess = SQLExec( lnSQLHand, lcSQLCmd, [xlResults] ) > ? [SQL Cmd Success:], Iif( lnSuccess > 0, 'Good!', 'Failed' ) > If lnSuccess < 0 > Local Array laErr[1] > Aerror( laErr ) > ? laErr(3) > SQLDisconnect( lnSQLHand ) > Return .F. > Endif > > *-- Show the results > Select xlResults > *BROWSE noWAIT > *Return > > > Select xlResults > > * a variável guarda o total de registos do cursor tempcursor > mntotal=reccount("xlResults") > > > * inicializa a régua apresentando um título e o nº total de registos ( Inicalize ProgressBar ) > regua(0,mntotal,"A processar as linhas do ficheiro de Excel") > > Scan > > **Régua para mostrar o progresso da tarefa > regua[1,recno(),"Processando a linha nº " + ASTR(recno()) + " de " + ASTR(mntotal) ] ( ProgressBar counting Evolution ) > > **Funcionalidade que permite abortar a importação dos registos > If aborta() > regua(2) > mensagem("INTERROMPIDO ....") > fecha("xlResults") > Return .F. > Endif > > Select xlResults > > Select TODOS > Append Blank > > Replace TODOS.ref With Chrtran( Alltrim(xlResults.ref) , [...!|"§$%&-,. ] , [] ) In TODOS > Replace TODOS.refExcel With Upper(Alltrim(xlResults.ref)) > Replace TODOS.Design With Nvl(xlResults.Design,'') > Replace TODOS.newpc With Nvl(xlResults.pvp2,0.00) > Replace TODOS.MargRed With IIF(EMPTY(xlResults.MargRed),0,1) > Replace TODOS.Arred With IIF(EMPTY(xlResults.Arred ),0,1) > Replace TODOS.DescForn With Nvl(xlResults.DescForn ,0) > Replace TODOS.MARGBVND With Nvl(xlResults.MARGBVND,0) > Replace TODOS.CB With Chrtran( Alltrim(xlResults.ref) , [,] , [.] ) In TODOS > > Endscan > > *!* Fecha Regua > Regua(2) && close ProgressBar > > *!* Release Excel connection > SQLDisconnect( lnSQLHand ) > >>Could someone explain Why and what is missing to free Excel file and connection.