#187618 - 2008-05-14 02:42 AM
Start Excel, then change the Locale setting...
|
_Andy_
Fresh Scripter
Registered: 2007-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:
;------------------------------------------------------------------------------
; 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
|
|
|
|
#187649 - 2008-05-15 02:40 AM
Re: Start Excel, then change the Locale setting...
[Re: Mart]
|
_Andy_
Fresh Scripter
Registered: 2007-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
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
|
|
|
|
Moderator: Shawn, ShaneEP, Ruud van Velsen, Arend_, Jochen, Radimus, Glenn Barnas, Allen, Mart
|
0 registered
and 507 anonymous users online.
|
|
|