Prev: Displaying PDF in VB6 Form
Next: WM_CLICK
From: Arthur Fouls on 1 Sep 2005 18:40
Hi All,

This is kind of in relation to my earlier question but slighly different.

In the mshflexgrid i have now managed to get my dates showing correctly in
the dd/mm/yy format
( thank you BOB! )

But the code i use to export all the data to my excel spreadsheet now seems
to have gone AWOL, or something has.

The code i use goes over each cell and should duplicate that cell to an
excel cell, the code is.

With MSHFlexGrid1
For i = 0 To .Rows - 1
For j = 0 To .Cols - 1
.Row = i
.Col = j
xlWs.Cells(i + 1, j + 1) = .Text
'' If j = 13 Then MsgBox .Text '' checks what text the code
is picking up
Next
Next
End With

When the code above locks on to one of the dates say 01/09/05 the msgbox
does return 01/09/05, but when it goes into the excel spreadsheet it puts
in 09/01/05 Arrhhh!!

Am i missing something in either my code or excel to tell it i want it
exported exactly how it is shown in the grid?

i have checked my excel sheet and it was formatted general, then text, now
date and in format dd/mm/yy but no joy?

any ideas anyone please???

Thanks so much

Arthur



From: Bob Butler on 1 Sep 2005 20:19
"Arthur Fouls" <arthur(a)fouls.com> wrote in message
news:drLRe.63806$w74.41759(a)fe03.news.easynews.com
> Hi All,
>
> This is kind of in relation to my earlier question but slighly
> different.
>
> In the mshflexgrid i have now managed to get my dates showing
> correctly in the dd/mm/yy format
> ( thank you BOB! )
>
> But the code i use to export all the data to my excel spreadsheet now
> seems to have gone AWOL, or something has.
>
> The code i use goes over each cell and should duplicate that cell to
> an excel cell, the code is.
>
> With MSHFlexGrid1
> For i = 0 To .Rows - 1
> For j = 0 To .Cols - 1
> .Row = i
> .Col = j
> xlWs.Cells(i + 1, j + 1) = .Text
> '' If j = 13 Then MsgBox .Text '' checks what text
> the code is picking up
> Next
> Next
> End With
>
> When the code above locks on to one of the dates say 01/09/05 the
> msgbox does return 01/09/05, but when it goes into the excel
> spreadsheet it puts in 09/01/05 Arrhhh!!
>
> Am i missing something in either my code or excel to tell it i want it
> exported exactly how it is shown in the grid?
>
> i have checked my excel sheet and it was formatted general, then
> text, now date and in format dd/mm/yy but no joy?

Excel is going to attempt to interpret dates and display them using the
format defined for the cell.

a couple of things to try:

With xlWs.Cells(i + 1, j + 1)
.NumberFormat = "@"
.Value = .Text
End With

or maybe

With xlWs.Cells(i + 1, j + 1)
.NumberFormat = "dd/mm/yyyy"
.Value = .Text
End With

--
Reply to the group so all can participate
VB.Net: "Fool me once..."

From: Arthur Fouls on 1 Sep 2005 20:26

"Bob Butler" <tiredofit(a)nospam.com> wrote in message
news:eOE2GQ1rFHA.528(a)TK2MSFTNGP09.phx.gbl...
> "Arthur Fouls" <arthur(a)fouls.com> wrote in message
> news:drLRe.63806$w74.41759(a)fe03.news.easynews.com
> > Hi All,
> >
> > This is kind of in relation to my earlier question but slighly
> > different.
> >
> > In the mshflexgrid i have now managed to get my dates showing
> > correctly in the dd/mm/yy format
> > ( thank you BOB! )
> >
> > But the code i use to export all the data to my excel spreadsheet now
> > seems to have gone AWOL, or something has.
> >
> > The code i use goes over each cell and should duplicate that cell to
> > an excel cell, the code is.
> >
> > With MSHFlexGrid1
> > For i = 0 To .Rows - 1
> > For j = 0 To .Cols - 1
> > .Row = i
> > .Col = j
> > xlWs.Cells(i + 1, j + 1) = .Text
> > '' If j = 13 Then MsgBox .Text '' checks what text
> > the code is picking up
> > Next
> > Next
> > End With
> >
> > When the code above locks on to one of the dates say 01/09/05 the
> > msgbox does return 01/09/05, but when it goes into the excel
> > spreadsheet it puts in 09/01/05 Arrhhh!!
> >
> > Am i missing something in either my code or excel to tell it i want it
> > exported exactly how it is shown in the grid?
> >
> > i have checked my excel sheet and it was formatted general, then
> > text, now date and in format dd/mm/yy but no joy?
>
> Excel is going to attempt to interpret dates and display them using the
> format defined for the cell.
>
> a couple of things to try:
>
> With xlWs.Cells(i + 1, j + 1)
> .NumberFormat = "@"
> .Value = .Text
> End With
>
> or maybe
>
> With xlWs.Cells(i + 1, j + 1)
> .NumberFormat = "dd/mm/yyyy"
> .Value = .Text
> End With
>
> --
> Reply to the group so all can participate
> VB.Net: "Fool me once..."
>

Top man, will have a bash at this tmrw, i am sure one of them will work,
your code and help seem some of the best!

Thank very much for all your help, much apprieicated.

Arthur.


From: Rick Rothstein [MVP - Visual Basic] on 2 Sep 2005 00:27
> But the code i use to export all the data to my excel spreadsheet now
seems
> to have gone AWOL, or something has.
>
> The code i use goes over each cell and should duplicate that cell to
an
> excel cell, the code is.
>
> With MSHFlexGrid1
> For i = 0 To .Rows - 1
> For j = 0 To .Cols - 1
> .Row = i
> .Col = j
> xlWs.Cells(i + 1, j + 1) = .Text


Instead of using the slow method of moving the active cell around (using
Row and Col) in order to get to the text in each cell, use the
TextMatrix property to get to the text without having to move cell
focus. Replace the last 3 lines above with this single line...

xlWs.Cells(i + 1, J + 1) = .TextMatrix(i, j)

Rick


 | 
Pages: 1
Prev: Displaying PDF in VB6 Form
Next: WM_CLICK