So, if anyone is interested here is the code for my little project.
This is the first pass. What it does is figure out yesterday's date, then format it as YYYY-MM-DD.
then it reads a set of MySQL commands from a text file, and writes the query to a new text file with the date first and then the other commands(all this because I need it to set a variable on line 1 and you can't write to the front of a file with vbs)
then finally it calls a batch file to start MySQL with the appropriate query file.
Code:
Option Explicit
Dim objFSO, objFolder, objShell, objTextFile, objFile, objApp, strFile2, strFile3
Dim strDirectory, strFile, strText1, strText2, strText3, strText4, strText5, strText6
Dim f, fso, ReadAllTextFile, dFile, strBat
Dim strDate, strYear, strMonth, strDay
strDirectory = "U:\Phone"
strFile = "\MySQL-PC.txt"
strFile2 = "\MySQL-PC1.txt"
strFile3 = "\MySQL-OUT.txt"
strBat = "\MySQL.bat"
strText3 = "SET @D1 = '"
strText5 = "SET @D2 = '"
strText4 = " 00:00:00';"
strText6 = " 23:00:00';"
Set objShell = CreateObject("WScript.Shell")
'This section gathers the user definded dates (removed for automatic date generation)
'strText1 = InputBox("Please input start date to report", "", "YYYY-MM-DD")
'strText2 = InputBox("Please input end date to report", "", "YYYY-MM-DD")
strDate = DATE() - 1 'get yesterday's date
strYear = YEAR(strDate) 'get the year
strMonth = MONTH(strDate) 'get the month
strMonth = 100 + strMonth 'get the month plus 100
strMonth = CStr(strMonth) 'convert the value to a text string
strMonth = Right(strMonth, 2) 'crop to the right two digits
strDay = DAY(strDate) 'repeat month procedure to convert to
strDay = 100 + strDay
strDay = CStr(strDay)
strDay = Right(strDay, 2)
strText1= strYear & "-" & strMonth & "-" & strDay
strText2 = strText1
' Create the File System Object
Set objFSO = CreateObject("Scripting.FileSystemObject")
' Check that the strDirectory folder exists
If objFSO.FolderExists(strDirectory) Then
Set objFolder = objFSO.GetFolder(strDirectory)
Else
Set objFolder = objFSO.CreateFolder(strDirectory)
WScript.Echo "Just created " & strDirectory
End If
If objFSO.FileExists(strDirectory & strFile) Then
Set objFolder = objFSO.GetFolder(strDirectory)
Else
Set objFile = objFSO.CreateTextFile(strDirectory & strFile)
Wscript.Echo "Just created " & strDirectory & strFile
End If
Set fso = CreateObject("Scripting.FileSystemObject")
Set f = fso.OpenTextFile(strDirectory & strFile2, 1)
ReadAllTextFile = f.ReadAll
set objFile = nothing
set objFolder = nothing
' OpenTextFile Method needs a Const value
' ForAppending = 8 ForReading = 1, ForWriting = 2
Const ForWriting = 2
Set objTextFile = objFSO.OpenTextFile(strDirectory & strFile, ForWriting, True)
' Writes strText 1 & 2 every time you run this VBScript
objTextFile.WriteLine(strText3&strText1&strText4)
objTextFile.WriteLine(strText5&strText2&strText6)
objTextFile.Write(ReadAllTextFile)
objTextFile.Close
' Bonus or cosmetic section to launch explorer to check file
'If err.number = vbEmpty then
' objShell.run ("Explorer" &" " & strDirectory & "\" )
'Else WScript.echo "VBScript Error: " & err.number
'End If
' This sections executes some file clean-up and launches mysql
'Find the output file and delete it.
If objFSO.FileExists(strDirectory & strFile3) Then
Set dFile = objFSO.GetFile(strDirectory & strFile3)
dFile.Delete
Else
End If
objShell.run(strDirectory & strBat)
'MsgBox "Ending sub-routine 1"
WScript.Quit