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.