Writting to Excel.

Started by morenos1, February 15, 2018, 01:36:48 PM

Previous topic - Next topic

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?
"No one who sees a peregrine falcon fly can ever forget the beauty and thrill of that flight."
  - Dr. Tom Cade

morenos1

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

stanl

Quote from: morenos1 on February 15, 2018, 01:36:48 PM
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


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.