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