Check return value from SQLDisconnect() to make sure that it was successful
>
>I have build this code to import Excel file into VFP Cursor, but after make SQLDisconnect( lnSQLHand ), if i try open my excel file the same are READONLY:
>This is my code :
>
>
>
>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)
>
> *!* Release Excel connection
> SQLDisconnect( lnSQLHand )
>
>>Could someone explain Why and what is missing to free Excel file and connection.
>
>Many Thanks,
>Best regards,
>Luis
Heelo Sergey,
Thanks for your reply, the return value of SQLDisconnect is 1.
Best regards,
Luis
