Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA Copy Paste Issue | Excel Discussion (Misc queries) | |||
Copy & Paste Issue | Excel Discussion (Misc queries) | |||
Copy Paste issue | Excel Worksheet Functions | |||
Excel Copy and Paste Issue | Excel Programming | |||
Issue with copy & paste? | Excel Discussion (Misc queries) |