Posts: 301

Joined: Fri Jun 01, 2007 09:07 AM

oBrw:ToExcel() error ACTIVESHEET:CELLS/0 S_OK: _FORMULA
Posted: Thu Apr 26, 2012 01:54 PM

Hi,
My program was worked good until few days. I havnt changed anything is source code.
Now while XBROWSE exporting data to EXCEL 'TXBROWSE:TOEXCEL()' method produce error:

Application

Path and name: C:\dantools\dantools.exe (32 bits)
Size: 3,117,056 bytes
Time from start: 0 hours 0 mins 36 secs
Error occurred at: 26/04/2012, 15:06:10
Error description: Error Excel.Application:ACTIVESHEET:CELLS/0 S_OK: _FORMULA
Args:
[ 1] = C =SUBTOTAL(9,I2:I87)

Stack Calls

Called from: source\rtl\win32ole.prg => TOLEAUTO:_FORMULA(0)
Called from: .\xbrowse.PRG => TXBROWSE:TOEXCEL(6871)
Called from: .\mybrowse.PRG => (b)EXCELMENU(457)
Called from: .\source\classes\MENU.PRG => TMENU:ACTIVATE(474)
Called from: .\source\classes\BTNBMP.PRG => TBTNBMP:SHOWPOPUP(1484)
Called from: .\mybrowse.PRG => (b)BTNBAR(307)
Called from: .\source\classes\BTNBMP.PRG => TBTNBMP:CLICK(466)
Called from: .\source\classes\BTNBMP.PRG => TBTNBMP:LBUTTONUP(660)
Called from: .\source\classes\CONTROL.PRG => TCONTROL:HANDLEEVENT(1489)
Called from: .\source\classes\BTNBMP.PRG => TBTNBMP:HANDLEEVENT(1455)
Called from: .\source\classes\WINDOW.PRG => _FWH(3409)
Called from: => DIALOGBOXINDIRECT(0)
Called from: .\source\classes\DIALOG.PRG => TDIALOG:ACTIVATE(273)
Called from: .\mybrowse.PRG => MYBROWSE(197)
Called from: .\sql.PRG => IZVRSISQL(583)
Called from: .\sql.PRG => (b)SQL(101)
Called from: .\source\classes\BUTTON.PRG => TBUTTON:CLICK(176)
Called from: .\source\classes\CONTROL.PRG => TBUTTON:HANDLEEVENT(1447)
Called from: .\source\classes\WINDOW.PRG => _FWH(3409)
Called from: => SENDMESSAGE(0)
Called from: .\source\classes\DIALOG.PRG => TDIALOG:COMMAND(407)
Called from: => TWINDOW:HANDLEEVENT(0)
Called from: .\source\classes\DIALOG.PRG => TDIALOG:HANDLEEVENT(933)
Called from: => DIALOGBOXINDIRECT(0)
Called from: .\source\classes\DIALOG.PRG => TDIALOG:ACTIVATE(273)
Called from: .\sql.PRG => SQL(456)
Called from: .\dantools.PRG => (b)TAPPLICATION:BUILDMENU(261)
Called from: .\source\classes\MENU.PRG => TMENU:COMMAND(441)
Called from: .\source\classes\WINDOW.PRG => TWINDOW:COMMAND(1043)
Called from: .\source\classes\MDIFRAME.PRG => TMDIFRAME:COMMAND(243)
Called from: => TMDIFRAME:HANDLEEVENT(0)
Called from: .\source\classes\WINDOW.PRG => _FWH(3409)
Called from: => WINRUN(0)
Called from: .\source\classes\WINDOW.PRG => TMDIFRAME:ACTIVATE(992)
Called from: .\dantools.PRG => TAPPLICATION:ACTIVATE(207)
Called from: .\dantools.PRG => MAIN(122)

While analizing i remembered that i have changed "List Separator" in "Regional and Language Options" from "," to "|"
I need that from time to time for saving EXCEL files as ".CSV" file with "|" separator.
I put it back to "," and program work OK.
I maded one more test:
Changed "List Separator" with "|" and in
XBROWSE CLASS in static function SetExcelLanguage( oExcel )
changed line:
cxlSum := "SUBTOTAL(9,"
with
cxlSum := "SUBTOTAL(9|"
and program work good.

SO: "," character really is "List Separator" which is put in "Regional and Language Options"

My Question is HOW TO DETERMINE "List Separator" from "Regional and Language Options" and how to send it
like a parameter to XBROWSE
Or some other solution PLEASE.
No sense to change program all time or not changing "Regional and Language Options"

Best Regards,

nageswaragunupudi

Posts: 10733

Joined: Sun Nov 19, 2006 05:22 AM

Re: oBrw:ToExcel() error ACTIVESHEET:CELLS/0 S_OK: _FORMULA
Posted: Thu Apr 26, 2012 05:25 PM

My Question is HOW TO DETERMINE "List Separator" from "Regional and Language Options" and how to send it
like a parameter to XBROWSE

We shall soon provide a way for the programmer to specify language specific parameters, to replace the defaults where necessary.

We also need to do this commonly for toExcel() methods both in XBrowse and Treport.

Regards

G. N. Rao.

Hyderabad, India

avista

Posts: 301

Joined: Fri Jun 01, 2007 09:07 AM

Re: oBrw:ToExcel() error ACTIVESHEET:CELLS/0 S_OK: _FORMULA
Posted: Thu Apr 26, 2012 08:49 PM

Hi Rao,
Thanks for reply,
I see but ...
some sugestions for now ?
I ask again ...any chance to TO DETERMINE "List Separator" from "Regional and Language Options" from FWH
If yes i will make some changes to XBROWSE class to use "List Separator"
May be i can ask user when start oBrw:ToExcel() what "List Separator" use but that is amateurish and however someone dont know what is that
If no ...any chance to use TRY clausule to stop CRASHING ptogram ?
Any ideas useful

Best Regards,

lucasdebeltran

Posts: 1303

Joined: Tue Jul 21, 2009 08:12 AM

Re: oBrw:ToExcel() error ACTIVESHEET:CELLS/0 S_OK: _FORMULA
Posted: Sat Apr 28, 2012 02:55 PM

Great Mr. Rao.

I also offer my help to test on spanish systems.

Thank you very much.

Muchas gracias. Many thanks.

Un saludo, Best regards,

Harbour 3.2.0dev, Borland C++ 5.82 y FWH 13.06 [producción]

Implementando MSVC 2010, FWH64 y ADO.

Abandonando uso xHarbour y SQLRDD.

avista

Posts: 301

Joined: Fri Jun 01, 2007 09:07 AM

Re: oBrw:ToExcel() error ACTIVESHEET:CELLS/0 S_OK: _FORMULA
Posted: Mon Apr 30, 2012 12:28 PM

Hi Rao,

I please again ...
some sugestions for now ?

any chance to TO DETERMINE "List Separator" from "Regional and Language Options" from FWH
If yes i will make some changes to XBROWSE class to use "List Separator"

If no ...any chance to use TRY clausule to stop CRASHING program ?
Any ideas useful

Best Regards,

avista

Posts: 301

Joined: Fri Jun 01, 2007 09:07 AM

Re: oBrw:ToExcel() error ACTIVESHEET:CELLS/0 S_OK: _FORMULA
Posted: Wed May 02, 2012 08:05 AM

??

avista

Posts: 301

Joined: Fri Jun 01, 2007 09:07 AM

Re: oBrw:ToExcel() error ACTIVESHEET:CELLS/0 S_OK: _FORMULA
Posted: Mon May 07, 2012 02:56 PM

My solution for this problem.
First i added a function GetListSeparator() in XBROWSE CLASS which return
"List Separator" from "Regional and Language Options"

Code (fw): Select all Collapse

function GetListSeparator() // Return "List Separator" from "Regional and Language Options"

// Managing Register services from FiveWin

#define  HKEY_CLASSES_ROOT       2147483648
#define  HKEY_CURRENT_USER       2147483649
#define  HKEY_LOCAL_MACHINE      2147483650
#define  HKEY_USERS              2147483651
#define  HKEY_PERFORMANCE_DATA   2147483652
#define  HKEY_CURRENT_CONFIG     2147483653
#define  HKEY_DYN_DATA           2147483654


   local cListSeparator := ","  // Default value
   local hKey, cName, uValue, n := 0

   if RegOpenKey( HKEY_CURRENT_USER,;
         "Control Panel\International", @hKey ) == 0

      while RegEnumValue( hKey, n++, @cName, @uValue ) == 0
            if cName = "sList"
               cListSeparator := left( uValue, 1 )
            endif
      end

   endif

return cListSeparator

In class XBROWSE in method
METHOD ToExcel( bProgress, nGroupBy, aCols ) CLASS TXBrowse
i changed lines:
if ! Empty( cxlSum )
cxlAggr := cxlSum
with:
if ! Empty( cxlSum )
// cxlAggr := cxlSum
cxlAggr := left(cxlSum,len(alltrim(cxlSum))-1) + GetListSeparator()

Working nice with English and probably Spanis .. i am not sure for other languages.

First i started with changing in:
static function SetExcelLanguage( oExcel )
changed line:
cxlSum := "SUBTOTAL(9,"
with:
cxlSum := "SUBTOTAL(9" + GetListSeparator()

Working GOOD BUT if change list separator while program is still active program CRASH again
with the same error:
Error description: Error Excel.Application:ACTIVESHEET:CELLS/0 S_OK: _FORMULA

So I think first WAY is ok but i still ask for your opinion or some other or better solution.

Best regards,

nageswaragunupudi

Posts: 10733

Joined: Sun Nov 19, 2006 05:22 AM

Re: oBrw:ToExcel() error ACTIVESHEET:CELLS/0 S_OK: _FORMULA
Posted: Wed Dec 31, 2014 02:26 PM

An easier way to get list separator is

cListSeparator := oExcel:International[ 5 ]

Regards

G. N. Rao.

Hyderabad, India

FiveTech Support Forums

Application

Stack Calls

Continue the discussion