xBrowse oBw:ToExcel()

[avista]

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: Select all

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

/*
   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]

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?

[avista]

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]

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

[avista]

Yes

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

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

Regards,

[nageswaragunupudi]

That is correct.

Then please also put another msginfo

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

[avista]

Result,

SUBTOTAL(9,I2:I87)

Here is source of the ExcelTranslate() function

Code: Select all

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

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]

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

[nageswaragunupudi]

Please use this revised function

Code: Select all

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
 

[avista]

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]

Up,

[nageswaragunupudi]

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