Posts: 301

Joined: Fri Jun 01, 2007 09:07 AM

xBrowse oBw:ToExcel()
Posted: Tue Dec 15, 2015 12:58 PM

To escape from error if ListSeparator is changed on the PC i use this function GetListSeparator() in xBrowse class

cFormula:= "SUBTOTAL(" + ;
LTrim( Str( FW_DeCode( IfNil( oCol:nFooterType, 0 ), AGGR_SUM, 9, AGGR_MAX, 4, AGGR_MIN, 5, ;
AGGR_COUNT, 3, AGGR_AVG, 1, AGGR_STDEV, 7, AGGR_STDEVP, 8, 9 ) ) ) + ;
// "," + ;
GetListSeparator() + ;
oSheet:Range( oSheet:Cells( 2, nCol ), ;
oSheet:Cells( nRow - 1, nCol ) ):Address( .f., .f. ) + ;
")"

Code (fw): Select all Collapse

//----------------------------------------------------------------------------//

/*
   New Function GetListSeparator()
   used to return "List Separator" from "Regional and Language Options"
   This character is used in oBw:ToExcel() METHOD
   by default in XBROWSE.PRG is ","
   but if it is changed in "Regional and Language Options" for example with "|"
   program crash
*/

Function GetListSeparator() // Returning "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

//----------------------------------------------------------------------------//

Best regards,

nageswaragunupudi

Posts: 10733

Joined: Sun Nov 19, 2006 05:22 AM

Re: xBrowse oBw:ToExcel()
Posted: Wed Dec 16, 2015 10:34 PM

The formula that is built above is translated by the function ExcelTranslate( cFormula ) before assigning to the cell in the next line.

The function ExcelTranslate replaces "," with the proper ListSeparator. This function is in olefuncs.prg.

Here ListSepator is obtained from Excel Object than from the Registry.
May I know if the version of FWH you are using contains this ExcelTranslate() function?

Regards

G. N. Rao.

Hyderabad, India

avista

Posts: 301

Joined: Fri Jun 01, 2007 09:07 AM

Re: xBrowse oBw:ToExcel()
Posted: Thu Dec 17, 2015 08:15 AM

Mr. Rao,

I use FWH 14.12
xHarbour (Build 20141124)
bcc582

yes, FWH i am using contains this ExcelTranslate() function.

BUT program crash at line 7545
oSheet:Cells( nRow, nCol ):Formula := '=' + ExcelTranslate( cFormula )

ListSeparator to my PC is "|"

Error description: Error Excel.Application:ACTIVESHEET:CELLS/0 S_OK: _FORMULA
Args:
[ 1] = C =SUBTOTAL(9,I2:I87)

Stack Calls
===========
Called from: => TOLEAUTO:_FORMULA( 0 )
Called from: .\xbrowse.PRG => TXBROWSE:TOEXCEL( 7545 )

Best regards,

nageswaragunupudi

Posts: 10733

Joined: Sun Nov 19, 2006 05:22 AM

Re: xBrowse oBw:ToExcel()
Posted: Thu Dec 17, 2015 08:22 AM

Can you help ?
Can you check the result of oExcel:International[ 5 ] ?

Regards

G. N. Rao.

Hyderabad, India

avista

Posts: 301

Joined: Fri Jun 01, 2007 09:07 AM

Re: xBrowse oBw:ToExcel()
Posted: Thu Dec 17, 2015 08:33 AM

Yes

I put msginfo just before line 7545 (where program crash)

the value of oExcel:International[ 5 ] is "|"

Regards,

nageswaragunupudi

Posts: 10733

Joined: Sun Nov 19, 2006 05:22 AM

Re: xBrowse oBw:ToExcel()
Posted: Thu Dec 17, 2015 08:51 AM

That is correct.

Then please also put another msginfo

msginfo( ExcelTranslate( cFormula ) ) // without applying your correction

Regards

G. N. Rao.

Hyderabad, India

avista

Posts: 301

Joined: Fri Jun 01, 2007 09:07 AM

Re: xBrowse oBw:ToExcel()
Posted: Thu Dec 17, 2015 09:19 AM

Result,

SUBTOTAL(9,I2:I87)

Here is source of the ExcelTranslate() function

Code (fw): Select all Collapse

//----------------------------------------------------------------------------//

function ExcelTranslate( cFunc )

   local cRet, nAt, cName

   // 1 English, 2 Spanish, 3 French, 4 Portugese, 5 German, 6 Italian
   local aTranslates := { ;
   { "TRUE",      "VERDADERO",   "VRAI",        "VERDADEIRO", "WAHR",          "VERO" }, ;
   { "FALSE",     "FALSO",       "FAUX",        "FALSO",      "FALSCH",        "FALSO" }, ;
   { "SUM(",      "SUMA(",       "SOMME(",      "SOMA(",      "SUMME(",        "SOMMA(" }, ;
   { "SUBTOTAL(", "SUBTOTALES(", "SOUS.TOTAL(", "SUBTOTAL(",  "TEILERGEBNIS(", "SUBTOTALE(" }, ;
   { ',',         ';',           ';',           ';',          ';',              ';' } ;
   }

   if nExcelLangNo == nil
      ExcelLangID()
   endif
   cFunc       := Upper( cFunc )
   cRet        := cFunc
   if nExcelLangNo > 1 .and. nExcelLangNo <= 6
      // Translation required
      cName    := cFunc
      if ( nAt := At( '(', cFunc ) ) > 0
         cName    := Left( cFunc, nAt )
      endif
      if ( nAt := AScan( aTranslates, { |a| a[ 1 ] == cName } ) ) > 0
         cRet     := StrTran( cFunc, cName, aTranslates[ nAt, nExcelLangNo ] )
         cRet     := StrTran( cRet, ',', ATAIL( aTranslates )[ nExcelLangNo ] )
      endif
   elseif nExcelLangNo != 1
      if cRet == "TRUE";  cRet := "1=1"; endif
      if cRet == "FALSE"; cRet := "1=0"; endif
   endif

return cRet

//----------------------------------------------------------------------------//

Regards,

nageswaragunupudi

Posts: 10733

Joined: Sun Nov 19, 2006 05:22 AM

Re: xBrowse oBw:ToExcel()
Posted: Thu Dec 17, 2015 09:24 AM

We need to fix exceltranslate function. I'll get back

Regards

G. N. Rao.

Hyderabad, India

nageswaragunupudi

Posts: 10733

Joined: Sun Nov 19, 2006 05:22 AM

Re: xBrowse oBw:ToExcel()
Posted: Thu Dec 17, 2015 01:45 PM

Please use this revised function

Code (fw): Select all Collapse

function ExcelTranslate( cFunc )

   local cRet, nAt, cName

   // 1 English, 2 Spanish, 3 French, 4 Portugese, 5 German, 6 Italian
   local aTranslates := { ;
   { "TRUE",      "VERDADERO",   "VRAI",        "VERDADEIRO", "WAHR",          "VERO" }, ;
   { "FALSE",     "FALSO",       "FAUX",        "FALSO",      "FALSCH",        "FALSO" }, ;
   { "SUM(",      "SUMA(",       "SOMME(",      "SOMA(",      "SUMME(",        "SOMMA(" }, ;
   { "SUBTOTAL(", "SUBTOTALES(", "SOUS.TOTAL(", "SUBTOTAL(",  "TEILERGEBNIS(", "SUBTOTALE(" }, ;
   { ',',         ';',           ';',           ';',          ';',              ';' } ;
   }

   if nExcelLangNo == nil
      ExcelLangID()
   endif
   cFunc       := Upper( cFunc )
   cRet        := cFunc
   if nExcelLangNo > 1 .and. nExcelLangNo <= 6
      // Translation required
      cName    := cFunc
      if ( nAt := At( '(', cFunc ) ) > 0
         cName    := Left( cFunc, nAt )
      endif
      if ( nAt := AScan( aTranslates, { |a| a[ 1 ] == cName } ) ) > 0
         cRet     := StrTran( cFunc, cName, aTranslates[ nAt, nExcelLangNo ] )
      endif
   elseif nExcelLangNo != 1
      if cRet == "TRUE";  cRet := "1=1"; endif
      if cRet == "FALSE"; cRet := "1=0"; endif
   endif

   if cListSeparator != ','
      cRet     := StrTran( cRet, ',', cListSeparator )
   endif

return cRet

Regards

G. N. Rao.

Hyderabad, India

avista

Posts: 301

Joined: Fri Jun 01, 2007 09:07 AM

Re: xBrowse oBw:ToExcel()
Posted: Thu Dec 17, 2015 02:12 PM

I replaced the function
and included file olefuncs.prg in program

This error occured

Error description: Error BASE/1003 Variable does not exist: CLISTSEPARATOR

Stack Calls

Called from: .\olefuncs.PRG => EXCELTRANSLATE( 184 )
Called from: .\xbrowse.PRG => SETEXCELLANGUAGE( 8791 )
Called from: .\xbrowse.PRG => TXBROWSE:TOEXCEL( 7339 )

Regards,

avista

Posts: 301

Joined: Fri Jun 01, 2007 09:07 AM

Re: xBrowse oBw:ToExcel()
Posted: Thu Dec 24, 2015 07:56 AM

Up,

nageswaragunupudi

Posts: 10733

Joined: Sun Nov 19, 2006 05:22 AM

Re: xBrowse oBw:ToExcel()
Posted: Thu Dec 24, 2015 08:59 AM

We are using cListSeparator in versions subsequent to your version. We are deriving cListSeparator from oExcel:International[ 5 ].

We made suitable modification as proposed above in the next version and is working.
For older versions you may need to use oExcel:International[ 5 ] or your Regstry function to replace comma

Regards

G. N. Rao.

Hyderabad, India

FiveTech Support Forums

Stack Calls

Continue the discussion