Formating data - exporting to Excel

[PIAS]

Hi!

I'm exporting data from some grid to excel.

If i have value in grid like; 2.57 or 10.98,
excel converts these values into: feb.57 and okt.98 !

i don't want excel to convert these values

Is there any way to switch off formating in excel when exporting?

i'm working with a VB script, this is a code:

sub izvoziExcel

Dim sHTML,oExcel,oBook,oCells

naslovR=document.getElementById("naslovReklame").Value
if naslovR<>"" then
vsebinaR=document.all.item("tblReklame").outerHtml
dolzina=instr(1,vsebinaR,"<tr",1)
vsebina1=mid(vsebinaR,1,dolzina-1)
dolzina=instr(1,vsebinaR,"</tr>",1)
vsebinaR=vsebina1&naslovR&mid(vsebinaR,dolzina+5)&"<p></p>"'"<table><tr><td></td></tr></table>"
end if

naslovB=document.getElementById("naslovBreklam").Value

if naslovB<>"" then
vsebinaB=document.all.item("tblBreklam").outerHtml
dolzina=instr(1,vsebinaB,"<tr",1)
vsebina1=mid(vsebinaB,1,dolzina-1)
dolzina=instr(1,vsebinaB,"</tr>",1)
vsebinaB=vsebina1&naslovB&mid(vsebinaB,dolzina+5)&"<p></p>"'"<table><tr><td></td></tr></table>"
end if

naslovS=document.getElementById("naslovSkupaj").Value
if naslovS<>"" then
vsebinaS=document.all.item("tblSkupaj").outerHtml
'kako zbrisem prvo vrstico
dolzina=instr(1,vsebinaS,"<tr",1)
vsebina1=mid(vsebinaS,1,dolzina-1)
dolzina=instr(1,vsebinaS,"</tr>",1)
vsebinaS=vsebina1&naslovS&mid(vsebinaS,dolzina+5)
end if

if vsebinaR<>"" OR vsebinaB<>"" OR vsebinaS<>"" then
set oExcel=CreateObject("Excel.Application")
Set oBook=oExcel.Workbooks.Add
oBook.HTMLProject.
oBook.HTMLProject.HTMLProjectItems(1).Text=vsebinaR&vsebinaB&vsebinaS
oBook.HTMLProject.RefreshDocument
oExcel.Visible=true
oExcel.UserControl=true
end if
end sub

Thanks!

G.

 

[snotmare]

Good morning.

Interesting variable names! Does "izvozi" mean "export"?

If you want to format your columns, use this kind of code somewhere after you've set your "oBook" variable and before you put in the data...

With oBook.WorkSheets(0) '0 is the first worksheet.
    .Cells(1, 1).NumberFormat = "General"
    .Range(.Cells(1, 2), .Cells(1, 5)).NumberFormat = "0.00"
End With

For future reference, you can learn more about how to manipulate an Excel worksheet by simply recording macros in Excel, and viewing the VBA code that is automatically generated.

He who has knowledge spares his words, and a man of understanding is of a calm spirit. Even a fool is counted wise when he holds his peace; when he shuts his lips, he is considered perceptive. - King Solomon

 

[PIAS]

Hi!

Thanks for answer! "Izvozi" realy means "export"! How did you know?

Where do i put this code, before i put data in variable?

Anyway i tryed both, but it doesen't work, any suggestions?

G.

 

[PIAS]

Aha, one more thing, i have flexible grids, it means that i don't know how many cells and rows will hawe in grid which i'm exporting to excel.

G.