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.....
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?
When I write to a cell, the cell ends up formatted as "text" even if I have the cell formatted for time....
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.
Office 2010 installed ....
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
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.