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.