''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''
'TITLE:		xlsQuickLogger.mlsilverman.v4.vbs
'DESCRIPTION:	Appends text to an Excel spreadsheet in the B column with
'today's date autofilled in A
'CREATED BY:	Gina Trapani - lifehacker.com
'CREATE DATE:	07/28/2005
'
' http://www.lifehacker.com/software/weight-loss/script-log-your-weight-
' over-time-with-excel-114904.php
'MODIFIED BY:	Mitchell L. Silverman <mitchell@agman.us>
'LAST MODIFIED:	10/06/2006
'LICENSE:	GPL, http://www.gnu.org/copyleft/gpl.html
'
'INSTRUCTIONS:
' 1.  Enter the directory you'd like your log files saved to.
'     Make sure to only change the text that currently reads:
'     "c:\documents\logs\".
'     The path must be surrounded by quotes.
'
' 2.  Run xlsQuickLogger.mlsilverman.v4.vbs by double-clicking (or change
'     the name to something less cumbersome).
'
' 3.  Set up quicklogger to run as a scheduled task by settting
'     it to run as a Windows Scheduled Task (see the Lifehacker post at
'     http://tinyurl.com/9gqos).  Then, in the task's properties, select
'     the checkbox "Run only if logged in.")
'
' 4.  To launch quicklogger with a simple key combination, see the
'     instructions on Lifehacker here: http://tinyurl.com/neoq8
'
' Modified 8/14/2006 (by MLS) to a) borrow the dating filename from Lester
' McGrath's' "quicklogger.lsmcgrath.vbs" (and learning about the Filesystem
' Object) -- this new version a) creates the Excel workbook file if it doesn't
' already exist, b) formats the new Excel workbook once it's created, and
' c) adds an automatic duration calculation column with total duration at
' the bottom (all also properly formatted).
'
' Modified 8/26/06 - 9/5/06 (by MLS) to check if the user wants to track time
' each day, to save a semaphore block file if not, and to remove the blockfile
' it saves when it first runs the next day.
'
' Since the script now checks each day if you want to log your time, it no
' longer allows monthly time logs.  If you need monthly logs, email me.
'
' Modified 10/6/06 by MLS to a) repeat the last activity description if the user
' enters " " " (a double quote) into the input box, and b) to save a semaphore
' blockfile (and stop keeping track of time)if the user enters "*STOP*" (in
' upper or lower case) into the input box.
'
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Dim xlApp, xlBook, xlSht
Dim w, filepath, filedate, filename, currentRow, lastVal, oFS
Dim DayofWeek, DoLog, Skip

'Set filepath to the path to your log file directory
filepath = "c:\documents\logs\"

filedate = DateFileName(date - 1)

oldlogfile = filepath & "nolog-" & filedate & ".txt"

filedate = DateFileName(date)

filename = filepath & "worklog-" & filedate & ".xls"

nologfile = filepath & "nolog-" & filedate & ".txt"

Call DoBlockFile(nologfile)

If FileExists(nologfile) = False then

	w = InputBox("Add to "&filename&":", "xls Quick Logger")

	if LCase(w) = "*stop*" then

		CreateBlockFile

		WScript.Quit

	end if

	if w <> "" then Call FileOps(w, filename)

End If

If FileExists(oldlogfile) = True then

		'delete old blockfile
		Set oFS = CreateObject("Scripting.FilesystemObject")
		oFS.DeleteFile oldlogfile

End If

'end of program flow

Function DateFileName(filedate)

	DateFileName = cstr(datepart("m",filedate)) & "-" & _
	cstr(datepart("d", filedate)) & "-" & cstr(datepart("yyyy",filedate))

End Function

Sub CreateBlockFile

		'create blockfile

		Set oFS = CreateObject("Scripting.FilesystemObject")

		oFS.CreateTextFile nologfile

End Sub

Sub DoBlockFile(nologfile)

	Skip = FileExists(nologfile) 'blockfile

	If Skip = True then Exit Sub

	Skip = FileExists(filename) 'logfile

	If Skip = True then Exit Sub

	DayofWeek = cstr(datepart("w",date))

	Select Case DayOfWeek

		Case(1)
			DayofWeekText = "Sunday"
		Case(2)
			DayofWeekText = "Monday"
		Case(3)
			DayofWeekText = "Tuesday"
		Case(4)
			DayofWeekText = "Wednesday"
		Case(5)
			DayofWeekText = "Thursday"
		Case(6)
			DayofWeekText = "Friday"
		Case(7)
			DayofWeekText = "Saturday"

	End Select

	DoLog = msgBox("It's " & DayofWeekText & _
	".  Do you want to log your time?", vbYesNo + vbInformation, _
	"Log time?")

	If DoLog = vbNo then

		CreateBlockFile

		WScript.Quit

	End If

End Sub

Function FileExists(file)

	Set oFS = CreateObject("Scripting.FilesystemObject")
	If oFS.FileExists(file) then
		FileExists = True
	else
		FileExists = False
	End If
End Function


Sub FileOps(w, filename)

	Set xlApp = CreateObject("Excel.Application")
	xlApp.Visible = False
	xlApp.DisplayAlerts = False' Hide any Excel alerts

	Set oFS = CreateObject("Scripting.FilesystemObject")

	If oFS.FileExists(filename) then
		set xlBook = xlApp.WorkBooks.Open(filename)
	else
		'Create and save the worksheet with the dated filename
		set xlBook = xlApp.WorkBooks.Add
		xlApp.ActiveWorkbook.SaveAs(filename)

		'Format the worksheet
		xlApp.Range("A:A").Select
		xlApp.Selection.ColumnWidth = 20
		xlApp.Selection.NumberFormat = "mm/dd/yyyy h:mm AM/PM"
		xlApp.Selection.HorizontalAlignment = &hFFFFEFDD ' = xlLeft
		xlApp.Range("B:B").Select
		xlApp.Selection.ColumnWidth = 65
		xlApp.Range("C:C").Select
		xlApp.Selection.ColumnWidth = 10
		xlApp.Selection.NumberFormat = "h:mm"
	end if

	set xlSht = xlApp.activesheet

	'write data
	currentRow = 3
	lastVal = xlSht.Cells(2, 2)
	while lastVal <> ""
		currentRow = currentRow + 1
		lastVal = xlSht.Cells(currentRow, 2 )
	wend

	if currentRow = 3 then
		xlSht.Cells(1, 2) = "Work log for " & filedate
		xlApp.Range("B1:B1").Select
		xlApp.Selection.Font.Bold = True
		xlApp.Selection.HorizontalAlignment = &HFFFFEFF4 ' = xlCenter
		xlSht.Cells(2, 1) = "Date"
		xlSht.Cells(2, 2) = "Activity"
		xlSht.Cells(2, 3) = "Duration"
	end if

	xlSht.Cells(currentRow, 1) = Now

	if w = chr(34) then

		xlSht.Cells(currentRow, 2) = xlSht.Cells(currentRow - 1, 2)

		else
			xlSht.Cells(currentRow, 2) = w

	end if

	if currentRow > 3 then
		xlSht.Cells(currentRow - 1, 3) = "=A" & currentRow & "-" & _
		"A" & (currentRow - 1)
	end if

	xlSht.Cells(currentRow, 3) = ""
	xlSht.Cells(currentRow + 1, 2) = ""
	xlSht.Cells(currentRow + 1, 3) = ""
	xlSht.Cells(currentRow + 2, 2) = "Total Duration:"
	xlSht.Cells(currentRow + 2, 3) = "=SUM(C2:C" & (currentRow - 1)

	xlBook.Save
	xlBook.Close SaveChanges = True
	xlApp.Quit

	'deallocate
	set xlSht = Nothing
	Set xlBook = Nothing
	Set xlApp = Nothing
End Sub
'eof