Page 1 of 1 1
Topic Options
#187618 - 08/05/14 02:42 AM Start Excel, then change the Locale setting...
_Andy_ Offline
Fresh Scripter

Registered: 07/10/27
Posts: 17
Loc: Sydney, Australia
Hi All,

Can someone help me with starting excel, change the locale settings in excel and then open an excel file (and turn on autofilters and autofit all columns).

As I am in Australia, I would like my dates in my excel file in my format dd/mm/yyyy. I have looked and looked and even tried a vb app... When the file opens some dates are corrupted, especially if there is a leading zero or the month and day are less than 12... I believe I need to enforce the locale or regional settings somehow...

Here is my code:

 Code:
;------------------------------------------------------------------------------
;   Name:       excel.kix
;------------------------------------------------------------------------------

break on
$=setoption('Explicit','ON')
$=setoption('WrapAtEOL','ON')
$=setoption('NoVarsInStrings','ON')

main
Quit 0

;------------------------------------------------------------------------------

Function main()
  Dim $rc
  Dim $filename
  Dim $autofilters
  Dim $xl
  Dim $wb
  Dim $ws
  Dim $rng

  $filename = "c:\fred.xls"
  $autofilters = "Y"

  ; Start excel.
  $xl = CreateObject("Excel.Application")
  if @error
    messagebox ("Errors detected starting excel...", "Unable to run command.", 0, 5)
    quit 1
  endif

  $xl.Visible = True
  $xl.DisplayAlerts = 0

  if exist($filename)			; if the file exists, open it

    $wb = $xl.Workbooks.Open($filename)
    $ws = $wb.WorkSheets(1)

    $rng = $ws.Range("A1:CC2000")

    if $autofilters = "Y"
      $rng.Rows("1:1").Select
      $rng.AutoFilter
      $ws.Range("A2").Select
    endif

    $rng.Cells.Select
    $rng.Cells.EntireColumn.AutoFit
    $ws.Range("A1").Select

  endif

  exit @ERROR

EndFunction

;------------------------------------------------------------------------------



Any help will be greatly appreciated.
_________________________
Regards,
Andy.


--Just shoot me, please.--

Top
#187627 - 08/05/14 09:32 AM Re: Start Excel, then change the Locale setting... [Re: _Andy_]
Mart Moderator Offline
KiX Supporter
*****

Registered: 02/03/27
Posts: 4406
Loc: Rotterdam, The Netherlands
I used Glenn's Excel library for some Excel tasks a few weeks ago.
There is one UDF that is able to change the cell format. The Excel library is located on his site but I must be going blind because I canít find the link anymore. Maybe Glenn can post a link here.
_________________________
Mart

- I don't have an attitude! I just have a personality that you cannot handle! - Jerry the mouse.

Top
#187635 - 08/05/14 03:19 PM Re: Start Excel, then change the Locale setting... [Re: Mart]
_Andy_ Offline
Fresh Scripter

Registered: 07/10/27
Posts: 17
Loc: Sydney, Australia
Problem is... as soon as you open the file, the date data is corrupted. Changing the cell format afterwards does not help.

eg. "01/02/2008" gets converted to 2/01/2008
and "20/02/2008" gets converted to 20/02/2008

Then changing the cell format to dd/mm/yyyy the values do not change.

Weird thing is... If I just double click on the csv file in Windows Explorer then the data is ok, but my autofilters and auto column width bits have to be done manually...
_________________________
Regards,
Andy.


--Just shoot me, please.--

Top
#187645 - 08/05/14 08:44 PM Re: Start Excel, then change the Locale setting... [Re: _Andy_]
Mart Moderator Offline
KiX Supporter
*****

Registered: 02/03/27
Posts: 4406
Loc: Rotterdam, The Netherlands
As far as I know Excel does not have its own regional settings. It just takes whatever is set in the regional settings in the control panel. So if you set it to US August 1st 2008 would become 08/01/2008 (not sure about the leading 0's) and if you set it to lets says Dutch August 1st 2008 will be 01/08/2008 both in Windows and Excel.
_________________________
Mart

- I don't have an attitude! I just have a personality that you cannot handle! - Jerry the mouse.

Top
#187649 - 08/05/15 02:40 AM Re: Start Excel, then change the Locale setting... [Re: Mart]
_Andy_ Offline
Fresh Scripter

Registered: 07/10/27
Posts: 17
Loc: Sydney, Australia
Found something that is leaving me a little more confused...

Open Excel 2003, Alt+F11, do a search: Named Numeric Format, open first one found - Named Numeric Formats (Format Function), click on bold word: locale

 Quote:

locale
The set of information that corresponds to a given language and country. The code locale setting affects the language of terms such as keywords and defines locale-specific settings such as the decimal and list separators, date formats, and character sorting order.

The system locale setting affects the way locale-aware functionality behaves, for example, when you display numbers or convert strings to dates. You set the system locale using the Control Panel utilities provided by the operating system.

Although the code locale and system locale are generally set to the same setting, they may differ in some situations. For example, in Visual Basic, Standard Edition and Visual Basic, Professional Edition, the code is not translated from English-U.S. The system locale can be set to the user's language and country, but the code locale is always set to English-U.S. and can't be changed. In this case, the English-U.S. separators, format placeholders, and sorting order are used.


Just !@#$% great...


Now I have to look for a non-vb based solution.
_________________________
Regards,
Andy.


--Just shoot me, please.--

Top
Page 1 of 1 1


Moderator:  Chris S., Shawn, ShaneEP, Sealeopard, Bryce, Jochen, Radimus, Kdyer, Howard Bullock, Glenn Barnas, Allen, Benny69, Mart 
Hop to:
Shout Box

Who's Online
1 registered (Allen) and 66 anonymous users online.

Generated in 0.031 seconds in which 0.002 seconds were spent on a total of 14 queries. Zlib compression enabled.

Search the board with:
superb Board Search
or try with google:
Google
Web kixtart.org