write date & time to Excel file without opening it?

[newcod3r]

I'm trying to track the average time elapsed across tasks as follows:

Code: Select all

Printscreen::
Insert current date & time in Column A ; A1..A2..A3 and so on. If A3 exists, go to A4
Tooltip that shows result of Average(A4-A3, A3-A2,A2-A1) ; i.e. average time elapsed 
return ; without showing the excel file itself

how can I do this?

[BoBo]

Simple? Creating a csv-file should do it: FileAppend
Parse that file: Loop, Parse, … CSV
doing some math: EnvSub
ToolTip

[aifritz]

...or with ADODB
viewtopic.php?f=76&t=99931&p=444007#p444007

[Ahk_fan]

or EPPlus.dll

viewtopic.php?t=50949

Please notify: EPPlus version 5 is not unter LGPL licenced, if you use commercial please buy a licence.
old versions (to 4.5.3) are under LGPL.

[flyingDman]

Try this:

Code: Select all

#Persistent

tmstmp := A_MM "/" A_DD "/" A_YYYY " " A_Hour ":" A_min ":" A_Sec "." A_MSec
cnt := 0, sum := 0
xl := ComObjCreate("excel.application") 
wrkbk := xl.workbooks.open(a_scriptdir . "\timestamp.xlsx")
nwrw := xl.Range("A" xl.Rows.Count).End(-4162).Row + 1
xl.range("a" nwrw).value := tmstmp
xl.range("a2:a" nwrw).NumberFormat := "0.00"
for c in xl.activesheet.range("a2:a" nwrw)
	if (c.offset(1,0).value)	
		sum += xl.worksheetfunction.Sum(c.offset(1,0).value, -c.value) * 86400, ++cnt
tooltip % sum / cnt
xl.range("a2:a" nwrw).NumberFormat := "m/d/yyyy h:mm:ss;@"
wrkbk.close(1)
xl.quit()
settimer, ttoff, -3000
return

ttoff:
tooltip
return

This opens the Excel "timestamp" file every time the script is launched. If it is the intent to keep the Excel file active (in the background), some minor changes need to be made. Because Excel takes some time to open, save, and close, this is not for a "rapid file" type of use. For that I wouldn't use Excel. A .csv or a script that only writes to Excel upon exit, would be a better bet. ADODB is great for read speeds. I haven't even looked into the possibility to write using this method.
Use the hotkey of your choice.

[BoBo]

Code: Select all

#SingleInstance, Force
SetWorkingDir, A_ScriptDir

F10::Run test.csv							; display test file
F11::FileAppend,% A_Now ";", test.csv		; writing timestamp into test file

F12::
delta:=0									; reset
FileRead, content, test.csv					; get timestamps from test file
arr := StrSplit(RTrim(content,";"),";")		; trim trailing ';' and split timestamps into an array
Loop % arr.Count()-1						; loop number of items in array
   delta += arr[A_Index+1]-arr[A_Index]		; substract following timestamp from previous to get time delta
MsgBox % delta/arr.Count()					; divide added number of seconds through number of timestamps to get average time elapsed
Return

[newcod3r]

Code: Select all

#SingleInstance, Force
SetWorkingDir, A_ScriptDir

F10::Run test.csv							; display test file
F11::FileAppend,% A_Now ";", test.csv		; writing timestamp into test file

F12::
delta:=0									; reset
FileRead, content, test.csv					; get timestamps from test file
arr := StrSplit(RTrim(content,";"),";")		; trim trailing ';' and split timestamps into an array
Loop % arr.Count()-1						; loop number of items in array
   delta += arr[A_Index+1]-arr[A_Index]		; substract following timestamp from previous to get time delta
MsgBox % delta/arr.Count()					; divide added number of seconds through number of timestamps to get average time elapsed
Return

the con of this approach is that all the inputs are in 1 cell, which makes it difficult to manipulate / do further analysis manually in excel. Is it possible to store 1 timestamp in each own cell instead?

@flyingDman , ideally it will be best if the appending can be done without Excel launching / residing in the background too. Thank you..

[BoBo]

AFAIK, convert a csv to xls(x) (with setting the separator accordingly) will transfer every value into a separate cell.
The main limitation I seem to remember is a max of ~16k cells/row

https://www.automateexcel.com/how-to/convert-csv-to-xlsx/

[flyingDman]

@flyingDman , ideally it will be best if the appending can be done without Excel launching / residing in the background too. Thank you..

Excel files are not text files and appending text to them will corrupt them. So adding cells to an excel file without opening Excel itself is - afaik - not possible. That said, with my script, you will never know that excel was launched.

[newcod3r]

Dear both, appreciate your help. Will try out both methods and see which one is more appropriate. Thanks!

[flyingDman]

Can you give me an idea of how large that spreadsheet is (number of rows?) Any other columns than that one?

[newcod3r]

Can you give me an idea of how large that spreadsheet is (number of rows?) Any other columns than that one?

for now it will just be 1 column and 10000 rows.

[flyingDman]

As I said before, writing to an Excel file - i.e. even adding a cell to the bottom of a column of values - is only possible if Excel is used, either manually or using the COM interface (anyone challenging that statement? ). In this specific case, we need to read the data first, calculate the intervals (= the difference between the value in one cell and the next), add the new time stamp interval, calculate the average and then save the Excel file. So, because we need to write to the file we need to launch Excel at one point. However, that can be done at the very end because the reading part can be done very fast using COM for ADODB. This "dual" approach saves a lot of time. On my laptop, reading the existing data (10,000 values in the YYYYMMDDHH24MISS format) using ADODB, calculating the intervals and calculating the average interval takes just over 150 ms. A respectable value IMHO. This would take about 10 x longer using COM for Excel. So, your tooltip showing the average interval is almost instantaneous to the human eye. Adding the new timestamp to the data and Saving the the file using COM for Excel, takes about a 1500 ms, most of that is just launching Excel in the background.
This technique should be interesting to a wider audience - not just for timestampers! The only alternative, AFAIK, is writing to a text file of CSV.

Code: Select all

st := A_TickCount
nstmp := a_now
dataSource := a_scriptdir . "\timestamp.xlsx"
objConnection := ComObjCreate("ADODB.Connection")
objRecordSet := ComObjCreate("ADODB.Recordset")
try objConnection.Open("Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" . dataSource . "; Extended Properties='Excel 12.0 xml;HDR=no;IMEX=1';")
catch
	{
	msgbox Error! Data could not be retrieved
	exitapp
	}
objRecordset.Open("Select * FROM [Sheet1$]", objConnection, 3, 3, 1)
pFields := objRecordset.Fields
arr := [], sum:= 0
Loop
	{
	arr.push(pFields.Item(0).value)
	objRecordset.MoveNext
	} Until objRecordset.EOF
for x,y in arr
	if arr[x+1]
		{
		stmp1 := arr[x], lststmp := stmp2 := arr[x+1]
		stmp2 -= stmp1, s
		sum += stmp2
		}
sum += (nstmp - lststmp)		
tooltip % sum / arr.count()"`n" A_TickCount - st "`n" arr.count()   ; gives average, elapsed time to run and number of timestamps        
objRecordSet.close
objConnection.close
objRecordset := objConnection := ""
xl := ComObjCreate("excel.application") 
wrkbk := xl.workbooks.open(a_scriptdir . "\timestamp.xlsx")
nwrw := xl.Range("A" xl.Rows.Count).End(-4162).Row + 1
xl.range("a" nwrw).value := nstmp
wrkbk.close(1)
xl.quit()
tooltip 
return

[nadure]

what about use this library?

viewtopic.php?style=17&t=95251

it is not open the excel, and just modifying value or get value from excel file.