Using decimalseperator in Excel 1

[Ldegeus]

I want to change the decimalseperato in an Excel sheet. I want to do this with Application.decimalseperator="," or Application.Decimalseperator="." but for some reason the application doesn't ecccept it. I think it has to do with some library I didn't install but I'm not sure. Which library has to be installed to fix this.

Using
Windows XP SP2
Excell 2000

 

[PHV]

You have to use this too:
Application.UseSystemSeparators = False

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Ldegeus]

I found out that in the given version this doesn't work. In later version this command is available.

 

[FaneDuru]

This code changes the decimal separator in Registry:

Sub RegionalSettings()
Set regedit = CreateObject("WScript.Shell")
 A = regedit.RegRead("HKEY_CURRENT_USER\Control Panel\International\sDecimal")

 If A = "," Then
   MsgBox "We'll change "","" with ""."" !"
   regedit.RegWrite "HKEY_CURRENT_USER\Control Panel\International\sDecimal", "."
   regedit.RegWrite "HKEY_CURRENT_USER\Control Panel\International\sThousand", ","
 Else
   MsgBox "We'll cahge ""."" with "","" !"
   regedit.RegWrite "HKEY_CURRENT_USER\Control Panel\International\sDecimal", ","
   regedit.RegWrite "HKEY_CURRENT_USER\Control Panel\International\sThousand", "."
 End If
End Sub

Unfortunately changes take efect only after closing and reopening of Excel.
I solved that before creating a script able to memorize all the open files and addins, to find the open session of Excel and close it followed by reopening of all files...

Fane Duru

 

[Ldegeus]

thank you for your info. It's sad indeed that it only takes affect after restarting Excell. I took an other solution. With replace I change all "." with ",". Its only one column and the max value is about 200 so I have no problems with thousands. And I don't save the changed data so thats no problem also. I also use the program on a Excell 2003 and there I adjusted the program and that works fine.

 

[FaneDuru]

If I will have some time I will post also the code able to close and open Excel with all files...

I used to have an addin making that

Fane Duru