Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
Excel File is READONLY
Message
 
 
To
16/03/2019 10:27:32
Luis Santos
Biglevel-Soluções Informáticas, Lda
Portugal
General information
Forum:
Visual FoxPro
Category:
Coding, syntax & commands
Environment versions
Visual FoxPro:
VFP 9 SP2
OS:
Windows 10
Network:
Windows Server 2012 R2
Database:
MS SQL Server
Application:
Desktop
Miscellaneous
Thread ID:
01667287
Message ID:
01667293
Views:
79
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)  && close ProgressBar
>
> 	*!* Release Excel connection
>	SQLDisconnect( lnSQLHand )
>
>
>Could someone explain Why and what is missing to free Excel file and connection.
>
>Many Thanks,
>Best regards,
>Luis
--sb--
Previous
Next
Reply
Map
View

Click here to load this message in the networking platform