Hi

I have done vast research but i seem to get no working answer to my problem.

I have a code that sends data from excel sheet to database. It works
perfectly but the problem is values with commas and dots.

I have values like 1400,50 (EUROPEAN CHARSET)

Database reads values only like 1400.50 (US CHARSET)

I send the data with this code:

query = "INSERT INTO company (salary) SELECT " & CDbl(ActiveSheet.Cells(8, 5))

This makes the query look like:

"INSERT INTO company (salary) SELECT 1400,50"

I have set european regional setting in my control panel, SQL tries to read
the value as two values: 1400 and 50 (1400,50) - resulting into an error.

Salary is defined as double in the database.

I cannot simply make a script that changes all commas into dots, as there
can be values like 150.400,60 - i can never know where the comma is.

I can't ask all users to change their regional settings to US while using
the script, there are many users in many countries.

I cannot make the script change the whole system setting to US while the
script is run, as it would be too dangerous.

How can i make the visual basic understand that the value to be sent to the
SQL is 1400.50 and not 1400,50 ?

HELP!!!

Hi

This should do it:

MyVal = Replace(ActiveSheet.Cells(8, 5), ",", ".")
query = "INSERT INTO company (salary) SELECT " & MyVal

Regards,
Per

"Poppe" skrev i meddelelsen
...
Hi

I have done vast research but i seem to get no working answer to my
problem.

I have a code that sends data from excel sheet to database. It works
perfectly but the problem is values with commas and dots.

I have values like 1400,50 (EUROPEAN CHARSET)

Database reads values only like 1400.50 (US CHARSET)

I send the data with this code:

query = "INSERT INTO company (salary) SELECT " & CDbl(ActiveSheet.Cells(8,
5))

This makes the query look like:

"INSERT INTO company (salary) SELECT 1400,50"

I have set european regional setting in my control panel, SQL tries to
read
the value as two values: 1400 and 50 (1400,50) - resulting into an error.

Salary is defined as double in the database.

I cannot simply make a script that changes all commas into dots, as there
can be values like 150.400,60 - i can never know where the comma is.

I can't ask all users to change their regional settings to US while using
the script, there are many users in many countries.

I cannot make the script change the whole system setting to US while the
script is run, as it would be too dangerous.

How can i make the visual basic understand that the value to be sent to
the
SQL is 1400.50 and not 1400,50 ?

HELP!!!

as there
can be values like 150.400,60

I don't think you would return the thousands separator when you read the
Value property (only if you read the cell's Text property)

for the decimal separator maybe this will help

Application.International(xlDecimalSeparator)

Typically the separator will be the same in Excel and for the system, but
not necessarily. For your needs I guess the system is more relevant, if
same as Excel I don't think you need to change your code at all. That is,
unless your db is to be used by users with different settings. For the
system setting there are API methods but it might be OK to simply return

Format$(0, ".")

Regards,
Peter T





"Poppe" wrote in message
...
Hi

I have done vast research but i seem to get no working answer to my
problem.

I have a code that sends data from excel sheet to database. It works
perfectly but the problem is values with commas and dots.

I have values like 1400,50 (EUROPEAN CHARSET)

Database reads values only like 1400.50 (US CHARSET)

I send the data with this code:

query = "INSERT INTO company (salary) SELECT " & CDbl(ActiveSheet.Cells(8,
5))

This makes the query look like:

"INSERT INTO company (salary) SELECT 1400,50"

I have set european regional setting in my control panel, SQL tries to
read
the value as two values: 1400 and 50 (1400,50) - resulting into an error.

Salary is defined as double in the database.

I cannot simply make a script that changes all commas into dots, as there
can be values like 150.400,60 - i can never know where the comma is.

I can't ask all users to change their regional settings to US while using
the script, there are many users in many countries.

I cannot make the script change the whole system setting to US while the
script is run, as it would be too dangerous.

How can i make the visual basic understand that the value to be sent to
the
SQL is 1400.50 and not 1400,50 ?

HELP!!!

Hi Poppe

The part of your INSERT statement - CDbl(ActiveSheet.Cells(8, 5)) - will
yield the locale format used by Excel, which has the comma as decimal setting.

If you substitute with - Format(ActiveSheet.Cells(8, 5),"0.00") - this
should give you the correct structure for US number formats in the INSERT
statement.

--
Nick


"Poppe" wrote:

Hi

I have done vast research but i seem to get no working answer to my problem.

I have a code that sends data from excel sheet to database. It works
perfectly but the problem is values with commas and dots.

I have values like 1400,50 (EUROPEAN CHARSET)

Database reads values only like 1400.50 (US CHARSET)

I send the data with this code:

query = "INSERT INTO company (salary) SELECT " & CDbl(ActiveSheet.Cells(8, 5))

This makes the query look like:

"INSERT INTO company (salary) SELECT 1400,50"

I have set european regional setting in my control panel, SQL tries to read
the value as two values: 1400 and 50 (1400,50) - resulting into an error.

Salary is defined as double in the database.

I cannot simply make a script that changes all commas into dots, as there
can be values like 150.400,60 - i can never know where the comma is.

I can't ask all users to change their regional settings to US while using
the script, there are many users in many countries.

I cannot make the script change the whole system setting to US while the
script is run, as it would be too dangerous.

How can i make the visual basic understand that the value to be sent to the
SQL is 1400.50 and not 1400,50 ?

HELP!!!

Thanks for your replies. I tried all of above, but nothing works.

Results:

------------------

Per Jessen: Your code works only with values with a single comma. If i have
larger values like 100,400.60 - it does not. That would result in wrong
values

------------------

Peter T:

Application.International(xlDecimalSeparator) = The output result is ","

If i force the separator as dot, the execute command will still send the
value with comma:

With Application
.DecimalSeparator = "."
.UseSystemSeparators = False
End With

"INSERT INTO company (salary) SELECT " & ActiveSheet.Cells(8, 5))

Result = "INSERT INTO company (salary) SELECT 1400,50"

------------------

NickH:

"INSERT INTO company (salary) SELECT " & Format(ActiveSheet.Cells(8,
5),"0.00")

Result = "INSERT INTO company (salary) SELECT 1400,50"

It still uses commas.

------------------

It's really a tough problem. I also think it should read them in "raw" mode
where there is no comma, but for some reason it does. My regional setting in
control panel is Finnish.

What to do? This really seems like a question with no answer..

I found something that might give a clue..

In excel 2007 help there is an example under "Format Function"

------------

Dim MyStr
MyStr = Format(5459.4, "##,##0.00") ' Returns "5,459.40".

------------

But when i run this code i do not get "5,459.40" i get "5 459,40"

So what is going on? Not even the excel help file example works!

Hi Poppe

I always thought the Format string worked independently of the locale -
every day is a learning day :-)

Try the following:

Dim strPointNum As String, strCommaNum As String, p As Integer
Dim strSQL As String
strcommaNum = Format(ActiveSheet.Cells(8, 5), "0.00")
p = InStr(1, strCommaNum, ",", vbTextCompare)
strPointNum = Left(strCommaNum, p - 1) & "." & Right(strCommaNum, 2)
strSQL = "INSERT INTO company (salary) SELECT " & strPointNum & ";"

This assumes the formatted number has two decimal places. If you need a
different number, you must adjust the code accordingly.

I tried this going from my number format (UK) to yours (Finland) and it
worked - substituting my "." with your ",". I have swapped the comma and
decimal stres around, so it should work as-is for you.

Nick

--
Nick


"Poppe" wrote:

Thanks for your replies. I tried all of above, but nothing works.

Results:

------------------

Per Jessen: Your code works only with values with a single comma. If i have
larger values like 100,400.60 - it does not. That would result in wrong
values

------------------

Peter T:

Application.International(xlDecimalSeparator) = The output result is ","

If i force the separator as dot, the execute command will still send the
value with comma:

With Application
.DecimalSeparator = "."
.UseSystemSeparators = False
End With

"INSERT INTO company (salary) SELECT " & ActiveSheet.Cells(8, 5))

Result = "INSERT INTO company (salary) SELECT 1400,50"

------------------

NickH:

"INSERT INTO company (salary) SELECT " & Format(ActiveSheet.Cells(8,
5),"0.00")

Result = "INSERT INTO company (salary) SELECT 1400,50"

It still uses commas.

------------------

It's really a tough problem. I also think it should read them in "raw" mode
where there is no comma, but for some reason it does. My regional setting in
control panel is Finnish.

What to do? This really seems like a question with no answer..

Thanks for your reply.

I understand your idea, and i thought something similar. But the problem is
that you can never know where the comma exactly is. There can be different
kinds of values.

With this code:

Value 502.200,55 results into value 502.200.55 (should be 502,200.55)

Value 502.200,5 results into value 502.200.,5 (should be 502,200.5)


The Replace() function can change commas to dots, but situation is the same.
I have for example 2000 rows of excel chart with mixed values, changing the
code for all different values is impossible.

I can use code that forces whole windows system wide regional settings to
change while running the code, but that is a dangerous option and not good
coding.

Value 502.200,55

Where are you getting an apparent value like that from. AFAIK, although it
might be displayed like that in a cell, VB/A would read it as 502200d55
where d is the system's decimal separator (not Excel's separator although
typically it's the same).

Regards,
Peter T


"Poppe" wrote in message
...
Thanks for your reply.

I understand your idea, and i thought something similar. But the problem
is
that you can never know where the comma exactly is. There can be different
kinds of values.

With this code:

Value 502.200,55 results into value 502.200.55 (should be 502,200.55)

Value 502.200,5 results into value 502.200.,5 (should be 502,200.5)


The Replace() function can change commas to dots, but situation is the
same.
I have for example 2000 rows of excel chart with mixed values, changing
the
code for all different values is impossible.

I can use code that forces whole windows system wide regional settings to
change while running the code, but that is a dangerous option and not good
coding.

Did you try the code I suggested?

--
Nick


"Poppe" wrote:

Thanks for your reply.

I understand your idea, and i thought something similar. But the problem is
that you can never know where the comma exactly is. There can be different
kinds of values.

With this code:

Value 502.200,55 results into value 502.200.55 (should be 502,200.55)

Value 502.200,5 results into value 502.200.,5 (should be 502,200.5)


The Replace() function can change commas to dots, but situation is the same.
I have for example 2000 rows of excel chart with mixed values, changing the
code for all different values is impossible.

I can use code that forces whole windows system wide regional settings to
change while running the code, but that is a dangerous option and not good
coding.

Hi Poppe

I have done some more experimentation on this and my code works for both
cases of your sample data. In my case, I am doing the opposite from you;
i.e. taking numbers from UK locale (123,456.78) to European locale
(123.456,78) format.

I thought that your original data cells might be formatted as text, rather
than numbers. However, running my code on string format numbers still works.
I used "502,200.55" and "502,200.5" as string literals and they came out as
"502200,55" and "502200,50" respectively. The statement
'Format(ActiveSheet.Cells(8, 5),"0.0")' returns "502200.55" and "502200.50"
(in your system, you would have "," instead of "."). In other words, it has
stripped out the thousands separators, leaving just the decimal point. From
this point on, it is simply string manipulation - so providing you don't go
back to a number between deconstruction and the SQL statement, it should work.

Not sure where to go next on this.

--
Nick


"Poppe" wrote:

Thanks for your reply.

I understand your idea, and i thought something similar. But the problem is
that you can never know where the comma exactly is. There can be different
kinds of values.

With this code:

Value 502.200,55 results into value 502.200.55 (should be 502,200.55)

Value 502.200,5 results into value 502.200.,5 (should be 502,200.5)


The Replace() function can change commas to dots, but situation is the same.
I have for example 2000 rows of excel chart with mixed values, changing the
code for all different values is impossible.

I can use code that forces whole windows system wide regional settings to
change while running the code, but that is a dangerous option and not good
coding.