Skip to Content
0
Nov 21, 2017 at 08:40 PM

How connect to Excel using Microsoft.ACE.OLEDB.12.0 with PB 12.5?

386 Views Last edit Nov 21, 2017 at 09:04 PM 3 rev

Hi,

Our PB Version : 12.5.2 Build 5652

Windows 7 SP1

Excel 2013

We are trying export data to an existing Excel File (template).

Our code:

string ls_fname   
transaction lt_tran

ls_fname = "C:\Temp\Template.xlsx"

TRY
	lt_tran = create transaction
	// Profile Excel
	lt_tran.DBMS = "OLE DB"
	lt_tran.AutoCommit = TRUE
	lt_tran.DBParm = "PROVIDER='Microsoft.ACE.OLEDB.12.0';DATASOURCE ='" + ls_fname + "';PROVIDERSTRING='Excel 12.0 XML;HDR=NO'"
	connect using lt_tran;
	if lt_tran.sqlcode <> 0 then
		messagebox("Error" , "Erreur de connexion au fichier d'entrée est " + ls_fname + " @ " + lt_tran.sqlerrtext, stopsign!)
		return -1
	end if
	
	ll_row = dw_2.RowCount()


//	Ecrit les donnees de produits dans l'onglet 2
For ll_cpt = 1 to ll_row
	
	ls_select = 'insert into [Products] (F1,F2,F3,F4) (' 
	
	ls_select += "'" + dw_2.Object.produits_code_produit[ ll_cpt ] +"',"
	ls_select += "'" + dw_2.Object.produits_ligne_produit_id[ ll_cpt ] +"',"
	ls_select += "'" + dw_2.Object.produits_nom_produit[ ll_cpt ] +"',"
	ls_select += "'" + dw_2.Object.produit_code_sh_code_hs[ ll_cpt ] +"',"
	ls_select +=  ")"
	EXECUTE IMMEDIATE :ls_select USING lt_tran;
next
FINALLY
	disconnect using lt_tran;
	destroy ld_dst
	destroy lt_tran
END TRY

But we get an error at this line:

connect using lt_tran;

If we watch the content of lt_tran:

sqlerrortext= An error occurred, but error text could not be retrieved due to a failure in the error handler.

sqlcode=-1

sqldbcode=999

The file template is a empty excel file (xlsx) without macros or formulas.

Thank you