problem with report to excel

[Patrick Chalvet]

Each time I try to export report to excel I have this error;

Time from start: 0 hours 0 mins 27 secs
Error occurred at: 13/10/2013, 17:50:08
Error description: (DOS Error -2147352567) WINOLE/1006 (0x800A03EC): _FORMULA
Args:
[ 1] = C =SUBTOTAL(9,B7:B25)

Stack Calls
===========
Called from: => TOLEAUTO:_FORMULA( 0 )
Called from: .\source\classes\REPORT.PRG => TREPORT:TOEXCEL( 2167 )
Called from: .\source\classes\RPREVIEW.PRG => (b)TPREVIEW( 81 )
Called from: .\source\classes\RPREVIEW.PRG => TPREVIEW:EXPORTTOMSEXCEL( 0 )
Called from: .\source\classes\RPREVIEW.PRG => (b)TPREVIEW_BUILDBUTTONBAR( 362 )
Called from: .\source\classes\BTNBMP.PRG => TBTNBMP:CLICK( 471 )
Called from: .\source\classes\BTNBMP.PRG => TBTNBMP:LBUTTONUP( 662 )
Called from: .\source\classes\CONTROL.PRG => TCONTROL:HANDLEEVENT( 1723 )
Called from: .\source\classes\BTNBMP.PRG => TBTNBMP:HANDLEEVENT( 1465 )
Called from: .\source\classes\WINDOW.PRG => _FWH( 3234 )
Called from: => SYSREFRESH( 0 )
Called from: .\source\function\MSGRUN.PRG => STOPUNTIL( 60 )
Called from: .\source\classes\RPREVIEW.PRG => TPREVIEW:ACTIVATE( 143 )
Called from: .\source\classes\RPREVIEW.PRG => RPREVIEW( 1767 )
Called from: .\source\classes\REPORT.PRG => (b)TREPORT_NEW( 170 )
Called from: .\source\classes\REPORT.PRG => TREPORT:ACTIVATE( 850 )
Called from: Exobal.prg => NBALCLI( 1453 )
Called from: EXO.PRG => (b)BUILDMENU( 509 )
Called from: .\source\classes\MENU.PRG => TMENU:COMMAND( 463 )
Called from: .\source\classes\WINDOW.PRG => TWINDOW:COMMAND( 1050 )
Called from: .\source\classes\MDIFRAME.PRG => TMDIFRAME:COMMAND( 255 )
Called from: => TMDIFRAME:HANDLEEVENT( 0 )
Called from: .\source\classes\WINDOW.PRG => _FWH( 3234 )
Called from: => WINRUN( 0 )
Called from: .\source\classes\WINDOW.PRG => TMDIFRAME:ACTIVATE( 1003 )
Called from: EXO.PRG => MAIN( 80 )

Harbour 3.2.0dev (r1307180024)
Fivewin 13.08
Microsoft Visual Studio 2010
Version 10.0.30319.1 RTMRel
Version installée : VC Express

[driessen]

Patrick,

Can you put your source here?

Thanks.

[Patrick Chalvet]

Code: Select all  Expand view

FUNCTION nbalcli(gl,complet)
local ofont1,ofont2

default complet to .f.
default gl to .f.

if faire
DEFINE FONT oFont1 NAME "andale mono" SIZE 0,-9
DEFINE FONT oFont2 NAME "andale mono" SIZE 0,-9 BOLD

mess := if(gl,"LE GRAND LIVRE","LA BALANCE")

opar := oserver:query("select * from param")
moisref := str(pannee,4)+"-"+strzero(pmois,2)
arrete := ctod("01/"+strzero(pmois,2)+"/"+str(pannee,4))

if complet
 laq := "select facture.*,clients.nom as nnom from facture inner join clients on clients.tiers = facture.tiers where facture.tiers between "+c2s(cli1)+" and "+c2s(cli2)+;
       " and facture.interne = "+c2s(.F.)+ " and left(datef,7) <= "+c2s(moisref)
 lorder := " order by facture.tiers,facture.datef"
else
 laq := "select facture.*,clients.nom as nnom from facture inner join clients on clients.tiers = facture.tiers "
 laq += "where facture.interne = "+c2s(.F.)+ " and left(datef,7) <= "+c2s(moisref)+" and (reglement+acompte) <> montant"

 lorder := " order by nnom,facture.datef"
endif

//tribal := alert("Balance génerale",{"Générale","Par commerciaux"})
//lorder := if( tribal = 2," order by commerciaux,tiers,datef"," order by nom,tiers,datef")

bal := oserver:query(laq+lorder)
memowrit("laq.sql",bal:cquery)

lbtiers:= bal:tiers

REPORT oReport TITLE  opar:societe,mess+" CLIENTS","Au "+right(dtoc(arrete),7) LEFT ;
               FOOTER ("Page: ")+ltrim(str(oReport:nPage,3)) ;
               FONT oFont1, oFont2  PREVIEW
if gl
COLUMN TITLE "Renseignement" DATA dtoc(bal:datef)+" -- "+bal:factno SIZE 50 FONT 1 GRID
COLUMN TITLE "Debit"         DATA bal:montant FONT 2 PICTURE "@Z 999 999 999"   TOTAL RIGHT GRID
COLUMN TITLE "Crédit"        DATA bal:reglement+bal:acompte   FONT 2 PICTURE "@Z 999 999 999"  TOTAL RIGHT GRID
COLUMN TITLE "Solde"         DATA bal:montant-(bal:reglement+bal:acompte)   FONT 2 PICTURE "@Z 999 999 999"   TOTAL RIGHT GRID
else
COLUMN TITLE "Libellé"   DATA pad(" ",45) SIZE 45 FONT 1 GRID
COLUMN TITLE "Debit"     DATA bal:montant FONT 2 PICTURE "@Z 999 999 999"   TOTAL RIGHT GRID
COLUMN TITLE "Crédit"    DATA bal:reglement+bal:acompte   FONT 2 PICTURE "@Z 999 999 999"  TOTAL RIGHT GRID
COLUMN TITLE "Solde"     DATA bal:montant-(bal:reglement+bal:acompte)   FONT 2 PICTURE "@Z 999 999 999"   TOTAL RIGHT GRID
endif

GROUP ON bal:nnom  FOOTER padl(lbtiers,6,'0')+" "+oreport:agroups[1]:cvalue  FONT 2
END REPORT

oReport:oTitle:aFont[1] := {|| 2 }
setrsql(bal,oreport)
oreport:lsummary := if(gl,.f.,.t.)
oreport:ljoin := .t.
oreport:lboxontotal := .t.
oreport:odevice:lPrvModal := .t.
oreport:odevice:cdir := chepers

ACTIVATE REPORT oReport ON STARTGROUP  (lbtiers:= bal:tiers)
oFont1:End()
oFont2:End()

endif
return nil

function setrsql(osql,oreport)
oreport:bSkip  := {|n| osql:skip(n)}
oreport:bwhile := {|| ! osql:Eof() }  
oreport:bInit  := {|| osql:GOTOP() }
return nil

 

[nageswaragunupudi]

Which language is your Excel is installed for?

[Patrick Chalvet]

Excel is installed in French
Thanks

[nageswaragunupudi]

Will you please make this correction in report.prg?

Please locate the string '=SUBTOTAL(9,'
and substitute it with
'=SOUS.TOTAL(9;'

We would be glad if you can let us know if this modification works for French installation of Excel.

[Patrick Chalvet]

Thank you for your answer.
with this change

Please locate the string '=SUBTOTAL(9,'
and substitute it with
'=SOUS.TOTAL(9;'

no error,
but when the report is summarize
oreport:lsummary := .T. //if(gl,.f.,.t.)
Excel sheet does not reflect the fivewin report. it contains details
I prepare samples to show this behavior
thank you again
Patrick

[nageswaragunupudi]

At present, method toExcel() does not support summary reports.
Only full reports.
Please let us know if the group and report totals are exported to excel correctly without errors with this change ( for full reports ).

[Patrick Chalvet]

With this change ( for full reports ),
the group and report totals are exported to excel correctly without errors

Do you think that this Method toExcel() will support summary reports ?

[nageswaragunupudi]

We shall provide that in future releases.

[bart]

Will you please make this correction in report.prg?

Please locate the string '=SUBTOTAL(9,'
and substitute it with
'=SOUS.TOTAL(9;'

We would be glad if you can let us know if this modification works for French installation of Excel.

I changed the file report.prg '=SUBTOTAAL(9;' but stil have the same error.

[nageswaragunupudi]

SUBTOTAAL or SUBTOTAL ?
What is Excel installation language?

[bart]

Hello Mr. Rao,

Excel is installed in Dutch.

So '=SUBTOTAL(9,' was changed into 'SUBTOTAAL(9;'

Kind regards,
Bart

[nageswaragunupudi]

Excel is installed in Dutch.

So '=SUBTOTAL(9,' was changed into 'SUBTOTAAL(9;'

I see.
So will you be kind enough to let us know what is working correctly with Dutch installation of Excel?
Also please try oBrw:ToExcel() from XBrowse,
Both have the same language issues for SUBTOTAL.
Please help us to support Dutch installation by advising us the correct keyword and usage.
Thanks in advance.

[bart]

Hello Mr. Rao,

The Excel formula in Dutch is "=SUBTOTAAL(9;"

However when changing the report.prg file with the correct formula this has no effect.

Application
===========
Path and name: C:\Users\bartv\OneDrive\BKH\fwbkhmain.exe (64 bits)
Size: 4,384,768 bytes
Compiler version: Harbour 3.2.0dev (r1412151448)
FiveWin Version: FWH 15.01
Windows version: 6.2, Build 9200

Time from start: 0 hours 0 mins 31 secs
Error occurred at: 04/09/2023, 13:24:57
Error description: (DOS Error -2147352567) WINOLE/1006 (0x800A03EC): _FORMULA
Args:
[ 1] = C =SUBTOTAL(9,D4:D12)

Stack Calls
===========
Called from: => TOLEAUTO:_FORMULA( 0 )
Called from: .\source\classes\REPORT.PRG => TREPORT:TOEXCEL( 0 )
Called from: .\source\classes\RPREVIEW.PRG => (b)TPREVIEW( 0 )
Called from: .\source\classes\RPREVIEW.PRG => TPREVIEW:EXPORTTOMSEXCEL( 0 )
Called from: .\source\classes\RPREVIEW.PRG => (b)TPREVIEW_BUILDBUTTONBAR( 0 )
Called from: .\source\classes\BTNBMP.PRG => TBTNBMP:CLICK( 0 )
Called from: .\source\classes\BTNBMP.PRG => TBTNBMP:LBUTTONUP( 0 )
Called from: .\source\classes\CONTROL.PRG => TCONTROL:HANDLEEVENT( 0 )
Called from: .\source\classes\BTNBMP.PRG => TBTNBMP:HANDLEEVENT( 0 )
Called from: .\source\classes\WINDOW.PRG => _FWH( 0 )
Called from: => SYSREFRESH( 0 )
Called from: .\source\function\MSGRUN.PRG => STOPUNTIL( 0 )
Called from: .\source\classes\RPREVIEW.PRG => TPREVIEW:ACTIVATE( 0 )
Called from: .\source\classes\RPREVIEW.PRG => RPREVIEW( 0 )
Called from: .\source\classes\REPORT.PRG => (b)TREPORT_NEW( 0 )
Called from: .\source\classes\REPORT.PRG => TREPORT:ACTIVATE( 0 )
Called from: fwbkhreport.prg => PRINT_VERKOOPDAGBOEK( 245 )

Thanks in advance for your help.

Best regards
Bart