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

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
>

On Jun 30, 12:30*pm, Joel <J...@discussions.microsoft.com> 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 (?).

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 <J...@discussions.microsoft.com> 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 (?).
>

On Jun 30, 3:45*pm, Joel <J...@discussions.microsoft.com> wrote:
> 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.
>
> "oliviers" wrote:
> > On Jun 30, 12:30 pm, Joel <J...@discussions.microsoft.com> 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 by1
> > > > 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

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 <J...@discussions.microsoft.com> 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 <J...@discussions.microsoft.com> 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
>

On Jun 30, 4:17*pm, Joel <J...@discussions.microsoft.com> 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 <J...@discussions.microsoft.com> 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 <J...@discussions.microsoft.com> 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

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

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 <J...@discussions.microsoft.com> 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 <J...@discussions.microsoft.com> 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 <J...@discussions.microsoft.com> 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
>
> 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
>
>

On Jul 1, 12:10*am, Joel <J...@discussions.microsoft.com> 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 <J...@discussions.microsoft.com> wrote:
> > > The problem is the cell you are copying is in General Format. *As Isaid
> > > 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 <J...@discussions.microsoft.com> 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 <J...@discussions.microsoft.com> 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 froma
> > > > > > > > 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.

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 <J...@discussions.microsoft.com> 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 <J...@discussions.microsoft.com> 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 <J...@discussions.microsoft.com> 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 <J...@discussions.microsoft.com> 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.
>