asked on

export data to excel

Hi,

I exported data from a flexgrid to an excel-sheet. Now I have another problem. The data in the flexgrid are not recognized as doubles in excel. All the data with a comma, are recognized as strings. How can I change this? So how can I make excel recognize my data with comma's as normal data.

this is the code:

Set ApExcel = CreateObject("Excel.application") 'Creates an object
ApExcel.Visible = True ' So you can see Excel
ApExcel.Workbooks.Add 'Adds a new book.
For x = 1 To aantal
For y = 1 To aantal
ApExcel.selection.numberformat = ""
ApExcel.Cells(x, y).Value = MSFlexGrid1.TextMatrix(x, y) 'Add Text to a Cell
'You can use the line above, changing coordenates to go to any
'cell and you can also add Formulas
Next
Next

greetz,
yoske

supunr

how about...

ApExcel.Cells(x, y).Value = MSFlexGrid1.TextMatrix(x, y) 'Add Text to a Cell

to

ApExcel.Cells(x, y).Value = Format(MSFlexGrid1.TextMatrix(x, y)) 'Add Text to a Cell

Good Luck!

yoske1

ASKER

ok, I found it already, this is the code:

ApExcel.Cells(x, y).Value = CDbl(MSFlexGrid1.TextMatrix(x, y))

yoske1

ASKER

tx for the effort, I tried it too, but yours doesn't work in my program supunr, sorry

supunr

mmm....it seems to be working fine for me even with out the Format function. What version of Excel do you have?

ASKER CERTIFIED SOLUTION

supunr

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

yoske1

ASKER

tried it, but doesnt work, but I will accept your answer for the efforts.

Cimperiali

It seems as if a bug is in Excel. You can make this experiment:
create a txt file with three lines:
1,01 1.000
1,1 1.000,1
1000,1
now open excel and import it as tabbed separated file.
Everithing seems fine.
Now close it. Change extension of txt file to xls, and doubvleclick it.
Excel will open it, but you will see spomething strange:
some numbers (thoose with Dot in) looses decimals or they are converted to string format...