Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bad PasteValues Operation with date
Hi,
What I'm basically doing is filling an excel worksheet from a recordset. As cells might contain numbers as well as text. Once pasted to Excel, I pasteSpecial the sheet, multiplying cells by 1 to get numbers. I'm encountering a strange behavior though. Here is the small piece of code: Dim xl As New Excel.Application Dim ws As Worksheet xl.Workbooks.Add Set ws = xl.ActiveWorkbook.Sheets(1) xl.Visible = True ws.Range("A1") = "'MAY 2007" ws.Range("B1") = 1 ws.Range("B1").Copy ws.Range("A1").Select xl.Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlMultiply, SkipBlanks:=False, Transpose:=False As you see, A1= MAY 2007, format forced to be Text by using '. My concern is that after the pastespecial, A1=39203 which I guess is the numeric representation of MAY 2007(?). Doing the exact same stuff from Excel will produce the expected behavior: A1='MAY 2007 B1=1 Copy B1 Select A1 PasteSpecial Values - Action Multiply A1 is still MAY 2007 as expected. Would someone have any tip to workaround the problem. Thanks, Olivier |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bad PasteValues Operation with date
You don't have a real excel date
from ws.Range("A1") = "'MAY 2007" to ws.Range("A1") = datevalue("MAY 2007") You can also format the cell like this ws.Range("A1").Range("A1").NumberFormat = "MMMM YYYY" "oliviers" wrote: Hi, What I'm basically doing is filling an excel worksheet from a recordset. As cells might contain numbers as well as text. Once pasted to Excel, I pasteSpecial the sheet, multiplying cells by 1 to get numbers. I'm encountering a strange behavior though. Here is the small piece of code: Dim xl As New Excel.Application Dim ws As Worksheet xl.Workbooks.Add Set ws = xl.ActiveWorkbook.Sheets(1) xl.Visible = True ws.Range("A1") = "'MAY 2007" ws.Range("B1") = 1 ws.Range("B1").Copy ws.Range("A1").Select xl.Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlMultiply, SkipBlanks:=False, Transpose:=False As you see, A1= MAY 2007, format forced to be Text by using '. My concern is that after the pastespecial, A1=39203 which I guess is the numeric representation of MAY 2007(?). Doing the exact same stuff from Excel will produce the expected behavior: A1='MAY 2007 B1=1 Copy B1 Select A1 PasteSpecial Values - Action Multiply A1 is still MAY 2007 as expected. Would someone have any tip to workaround the problem. Thanks, Olivier |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bad PasteValues Operation with date
On Jun 30, 12:30*pm, Joel wrote:
You don't have a real excel date from ws.Range("A1") = "'MAY 2007" to ws.Range("A1") = datevalue("MAY 2007") You can also format the cell like this ws.Range("A1").Range("A1").NumberFormat = "MMMM YYYY" "oliviers" wrote: Hi, What I'm basically doing is filling an excel worksheet from a recordset. As cells might contain numbers as well as text. Once pasted to Excel, I pasteSpecial the sheet, multiplying cells by 1 to get numbers. I'm encountering a strange behavior though. Here is the small piece of code: Dim xl As New Excel.Application Dim ws As Worksheet xl.Workbooks.Add Set ws = xl.ActiveWorkbook.Sheets(1) xl.Visible = True ws.Range("A1") = "'MAY 2007" ws.Range("B1") = 1 ws.Range("B1").Copy ws.Range("A1").Select xl.Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlMultiply, SkipBlanks:=False, Transpose:=False As you see, A1= MAY 2007, format forced to be Text by using '. My concern is that after the pastespecial, A1=39203 which I guess is the numeric representation of MAY 2007(?). Doing the exact same stuff from Excel will produce the expected behavior: A1='MAY 2007 B1=1 Copy B1 Select A1 PasteSpecial Values - Action Multiply A1 is still MAY 2007 as expected. Would someone have any tip to workaround the problem. Thanks, Olivier I don't want to see it as a date. I just want to see it as text. The problem is that when I pastespecial the whole range mutliplying by 1, that particular cell will return numbers instead of text because (my guess) Excel is considering it a date (?). |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bad PasteValues Operation with date
Why do you care if it is a date or not? When it is a date you can still get
the text value by using Range("A1").Text. "oliviers" wrote: On Jun 30, 12:30 pm, Joel wrote: You don't have a real excel date from ws.Range("A1") = "'MAY 2007" to ws.Range("A1") = datevalue("MAY 2007") You can also format the cell like this ws.Range("A1").Range("A1").NumberFormat = "MMMM YYYY" "oliviers" wrote: Hi, What I'm basically doing is filling an excel worksheet from a recordset. As cells might contain numbers as well as text. Once pasted to Excel, I pasteSpecial the sheet, multiplying cells by 1 to get numbers. I'm encountering a strange behavior though. Here is the small piece of code: Dim xl As New Excel.Application Dim ws As Worksheet xl.Workbooks.Add Set ws = xl.ActiveWorkbook.Sheets(1) xl.Visible = True ws.Range("A1") = "'MAY 2007" ws.Range("B1") = 1 ws.Range("B1").Copy ws.Range("A1").Select xl.Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlMultiply, SkipBlanks:=False, Transpose:=False As you see, A1= MAY 2007, format forced to be Text by using '. My concern is that after the pastespecial, A1=39203 which I guess is the numeric representation of MAY 2007(?). Doing the exact same stuff from Excel will produce the expected behavior: A1='MAY 2007 B1=1 Copy B1 Select A1 PasteSpecial Values - Action Multiply A1 is still MAY 2007 as expected. Would someone have any tip to workaround the problem. Thanks, Olivier I don't want to see it as a date. I just want to see it as text. The problem is that when I pastespecial the whole range mutliplying by 1, that particular cell will return numbers instead of text because (my guess) Excel is considering it a date (?). |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bad PasteValues Operation with date
On Jun 30, 3:45*pm, Joel wrote:
Why do you care if it is a date or not? *When it is a date you can still get the text value by using Range("A1").Text. "oliviers" wrote: On Jun 30, 12:30 pm, Joel wrote: You don't have a real excel date from ws.Range("A1") = "'MAY 2007" to ws.Range("A1") = datevalue("MAY 2007") You can also format the cell like this ws.Range("A1").Range("A1").NumberFormat = "MMMM YYYY" "oliviers" wrote: Hi, What I'm basically doing is filling an excel worksheet from a recordset. As cells might contain numbers as well as text. Once pasted to Excel, I pasteSpecial the sheet, multiplying cells by 1 to get numbers. I'm encountering a strange behavior though. Here is the small piece of code: Dim xl As New Excel.Application Dim ws As Worksheet xl.Workbooks.Add Set ws = xl.ActiveWorkbook.Sheets(1) xl.Visible = True ws.Range("A1") = "'MAY 2007" ws.Range("B1") = 1 ws.Range("B1").Copy ws.Range("A1").Select xl.Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlMultiply, SkipBlanks:=False, Transpose:=False As you see, A1= MAY 2007, format forced to be Text by using '. My concern is that after the pastespecial, A1=39203 which I guess is the numeric representation of MAY 2007(?). Doing the exact same stuff from Excel will produce the expected behavior: A1='MAY 2007 B1=1 Copy B1 Select A1 PasteSpecial Values - Action Multiply A1 is still MAY 2007 as expected. Would someone have any tip to workaround the problem. Thanks, Olivier I don't want to see it as a date. I just want to see it as text. The problem is that when I pastespecial the whole range mutliplying by 1, that particular cell will return numbers instead of text because (my guess) Excel is considering it a date (?). That's right, I just don't care. I'm just trying to explain that when one cell contains "'MAY 2007", I'm getting a number when I apply a PasteSpecial - Multiply method while I'm expecting Excel to keep the text. Just run the sample I wrote from VB6, it will be clear. Olivier |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bad PasteValues Operation with date
The problem is the cell you are copying is in General Format. As I said
before, add ws.Range("A1").NumberFormat = "MMMM YYYY" Then when you copy the cell it will preserve the date format you require. Sometimes it doesn't pay to fight the quirks in excel, just try to work with the quirks! "oliviers" wrote: On Jun 30, 3:45 pm, Joel wrote: Why do you care if it is a date or not? When it is a date you can still get the text value by using Range("A1").Text. "oliviers" wrote: On Jun 30, 12:30 pm, Joel wrote: You don't have a real excel date from ws.Range("A1") = "'MAY 2007" to ws.Range("A1") = datevalue("MAY 2007") You can also format the cell like this ws.Range("A1").Range("A1").NumberFormat = "MMMM YYYY" "oliviers" wrote: Hi, What I'm basically doing is filling an excel worksheet from a recordset. As cells might contain numbers as well as text. Once pasted to Excel, I pasteSpecial the sheet, multiplying cells by 1 to get numbers. I'm encountering a strange behavior though. Here is the small piece of code: Dim xl As New Excel.Application Dim ws As Worksheet xl.Workbooks.Add Set ws = xl.ActiveWorkbook.Sheets(1) xl.Visible = True ws.Range("A1") = "'MAY 2007" ws.Range("B1") = 1 ws.Range("B1").Copy ws.Range("A1").Select xl.Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlMultiply, SkipBlanks:=False, Transpose:=False As you see, A1= MAY 2007, format forced to be Text by using '. My concern is that after the pastespecial, A1=39203 which I guess is the numeric representation of MAY 2007(?). Doing the exact same stuff from Excel will produce the expected behavior: A1='MAY 2007 B1=1 Copy B1 Select A1 PasteSpecial Values - Action Multiply A1 is still MAY 2007 as expected. Would someone have any tip to workaround the problem. Thanks, Olivier I don't want to see it as a date. I just want to see it as text. The problem is that when I pastespecial the whole range mutliplying by 1, that particular cell will return numbers instead of text because (my guess) Excel is considering it a date (?). That's right, I just don't care. I'm just trying to explain that when one cell contains "'MAY 2007", I'm getting a number when I apply a PasteSpecial - Multiply method while I'm expecting Excel to keep the text. Just run the sample I wrote from VB6, it will be clear. Olivier |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bad PasteValues Operation with date
On Jun 30, 4:17*pm, Joel wrote:
The problem is the cell you are copying is in General Format. *As I said before, add ws.Range("A1").NumberFormat = "MMMM YYYY" Then when you copy the cell it will preserve the date format you require. * Sometimes it doesn't pay to fight the quirks in excel, just try to work with the quirks! "oliviers" wrote: On Jun 30, 3:45 pm, Joel wrote: Why do you care if it is a date or not? *When it is a date you can still get the text value by using Range("A1").Text. "oliviers" wrote: On Jun 30, 12:30 pm, Joel wrote: You don't have a real excel date from ws.Range("A1") = "'MAY 2007" to ws.Range("A1") = datevalue("MAY 2007") You can also format the cell like this ws.Range("A1").Range("A1").NumberFormat = "MMMM YYYY" "oliviers" wrote: Hi, What I'm basically doing is filling an excel worksheet from a recordset. As cells might contain numbers as well as text. Once pasted to Excel, I pasteSpecial the sheet, multiplying cells by 1 to get numbers. I'm encountering a strange behavior though. Here is the small piece of code: Dim xl As New Excel.Application Dim ws As Worksheet xl.Workbooks.Add Set ws = xl.ActiveWorkbook.Sheets(1) xl.Visible = True ws.Range("A1") = "'MAY 2007" ws.Range("B1") = 1 ws.Range("B1").Copy ws.Range("A1").Select xl.Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlMultiply, SkipBlanks:=False, Transpose:=False As you see, A1= MAY 2007, format forced to be Text by using '. My concern is that after the pastespecial, A1=39203 which I guess is the numeric representation of MAY 2007(?). Doing the exact same stuff from Excel will produce the expected behavior: A1='MAY 2007 B1=1 Copy B1 Select A1 PasteSpecial Values - Action Multiply A1 is still MAY 2007 as expected. Would someone have any tip to workaround the problem. Thanks, Olivier I don't want to see it as a date. I just want to see it as text. The problem is that when I pastespecial the whole range mutliplying by 1, that particular cell will return numbers instead of text because (my guess) Excel is considering it a date (?). That's right, I just don't care. I'm just trying to explain that when one cell contains "'MAY 2007", I'm getting a number when I apply a PasteSpecial - Multiply method while I'm expecting Excel to keep the text. Just run the sample I wrote from VB6, it will be clear. Olivier Yep, but as I said, the 'MAY 2007 string in A1 is just a sample. I'm pasting recordsets my application is building on the fly. The recordset may contain figures, dates, strings, formulas: all built-in by the user at run time. So when pasting, I don't know in advance whether I'll have to deal with a date and where any potential date will be in my recordset. The most amazing is that if I do the stuff manually in Excel, I won't have any problem. Doing the exact same thing from VB6 causes a problem. Olivier |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bad PasteValues Operation with date
You want to format the cell when data is entered, not ay the time of the cut
and paste. You should know where dates are being entered and format the cells as dates. Then when you copy and paste a range of cells the format will be preserved. "oliviers" wrote: On Jun 30, 4:17 pm, Joel wrote: The problem is the cell you are copying is in General Format. As I said before, add ws.Range("A1").NumberFormat = "MMMM YYYY" Then when you copy the cell it will preserve the date format you require. Sometimes it doesn't pay to fight the quirks in excel, just try to work with the quirks! "oliviers" wrote: On Jun 30, 3:45 pm, Joel wrote: Why do you care if it is a date or not? When it is a date you can still get the text value by using Range("A1").Text. "oliviers" wrote: On Jun 30, 12:30 pm, Joel wrote: You don't have a real excel date from ws.Range("A1") = "'MAY 2007" to ws.Range("A1") = datevalue("MAY 2007") You can also format the cell like this ws.Range("A1").Range("A1").NumberFormat = "MMMM YYYY" "oliviers" wrote: Hi, What I'm basically doing is filling an excel worksheet from a recordset. As cells might contain numbers as well as text. Once pasted to Excel, I pasteSpecial the sheet, multiplying cells by 1 to get numbers. I'm encountering a strange behavior though. Here is the small piece of code: Dim xl As New Excel.Application Dim ws As Worksheet xl.Workbooks.Add Set ws = xl.ActiveWorkbook.Sheets(1) xl.Visible = True ws.Range("A1") = "'MAY 2007" ws.Range("B1") = 1 ws.Range("B1").Copy ws.Range("A1").Select xl.Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlMultiply, SkipBlanks:=False, Transpose:=False As you see, A1= MAY 2007, format forced to be Text by using '. My concern is that after the pastespecial, A1=39203 which I guess is the numeric representation of MAY 2007(?). Doing the exact same stuff from Excel will produce the expected behavior: A1='MAY 2007 B1=1 Copy B1 Select A1 PasteSpecial Values - Action Multiply A1 is still MAY 2007 as expected. Would someone have any tip to workaround the problem. Thanks, Olivier I don't want to see it as a date. I just want to see it as text. The problem is that when I pastespecial the whole range mutliplying by 1, that particular cell will return numbers instead of text because (my guess) Excel is considering it a date (?). That's right, I just don't care. I'm just trying to explain that when one cell contains "'MAY 2007", I'm getting a number when I apply a PasteSpecial - Multiply method while I'm expecting Excel to keep the text. Just run the sample I wrote from VB6, it will be clear. Olivier Yep, but as I said, the 'MAY 2007 string in A1 is just a sample. I'm pasting recordsets my application is building on the fly. The recordset may contain figures, dates, strings, formulas: all built-in by the user at run time. So when pasting, I don't know in advance whether I'll have to deal with a date and where any potential date will be in my recordset. The most amazing is that if I do the stuff manually in Excel, I won't have any problem. Doing the exact same thing from VB6 causes a problem. Olivier |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bad PasteValues Operation with date
On Jul 1, 12:10*am, Joel wrote:
You want to format the cell when data is entered, not ay the time of the cut and paste. *You should know where dates are being entered and format the cells as dates. *Then when you copy and paste a range of cells the format will be preserved. "oliviers" wrote: On Jun 30, 4:17 pm, Joel wrote: The problem is the cell you are copying is in General Format. *As I said before, add ws.Range("A1").NumberFormat = "MMMM YYYY" Then when you copy the cell it will preserve the date format you require. * Sometimes it doesn't pay to fight the quirks in excel, just try to work with the quirks! "oliviers" wrote: On Jun 30, 3:45 pm, Joel wrote: Why do you care if it is a date or not? *When it is a date you can still get the text value by using Range("A1").Text. "oliviers" wrote: On Jun 30, 12:30 pm, Joel wrote: You don't have a real excel date from ws.Range("A1") = "'MAY 2007" to ws.Range("A1") = datevalue("MAY 2007") You can also format the cell like this ws.Range("A1").Range("A1").NumberFormat = "MMMM YYYY" "oliviers" wrote: Hi, What I'm basically doing is filling an excel worksheet from a recordset. As cells might contain numbers as well as text. Once pasted to Excel, IpasteSpecialthe sheet, multiplying cells by 1 to get numbers. I'm encountering a strange behavior though. Here is the small piece of code: Dim xl As New Excel.Application Dim ws As Worksheet xl.Workbooks.Add Set ws = xl.ActiveWorkbook.Sheets(1) xl.Visible = True ws.Range("A1") = "'MAY 2007" ws.Range("B1") = 1 ws.Range("B1").Copy ws.Range("A1").Select xl.Selection.PasteSpecialPaste:=xlPasteValues, Operation:=xlMultiply, SkipBlanks:=False, Transpose:=False As you see, A1= MAY 2007, format forced to be Text by using '. My concern is that after thepastespecial, A1=39203 which I guess is the numeric representation of MAY 2007(?). Doing the exact same stuff from Excel will produce the expected behavior: A1='MAY 2007 B1=1 Copy B1 Select A1 PasteSpecialValues - Action Multiply A1 is still MAY 2007 as expected. Would someone have any tip to workaround the problem. Thanks, Olivier I don't want to see it as a date. I just want to see it as text. The problem is that when Ipastespecialthe whole range mutliplying by 1, that particular cell will return numbers instead of text because (my guess) Excel is considering it a date (?). That's right, I just don't care. I'm just trying to explain that when one cell contains "'MAY 2007", I'm getting a number when I apply aPasteSpecial- Multiply method while I'm expecting Excel to keep the text. Just run the sample I wrote from VB6, it will be clear. Olivier Yep, but as I said, the 'MAY 2007 string in A1 is just a sample. I'm pasting recordsets my application is building on the fly. The recordset may contain figures, dates, strings, formulas: all built-in by the user at run time. So when pasting, I don't know in advance whether I'll have to deal with a date and where any potential date will be in my recordset. The most amazing is that if I do the stuff manually in Excel, I won't have any problem. Doing the exact same thing from VB6 causes a problem. Olivier The point is just that I don't want to format it as a date. In my scenario, it is not a date, it is a string having the value 'MAY 2007. It must be considered as string. The problem is that Excel is considering it as a date while it shouldn't. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bad PasteValues Operation with date
The format the cell as text before writing the data. the problem still is
the cell is in a genreal format and you need to change the format to something else. ws.Range("A1").NumberFormat = "@" ws.Range("A1").text ='MAY 2007 "oliviers" wrote: On Jul 1, 12:10 am, Joel wrote: You want to format the cell when data is entered, not ay the time of the cut and paste. You should know where dates are being entered and format the cells as dates. Then when you copy and paste a range of cells the format will be preserved. "oliviers" wrote: On Jun 30, 4:17 pm, Joel wrote: The problem is the cell you are copying is in General Format. As I said before, add ws.Range("A1").NumberFormat = "MMMM YYYY" Then when you copy the cell it will preserve the date format you require. Sometimes it doesn't pay to fight the quirks in excel, just try to work with the quirks! "oliviers" wrote: On Jun 30, 3:45 pm, Joel wrote: Why do you care if it is a date or not? When it is a date you can still get the text value by using Range("A1").Text. "oliviers" wrote: On Jun 30, 12:30 pm, Joel wrote: You don't have a real excel date from ws.Range("A1") = "'MAY 2007" to ws.Range("A1") = datevalue("MAY 2007") You can also format the cell like this ws.Range("A1").Range("A1").NumberFormat = "MMMM YYYY" "oliviers" wrote: Hi, What I'm basically doing is filling an excel worksheet from a recordset. As cells might contain numbers as well as text. Once pasted to Excel, IpasteSpecialthe sheet, multiplying cells by 1 to get numbers. I'm encountering a strange behavior though. Here is the small piece of code: Dim xl As New Excel.Application Dim ws As Worksheet xl.Workbooks.Add Set ws = xl.ActiveWorkbook.Sheets(1) xl.Visible = True ws.Range("A1") = "'MAY 2007" ws.Range("B1") = 1 ws.Range("B1").Copy ws.Range("A1").Select xl.Selection.PasteSpecialPaste:=xlPasteValues, Operation:=xlMultiply, SkipBlanks:=False, Transpose:=False As you see, A1= MAY 2007, format forced to be Text by using '. My concern is that after thepastespecial, A1=39203 which I guess is the numeric representation of MAY 2007(?). Doing the exact same stuff from Excel will produce the expected behavior: A1='MAY 2007 B1=1 Copy B1 Select A1 PasteSpecialValues - Action Multiply A1 is still MAY 2007 as expected. Would someone have any tip to workaround the problem. Thanks, Olivier I don't want to see it as a date. I just want to see it as text. The problem is that when Ipastespecialthe whole range mutliplying by 1, that particular cell will return numbers instead of text because (my guess) Excel is considering it a date (?). That's right, I just don't care. I'm just trying to explain that when one cell contains "'MAY 2007", I'm getting a number when I apply aPasteSpecial- Multiply method while I'm expecting Excel to keep the text. Just run the sample I wrote from VB6, it will be clear. Olivier Yep, but as I said, the 'MAY 2007 string in A1 is just a sample. I'm pasting recordsets my application is building on the fly. The recordset may contain figures, dates, strings, formulas: all built-in by the user at run time. So when pasting, I don't know in advance whether I'll have to deal with a date and where any potential date will be in my recordset. The most amazing is that if I do the stuff manually in Excel, I won't have any problem. Doing the exact same thing from VB6 causes a problem. Olivier The point is just that I don't want to format it as a date. In my scenario, it is not a date, it is a string having the value 'MAY 2007. It must be considered as string. The problem is that Excel is considering it as a date while it shouldn't. |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bad PasteValues Operation with date
On Jul 1, 12:10*am, Joel wrote:
You want to format the cell when data is entered, not ay the time of the cut and paste. *You should know where dates are being entered and format the cells as dates. *Then when you copy and paste a range of cells the format will be preserved. "oliviers" wrote: On Jun 30, 4:17 pm, Joel wrote: The problem is the cell you are copying is in General Format. *As I said before, add ws.Range("A1").NumberFormat = "MMMM YYYY" Then when you copy the cell it will preserve the date format you require. * Sometimes it doesn't pay to fight the quirks in excel, just try to work with the quirks! "oliviers" wrote: On Jun 30, 3:45 pm, Joel wrote: Why do you care if it is a date or not? *When it is a date you can still get the text value by using Range("A1").Text. "oliviers" wrote: On Jun 30, 12:30 pm, Joel wrote: You don't have a real excel date from ws.Range("A1") = "'MAY 2007" to ws.Range("A1") = datevalue("MAY 2007") You can also format the cell like this ws.Range("A1").Range("A1").NumberFormat = "MMMM YYYY" "oliviers" wrote: Hi, What I'm basically doing is filling an excel worksheet from a recordset. As cells might contain numbers as well as text. Once pasted to Excel, IpasteSpecialthe sheet, multiplying cells by 1 to get numbers. I'm encountering a strange behavior though. Here is the small piece of code: Dim xl As New Excel.Application Dim ws As Worksheet xl.Workbooks.Add Set ws = xl.ActiveWorkbook.Sheets(1) xl.Visible = True ws.Range("A1") = "'MAY 2007" ws.Range("B1") = 1 ws.Range("B1").Copy ws.Range("A1").Select xl.Selection.PasteSpecialPaste:=xlPasteValues, Operation:=xlMultiply, SkipBlanks:=False, Transpose:=False As you see, A1= MAY 2007, format forced to be Text by using '. My concern is that after thepastespecial, A1=39203 which I guess is the numeric representation of MAY 2007(?). Doing the exact same stuff from Excel will produce the expected behavior: A1='MAY 2007 B1=1 Copy B1 Select A1 PasteSpecialValues - Action Multiply A1 is still MAY 2007 as expected. Would someone have any tip to workaround the problem. Thanks, Olivier I don't want to see it as a date. I just want to see it as text. The problem is that when Ipastespecialthe whole range mutliplying by 1, that particular cell will return numbers instead of text because (my guess) Excel is considering it a date (?). That's right, I just don't care. I'm just trying to explain that when one cell contains "'MAY 2007", I'm getting a number when I apply aPasteSpecial- Multiply method while I'm expecting Excel to keep the text. Just run the sample I wrote from VB6, it will be clear. Olivier Yep, but as I said, the 'MAY 2007 string in A1 is just a sample. I'm pasting recordsets my application is building on the fly. The recordset may contain figures, dates, strings, formulas: all built-in by the user at run time. So when pasting, I don't know in advance whether I'll have to deal with a date and where any potential date will be in my recordset. The most amazing is that if I do the stuff manually in Excel, I won't have any problem. Doing the exact same thing from VB6 causes a problem. Olivier That's just impossible. I don't want to find another way of pasting my information, it's the best way. What I want to understand is why the code would just work when I run it from the Excel VBA editor and won't work when running the exact same code from VB6 using OLE. If my user is willing to paste the STRING MAY2007 in a cell and make excel consider it as a String, not a DATE, it should be possible. My application is a custom business intelligence solution, users are building their cubes on the fly. They can generate values using formulas, a cell can thus contain ... anything. It doesn't make sense to me to try to figure out which cell is a date or not. That dummy behavior of Excel is the problem. |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bad PasteValues Operation with date
I don't work for Microsoft so don't blame me for its quirks!!!
I went back and read all your postings because I was getting a little confused between "running manually", running it in excel 2003, using VB6, and in excell 2007. You seem to be all over the place. I initiallly thought it was working on the spreadsheet, but not running in VBA code in the same version of excel. Have you gotten it to working code in any version of Visual Basic/Excel? VB6 I believe is very similar to Excel 2003. I would also think 2007 is a different animal with many problems. I curious how you are opening the file (or declaring an excel object). I'm also wondering if the problem is the same with all versions ovf VB. I still think the correct way of solving the problem is avoiding a "General format" in the cell where the Date is entered. It seem the General format recognizes the Date and converts it to a Serial Date. "oliviers" wrote: On Jul 1, 12:10 am, Joel wrote: You want to format the cell when data is entered, not ay the time of the cut and paste. You should know where dates are being entered and format the cells as dates. Then when you copy and paste a range of cells the format will be preserved. "oliviers" wrote: On Jun 30, 4:17 pm, Joel wrote: The problem is the cell you are copying is in General Format. As I said before, add ws.Range("A1").NumberFormat = "MMMM YYYY" Then when you copy the cell it will preserve the date format you require. Sometimes it doesn't pay to fight the quirks in excel, just try to work with the quirks! "oliviers" wrote: On Jun 30, 3:45 pm, Joel wrote: Why do you care if it is a date or not? When it is a date you can still get the text value by using Range("A1").Text. "oliviers" wrote: On Jun 30, 12:30 pm, Joel wrote: You don't have a real excel date from ws.Range("A1") = "'MAY 2007" to ws.Range("A1") = datevalue("MAY 2007") You can also format the cell like this ws.Range("A1").Range("A1").NumberFormat = "MMMM YYYY" "oliviers" wrote: Hi, What I'm basically doing is filling an excel worksheet from a recordset. As cells might contain numbers as well as text. Once pasted to Excel, IpasteSpecialthe sheet, multiplying cells by 1 to get numbers. I'm encountering a strange behavior though. Here is the small piece of code: Dim xl As New Excel.Application Dim ws As Worksheet xl.Workbooks.Add Set ws = xl.ActiveWorkbook.Sheets(1) xl.Visible = True ws.Range("A1") = "'MAY 2007" ws.Range("B1") = 1 ws.Range("B1").Copy ws.Range("A1").Select xl.Selection.PasteSpecialPaste:=xlPasteValues, Operation:=xlMultiply, SkipBlanks:=False, Transpose:=False As you see, A1= MAY 2007, format forced to be Text by using '. My concern is that after thepastespecial, A1=39203 which I guess is the numeric representation of MAY 2007(?). Doing the exact same stuff from Excel will produce the expected behavior: A1='MAY 2007 B1=1 Copy B1 Select A1 PasteSpecialValues - Action Multiply A1 is still MAY 2007 as expected. Would someone have any tip to workaround the problem. Thanks, Olivier I don't want to see it as a date. I just want to see it as text. The problem is that when Ipastespecialthe whole range mutliplying by 1, that particular cell will return numbers instead of text because (my guess) Excel is considering it a date (?). That's right, I just don't care. I'm just trying to explain that when one cell contains "'MAY 2007", I'm getting a number when I apply aPasteSpecial- Multiply method while I'm expecting Excel to keep the text. Just run the sample I wrote from VB6, it will be clear. Olivier Yep, but as I said, the 'MAY 2007 string in A1 is just a sample. I'm pasting recordsets my application is building on the fly. The recordset may contain figures, dates, strings, formulas: all built-in by the user at run time. So when pasting, I don't know in advance whether I'll have to deal with a date and where any potential date will be in my recordset. The most amazing is that if I do the stuff manually in Excel, I won't have any problem. Doing the exact same thing from VB6 causes a problem. Olivier That's just impossible. I don't want to find another way of pasting my information, it's the best way. What I want to understand is why the code would just work when I run it from the Excel VBA editor and won't work when running the exact same code from VB6 using OLE. If my user is willing to paste the STRING MAY2007 in a cell and make excel consider it as a String, not a DATE, it should be possible. My application is a custom business intelligence solution, users are building their cubes on the fly. They can generate values using formulas, a cell can thus contain ... anything. It doesn't make sense to me to try to figure out which cell is a date or not. That dummy behavior of Excel is the problem. |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bad PasteValues Operation with date
On Jul 8, 6:40*pm, Joel wrote:
I don't work for Microsoft so don't blame me for its quirks!!! I went back and read all your postings because I was getting a little confused between "running manually", running it in excel 2003, using VB6, and in excell 2007. *You seem to be all over the place. I initiallly thought it was working on the spreadsheet, but not running in VBA code in the same version of excel. *Have you gotten it to working code in any version of Visual Basic/Excel? VB6 I believe is very similar to Excel 2003. *I would also think 2007 is a different animal with many problems. *I curious how you are opening the file (or declaring an excel object). *I'm also wondering if the problem is the same with all versions ovf VB. I still think the correct way of solving the problem is avoiding a "General format" in the cell where the Date is entered. *It seem the General format recognizes the Date and converts it to a Serial Date. "oliviers" wrote: On Jul 1, 12:10 am, Joel wrote: You want to format the cell when data is entered, not ay the time of the cut and paste. *You should know where dates are being entered and format the cells as dates. *Then when you copy and paste a range of cells the format will be preserved. "oliviers" wrote: On Jun 30, 4:17 pm, Joel wrote: The problem is the cell you are copying is in General Format. *As I said before, add ws.Range("A1").NumberFormat = "MMMM YYYY" Then when you copy the cell it will preserve the date format you require. * Sometimes it doesn't pay to fight the quirks in excel, just try to work with the quirks! "oliviers" wrote: On Jun 30, 3:45 pm, Joel wrote: Why do you care if it is a date or not? *When it is a date you can still get the text value by using Range("A1").Text. "oliviers" wrote: On Jun 30, 12:30 pm, Joel wrote: You don't have a real excel date from ws.Range("A1") = "'MAY 2007" to ws.Range("A1") = datevalue("MAY 2007") You can also format the cell like this ws.Range("A1").Range("A1").NumberFormat = "MMMM YYYY" "oliviers" wrote: Hi, What I'm basically doing is filling an excel worksheet from a recordset. As cells might contain numbers as well as text. Once pasted to Excel, IpasteSpecialthe sheet, multiplying cells by 1 to get numbers. I'm encountering a strange behavior though. Here is the small piece of code: Dim xl As New Excel.Application Dim ws As Worksheet xl.Workbooks.Add Set ws = xl.ActiveWorkbook.Sheets(1) xl.Visible = True ws.Range("A1") = "'MAY 2007" ws.Range("B1") = 1 ws.Range("B1").Copy ws.Range("A1").Select xl.Selection.PasteSpecialPaste:=xlPasteValues, Operation:=xlMultiply, SkipBlanks:=False, Transpose:=False As you see, A1= MAY 2007, format forced to be Text by using '. My concern is that after thepastespecial, A1=39203 which I guess is the numeric representation of MAY 2007(?). Doing the exact same stuff from Excel will produce the expected behavior: A1='MAY 2007 B1=1 Copy B1 Select A1 PasteSpecialValues - Action Multiply A1 is still MAY 2007 as expected. Would someone have any tip to workaround the problem. Thanks, Olivier I don't want to see it as a date. I just want to see it as text. The problem is that when Ipastespecialthe whole range mutliplying by 1, that particular cell will return numbers instead of text because (my guess) Excel is considering it a date (?). That's right, I just don't care. I'm just trying to explain that when one cell contains "'MAY 2007", I'm getting a number when I apply aPasteSpecial- Multiply method while I'm expecting Excel to keep the text. Just run the sample I wrote from VB6, it will be clear. Olivier Yep, but as I said, the 'MAY 2007 string in A1 is just a sample. I'm pasting recordsets my application is building on the fly. The recordset may contain figures, dates, strings, formulas: all built-in by the user at run time. So when pasting, I don't know in advance whether I'll have to deal with a date and where any potential date will be in my recordset. The most amazing is that if I do the stuff manually in Excel, I won't have any problem. Doing the exact same thing from VB6 causes a problem. Olivier That's just impossible. I don't want to find another way of pasting my information, it's the best way. What I want to understand is why the code would just work when I run it from the Excel VBA editor and won't work when running the exact same code from VB6 using OLE. If my user is willing to paste the STRING MAY2007 in a cell and make excel consider it as a String, not a DATE, it should be possible. My application is a custom business intelligence solution, users are building their cubes on the fly. They can generate values using formulas, a cell can thus contain ... anything. It doesn't make sense to me to try to figure out which cell is a date or not. That dummy behavior of Excel is the problem. Don't worry, I don't blame you ;-) You might be right by avoiding the General format but as users might send "anything" to excel, it makes sense to me. When they send values for the caption of columns, it can be anything like 'LAST MONTH' or 'LAST YEAR' or 'MAY 2007'. Our system is so neat and generic that I can't browse every cell to figure out which format is the best. I'm only working with Office 2003, not 2007. When I said it's working manually, I just tested and recorded a macro doing what i do whithin my VB6 code: 1. Put 'MAY 2007 (with ' to force a string) in A1 2. Put 1 in A2 3. Copy A2 4. Select A1 5. PasteSpecial, Values and Multiply. -- 'MAY 2007 is kept Doing the same by reference xl as excel.application will just convert MAY 2007 in serial date. Seems there is no workaround ... Thanks anyway. Olivier |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
.Copy Destination:= << Forget when PasteValues is desired | Excel Discussion (Misc queries) | |||
Change cut/paste operation to cut/insert operation | Excel Programming | |||
Conditional formatting, pastevalues, clearcontents | Excel Discussion (Misc queries) | |||
what does $ operation mean | New Users to Excel | |||
Operation on Ranges | Excel Programming |