Bad PasteValues Operation with date

[oliviers]

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

 

[Joel]

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]

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"

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 (?).

 

[Joel]

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]

Why do you care if it is a date or not?  When it is a date you can stillget
the text value by using Range("A1").Text.

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

 

[Joel]

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]

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!

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

 

[Joel]

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]

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.

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.

 

[Joel]

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]

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.

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.

 

[Joel]

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]

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 thefile
(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.

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