Writting to Excel.

[morenos1]

I am able to input data to an Excel Sheet using "MicroSoft.Jet.OLEDB.4.0".

I am looking for the syntax to specify the cell formatting. I need to specify time as the cell formatting when I write to a cell ...

Thanks.....

[td]

Do you mean that you want to specify a time format for cells that contain time information or do you mean that you want the data to be stored in cells as Excel's native time datatype?

[morenos1]

When I write to a cell, the cell ends up formatted as "text" even if I have the cell formatted for time....

[stanl]

I am able to input data to an Excel Sheet using "MicroSoft.Jet.OLEDB.4.0".

By using OLEDB, can we assume that Excel.Application is not available or Office is not installed as this will limit formatting.

[morenos1]

Office 2010 installed ....

[morenos1]

I need the time formatting in the sheet to stay.

This will write the time as text ...

adOpenDynamic    = 2
adOpenKeySet     = 1
adLockOptimistic = 3
adLockBatchOptimistic = 4
adCmdTable       = 2
adCmdTableDirect = 512
adCmdText        = 1

file  = "c:\temp\RAO.XLS"  ; change to suit your environment
cConn = "Provider=MicroSoft.Jet.OLEDB.4.0; Data Source=%file%;Extended Properties=Excel 8.0"
DB    = ObjectOpen("ADODB.Connection")
RS    = ObjectOpen("ADODB.Recordset")
DB.Open(cConn)
RS.Open("RAO$",DB,adOpenKeySet,adLockOptimistic,adCmdTableDirect)

x = 0
RS.MoveFirst()
While ! RS.eof()
   fld  = RS.Fields("BRAO")
   fld1 = RS.Fields("OCN")
   If StrSub( fld.Value,1,1) == "B"
      fld1.Value = "02:30 AM"
      x = x+1
   Endif
   RS.MoveNext()
Endwhile

DB.Close()
ObjectClose(DB)
ObjectClose(RS)
message("Number of OCN Values Changed",x)
Exit

[stanl]

Just my .02 but I think it would be better to process in Excel directly instead of an ADODB.Recordset. You might want to post a sample of the workbook.