Excel Automation : numbers Copy/Paste issue from VB6
 

Hi,

Here is the context of the issue I'm encountering:
- Windows decimal separator : , (column)
- Windows thousands sep : (blank)
- Excel set to use system separators.
- Excel version is Excel 2003

From VB6, I copy a number (146,876) to the clipboard (thus with a
decimal sep).
I then paste the number to the one cell in Excel using the following
code:
ExcelApp.ActiveSheet.Range("A1").Select
ExcelApp.ActiveSheet.Paste

The target cell will contain the number 146 876 (so no more decimal).
As the number is still in the clipboard, I switch to excel and hit the
Paste button. Ho miracle, the number is correctly pasted... If the
source number is something like 176,98 , it will be pasted as Text and
thus left aligned.
I checked the excelapp variable in the watch window, the decimal
separator is correctly set to column.

Any idea?

Thanks,

Olivier

Excel Automation : numbers Copy/Paste issue from VB6
 

Olivier,
Why not make it easier for yourself and just:
ExcelApp.ActiveSheet.Range("A1").Value=146876
Then the appearance will be determined by the format for that cell.

NickHK

wrote in message
ups.com...
Hi,

Here is the context of the issue I'm encountering:
- Windows decimal separator : , (column)
- Windows thousands sep : (blank)
- Excel set to use system separators.
- Excel version is Excel 2003

From VB6, I copy a number (146,876) to the clipboard (thus with a
decimal sep).
I then paste the number to the one cell in Excel using the following
code:
ExcelApp.ActiveSheet.Range("A1").Select
ExcelApp.ActiveSheet.Paste

The target cell will contain the number 146 876 (so no more decimal).
As the number is still in the clipboard, I switch to excel and hit the
Paste button. Ho miracle, the number is correctly pasted... If the
source number is something like 176,98 , it will be pasted as Text and
thus left aligned.
I checked the excelapp variable in the watch window, the decimal
separator is correctly set to column.

Any idea?

Thanks,

Olivier

Excel Automation : numbers Copy/Paste issue from VB6
 

It would of course be easier.
What I'm just doing is an export of a datagrid from VB6 to Excel.
I found it's much more faster to build a string with my grid data and
paste it to excel than copy/pasting data row by row in Excel.

Olivier

NickHK a écrit :

Olivier,
Why not make it easier for yourself and just:
ExcelApp.ActiveSheet.Range("A1").Value=146876
Then the appearance will be determined by the format for that cell.

NickHK

wrote in message
ups.com...
Hi,

Here is the context of the issue I'm encountering:
- Windows decimal separator : , (column)
- Windows thousands sep : (blank)
- Excel set to use system separators.
- Excel version is Excel 2003

From VB6, I copy a number (146,876) to the clipboard (thus with a
decimal sep).
I then paste the number to the one cell in Excel using the following
code:
ExcelApp.ActiveSheet.Range("A1").Select
ExcelApp.ActiveSheet.Paste

The target cell will contain the number 146 876 (so no more decimal).
As the number is still in the clipboard, I switch to excel and hit the
Paste button. Ho miracle, the number is correctly pasted... If the
source number is something like 176,98 , it will be pasted as Text and
thus left aligned.
I checked the excelapp variable in the watch window, the decimal
separator is correctly set to column.

Any idea?

Thanks,

Olivier

Excel Automation : numbers Copy/Paste issue from VB6
 

Olivier,
If you can construct an array of you grid values, you can dump them all
together.
Dim arr(1 To 2, 1 To 2)
arr(1, 1) = 1
arr(1, 2) = 2
arr(2, 1) = 3
arr(2, 2) = 4
Range("A1").Resize(UBound(arr, 1), UBound(arr, 2)) = arr

Or if you a RS with thr grid data:
Range("A1").CopyFromRecordset

I do not see how string manipulation could be any faster than using numbers.

NickHK


"oliviers" wrote in message
oups.com...
It would of course be easier.
What I'm just doing is an export of a datagrid from VB6 to Excel.
I found it's much more faster to build a string with my grid data and
paste it to excel than copy/pasting data row by row in Excel.

Olivier

NickHK a écrit :

Olivier,
Why not make it easier for yourself and just:
ExcelApp.ActiveSheet.Range("A1").Value=146876
Then the appearance will be determined by the format for that cell.

NickHK

wrote in message
ups.com...
Hi,

Here is the context of the issue I'm encountering:
- Windows decimal separator : , (column)
- Windows thousands sep : (blank)
- Excel set to use system separators.
- Excel version is Excel 2003

From VB6, I copy a number (146,876) to the clipboard (thus with a
decimal sep).
I then paste the number to the one cell in Excel using the following
code:
ExcelApp.ActiveSheet.Range("A1").Select
ExcelApp.ActiveSheet.Paste

The target cell will contain the number 146 876 (so no more decimal).
As the number is still in the clipboard, I switch to excel and hit the
Paste button. Ho miracle, the number is correctly pasted... If the
source number is something like 176,98 , it will be pasted as Text and
thus left aligned.
I checked the excelapp variable in the watch window, the decimal
separator is correctly set to column.

Any idea?

Thanks,

Olivier

Excel Automation : numbers Copy/Paste issue from VB6
 

Ok, your samples are interesting.
But my goal is not to get the fastest way to copy data from VB6 to
Excel.

From VB6, just get a reference to the active worksheet and try the
following:

ExcelObject.Activesheet.Cells(1,1) = "123,456" while the decimal
separator is , (column)

My excel cell is transformed to 123456 ! He just did not care about
the decimal sep.

Olivier

NickHK a écrit :

Olivier,
If you can construct an array of you grid values, you can dump them all
together.
Dim arr(1 To 2, 1 To 2)
arr(1, 1) = 1
arr(1, 2) = 2
arr(2, 1) = 3
arr(2, 2) = 4
Range("A1").Resize(UBound(arr, 1), UBound(arr, 2)) = arr

Or if you a RS with thr grid data:
Range("A1").CopyFromRecordset

I do not see how string manipulation could be any faster than using numbers.

NickHK


"oliviers" wrote in message
oups.com...
It would of course be easier.
What I'm just doing is an export of a datagrid from VB6 to Excel.
I found it's much more faster to build a string with my grid data and
paste it to excel than copy/pasting data row by row in Excel.

Olivier

NickHK a écrit :

Olivier,
Why not make it easier for yourself and just:
ExcelApp.ActiveSheet.Range("A1").Value=146876
Then the appearance will be determined by the format for that cell.

NickHK

wrote in message
ups.com...
Hi,

Here is the context of the issue I'm encountering:
- Windows decimal separator : , (column)
- Windows thousands sep : (blank)
- Excel set to use system separators.
- Excel version is Excel 2003

From VB6, I copy a number (146,876) to the clipboard (thus with a
decimal sep).
I then paste the number to the one cell in Excel using the following
code:
ExcelApp.ActiveSheet.Range("A1").Select
ExcelApp.ActiveSheet.Paste

The target cell will contain the number 146 876 (so no more decimal).
As the number is still in the clipboard, I switch to excel and hit the
Paste button. Ho miracle, the number is correctly pasted... If the
source number is something like 176,98 , it will be pasted as Text and
thus left aligned.
I checked the excelapp variable in the watch window, the decimal
separator is correctly set to column.

Any idea?

Thanks,

Olivier

Excel Automation : numbers Copy/Paste issue from VB6
 

Olivier,
Because it depends how that cell is formatted. If you want a particular
format, you need to set it.
You can see this if you examine the .Value and .Text properties of your
range with different formatting.
If you really want the value as a string rather than a number set it to
"'123,456", otherwise set the number format.

NickHK

"oliviers" wrote in message
oups.com...
Ok, your samples are interesting.
But my goal is not to get the fastest way to copy data from VB6 to
Excel.

From VB6, just get a reference to the active worksheet and try the
following:

ExcelObject.Activesheet.Cells(1,1) = "123,456" while the decimal
separator is , (column)

My excel cell is transformed to 123456 ! He just did not care about
the decimal sep.

Olivier

NickHK a écrit :

Olivier,
If you can construct an array of you grid values, you can dump them all
together.
Dim arr(1 To 2, 1 To 2)
arr(1, 1) = 1
arr(1, 2) = 2
arr(2, 1) = 3
arr(2, 2) = 4
Range("A1").Resize(UBound(arr, 1), UBound(arr, 2)) = arr

Or if you a RS with thr grid data:
Range("A1").CopyFromRecordset

I do not see how string manipulation could be any faster than using
numbers.

NickHK


"oliviers" wrote in message
oups.com...
It would of course be easier.
What I'm just doing is an export of a datagrid from VB6 to Excel.
I found it's much more faster to build a string with my grid data and
paste it to excel than copy/pasting data row by row in Excel.

Olivier

NickHK a écrit :

Olivier,
Why not make it easier for yourself and just:
ExcelApp.ActiveSheet.Range("A1").Value=146876
Then the appearance will be determined by the format for that cell.

NickHK

wrote in message
ups.com...
Hi,

Here is the context of the issue I'm encountering:
- Windows decimal separator : , (column)
- Windows thousands sep : (blank)
- Excel set to use system separators.
- Excel version is Excel 2003

From VB6, I copy a number (146,876) to the clipboard (thus with a
decimal sep).
I then paste the number to the one cell in Excel using the following
code:
ExcelApp.ActiveSheet.Range("A1").Select
ExcelApp.ActiveSheet.Paste

The target cell will contain the number 146 876 (so no more decimal).
As the number is still in the clipboard, I switch to excel and hit the
Paste button. Ho miracle, the number is correctly pasted... If the
source number is something like 176,98 , it will be pasted as Text
and
thus left aligned.
I checked the excelapp variable in the watch window, the decimal
separator is correctly set to column.

Any idea?

Thanks,

Olivier

Excel Automation : numbers Copy/Paste issue from VB6
 

NickHK,

I'm of course aware of the range format.
The point is that the behavior is different (or seems differrent) when
I paste the string 123,456 from VB6 or directly from Excel. The cell
format is the same in both cases.

The clipboard contains 123,145.
I paste it from VB6, it is modified to 123456.
I do nothing but switch to Excel and click the paste button 123,456
is pasted.
Format is the same. Behavior is different. Just like if Excel was smart
enough to detect the number 123,456 while its OLE object is not...

Olivier






NickHK a écrit :

Olivier,
Because it depends how that cell is formatted. If you want a particular
format, you need to set it.
You can see this if you examine the .Value and .Text properties of your
range with different formatting.
If you really want the value as a string rather than a number set it to
"'123,456", otherwise set the number format.

NickHK

"oliviers" wrote in message
oups.com...
Ok, your samples are interesting.
But my goal is not to get the fastest way to copy data from VB6 to
Excel.

From VB6, just get a reference to the active worksheet and try the
following:

ExcelObject.Activesheet.Cells(1,1) = "123,456" while the decimal
separator is , (column)

My excel cell is transformed to 123456 ! He just did not care about
the decimal sep.

Olivier

NickHK a écrit :

Olivier,
If you can construct an array of you grid values, you can dump them all
together.
Dim arr(1 To 2, 1 To 2)
arr(1, 1) = 1
arr(1, 2) = 2
arr(2, 1) = 3
arr(2, 2) = 4
Range("A1").Resize(UBound(arr, 1), UBound(arr, 2)) = arr

Or if you a RS with thr grid data:
Range("A1").CopyFromRecordset

I do not see how string manipulation could be any faster than using
numbers.

NickHK


"oliviers" wrote in message
oups.com...
It would of course be easier.
What I'm just doing is an export of a datagrid from VB6 to Excel.
I found it's much more faster to build a string with my grid data and
paste it to excel than copy/pasting data row by row in Excel.

Olivier

NickHK a écrit :

Olivier,
Why not make it easier for yourself and just:
ExcelApp.ActiveSheet.Range("A1").Value=146876
Then the appearance will be determined by the format for that cell.

NickHK

wrote in message
ups.com...
Hi,

Here is the context of the issue I'm encountering:
- Windows decimal separator : , (column)
- Windows thousands sep : (blank)
- Excel set to use system separators.
- Excel version is Excel 2003

From VB6, I copy a number (146,876) to the clipboard (thus with a
decimal sep).
I then paste the number to the one cell in Excel using the following
code:
ExcelApp.ActiveSheet.Range("A1").Select
ExcelApp.ActiveSheet.Paste

The target cell will contain the number 146 876 (so no more decimal).
As the number is still in the clipboard, I switch to excel and hit the
Paste button. Ho miracle, the number is correctly pasted... If the
source number is something like 176,98 , it will be pasted as Text
and
thus left aligned.
I checked the excelapp variable in the watch window, the decimal
separator is correctly set to column.

Any idea?

Thanks,

Olivier