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 |
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 |