MSFlexgrid to Excel save numbers as text - Xtreme Visual Basic Talk

01-24-2005, 11:50 AM

shezan74

Newcomer

Join Date: Jan 2005

Posts: 2

MSFlexgrid to Excel save numbers as text

Hi there, i've a problem with a small apps i'm working on.
I use this code to move a 2D MSFlexGrid to an excel file:

Code:

For i = 0 To MSFlexGrid1.Rows - 1
   For j = 0 To MSFlexGrid1.Cols - 1
      Spreadsheet1.Cells(i + 1, j + 1) = MSFlexGrid1.TextMatrix(i, j)
   Next
Next

Spreadsheet1.Columns.AutoFit

nfile = FreeFile
Open "filea" For Output As #nfile
Close #nfile
FileCopy "filea", App.Path & CStr(date) & ".xls"
Spreadsheet1.Export (App.Path & CStr(date) & ".xls")

All is working very well, except for the fact that i have some columns filled with numbers in my MSFlexGrid and those numbers, once moved to excel file, are stored as STRINGS and it's impossible to convert those to number again except by using a trick with copy "1" then paste special on all interested columns with "multiply" option...
I've searched all MS kb but i can't find a way to get rid of this problem...

Someone can please save me?

Last edited by shezan74; 01-24-2005 at 11:57 AM.

01-24-2005, 03:04 PM

herilane

Unashamed geek

Retired Moderator
* Expert *

Join Date: Jul 2003

Location: London, England

Posts: 8,988

Change the cell format to General, and then re-enter those numbers.

Code:

Selection.NumberFormat = "General"
Selection.Formula = Selection.Formula

01-24-2005, 03:10 PM

Flyguy

Lost Soul

Super Moderator
* Guru *

Join Date: May 2001

Location: Vorlon

Posts: 18,884

Some other method, which is also better when working with Excel 2003 (to prevent those green markers in the cells):

Code:

For i = 0 To MSFlexGrid1.Rows - 1
   For j = 0 To MSFlexGrid1.Cols - 1
      If IsNumeric(MSFlexGrid1.TextMatrix(i, j)) Then
        Spreadsheet1.Cells(i + 1, j + 1) = CDbl(MSFlexGrid1.TextMatrix(i, j))
      Else
        Spreadsheet1.Cells(i + 1, j + 1) = MSFlexGrid1.TextMatrix(i, j)
      End If
   Next
Next

01-25-2005, 01:16 AM

shezan74

Newcomer

Join Date: Jan 2005

Posts: 2

Quote:

Originally Posted by Flyguy

Some other method, which is also better when working with Excel 2003 (to prevent those green markers in the cells):

Code:

For i = 0 To MSFlexGrid1.Rows - 1
   For j = 0 To MSFlexGrid1.Cols - 1
      If IsNumeric(MSFlexGrid1.TextMatrix(i, j)) Then
        Spreadsheet1.Cells(i + 1, j + 1) = CDbl(MSFlexGrid1.TextMatrix(i, j))
      Else
        Spreadsheet1.Cells(i + 1, j + 1) = MSFlexGrid1.TextMatrix(i, j)
      End If
   Next
Next

Astonishing and exceptionally simple

Thank you a lot (this was my first attempt to make vb talk with MSOffice

)
Thank you