Problem running vbscrt with windows scheduler
So I've looked and tried this 15 different ways with different modifcations to my code and to windows scheduler but I cannot get my script to run at all in the morning before I get into work.
I am basically accessing two tables within a database and pulling all of the information from the tables and then depositing this information into two separate excel sheets that are dated and timestamped to avoid overwriting.
Here is my code...
Code:
'Documentation notes:
'1) Any file or variable that starts with "award..." is
' associated with the Awards table within the DB.
'2) Any file or variable that starts with "group..." is
' associated with the Award Groups table within the DB.
'Make sure the system does not create new variables during error
Option Explicit
'''''''''''''''''''''''''
'' Variable Definition ''
'''''''''''''''''''''''''
'File location for the excel files
Const location="C:\AwardBackup\"
'The connection information will be set here
Const server="*****"
Const database="*****"
Const user="*****"
Const password="*****"
'Table name declaration
Const awardTable = "dbo.Awards"
Const groupTable = "dbo.AwardGroups"
'Date information by first finding the full date and then
'separating it into it's parts
Dim currDate
Dim currDay
Dim currMonth
Dim currYear
Dim currTime
Dim fileTime
'Procedure declarations (SQL Queries)
Dim awardProc
Dim groupProc
'Excel file name declarations
Dim awardFileName, groupFileName
'Award and Award Group workbook and worksheet variables
Dim awardWS, awardWB, groupWS, groupWB
'
Dim objAward, objGroup
'Connection object
Dim cn
'Awards and Award Groups RecordSet objects
Dim awardRecordSet, groupRecordSet
'Place holders / a = current row in awards excel sheet /
'b = current row in award groups excel sheet / col = column
Dim a, b, col
'''''''''''''''''''''''''
'' Set Variables ''
'''''''''''''''''''''''''
'Setting date information
currDate = Date()
currDay = Day(currDate)
currMonth = Month(currDate)
currYear = Year(currDate)
'Setting time information
currTime = Now
fileTime = Right("00" & Hour(currTime), 2) & Right("00" & Minute(currTime), 2) & Right("00" & Second(currTime), 2)
'Set procedures / queries
awardProc = "SELECT * FROM " & database & "." & awardTable & ";"
groupProc = "SELECT * FROM " & database & "." & groupTable & ";"
'Set excel file name
awardFileName = "AwardsBackup-" & currMonth & "-" & currDay & "-" & currYear & "-" & fileTime & ".xls"
groupFileName = "AwardGroupsBackup-" & currMonth & "-" & currDay & "-" & currYear & "-" & fileTime & ".xls"
'''''''''''''''''''''''''
'' Setup DB Connection ''
'''''''''''''''''''''''''
'Create the connection object
Set cn = CreateObject("ADODB.Connection")
'Populate the connection object with the provider,
'connection string and cursor loc
With cn
.Provider = "SQLNCLI"
'Condenses the connection information already
'populated into a SQL 2008 compliant CS
.connectionString = "Data Source=" & server & _
";Database=" & database & _
";uid=" & user & _
";pwd=" & password & ";"
.CursorLocation = 3
.Open
End With
'Set the awards and group awards recordset objects and
'direct them with the connection and query information
Set awardRecordSet = CreateObject("ADODB.RecordSet")
awardRecordSet.Open awardProc, cn
Set groupRecordSet = CreateObject("ADODB.RecordSet")
groupRecordSet.Open groupProc, cn
'''''''''''''''''''''''''
'' XFer Excel Data ''
'''''''''''''''''''''''''
'Set the excel object
Set objAward = CreateObject("Excel.Application")
Set awardWB = objAward.Workbooks.Add()
Set awardWS = awardWB.Worksheets(1)
'Awards table loop which will populate the awards backup
Do Until awardRecordSet.EOF
'Loop through all of the fields in the row
For a = 0 To awardRecordSet.Fields.Count - 1
'If the contents of the field are not null
If Not IsNull(awardRecordSet.Fields(a).Value) Then
awardWS.Cells(col+1,a+1).Value = awardRecordSet.Fields(a).Value
End If
Next 'a
col = col + 1
awardRecordSet.MoveNext
Loop
'Close the RS because we do not need to access this table anymore
awardRecordSet.Close()
'Save the excel sheet with the name contained in the "awardsFileName" variable
awardFileName = location & awardFileName
objAward.ActiveWorkbook.SaveAs(awardFileName)
objAward.ActiveWorkbook.Close()
'<-------------Award Groups Data Backup---------------->'
'Set the excel object
Set objGroup = CreateObject("Excel.Application")
Set groupWB = objGroup.Workbooks.Add()
Set groupWS = groupWB.Worksheets(1)
'Award Groups table loop
Do Until groupRecordSet.EOF
'Loop through all of the fields in the row
For b = 0 To groupRecordSet.Fields.Count - 1
'If the contents of the field are not null
If Not IsNull(groupRecordSet.Fields(b).Value) Then
groupWS.Cells(col+1,b+1).Value = groupRecordSet.Fields(b).Value
End If
Next 'b
groupRecordSet.MoveNext
col = col + 1
Loop
'Close the RS because we do not need to access this table anymore
groupRecordSet.Close()
'Save the excel sheet with the name contained in the "awardsFileName" variable
groupFileName = location & groupFileName
objGroup.ActiveWorkbook.SaveAs(groupFileName)
objGroup.ActiveWorkbook.Close()
'''''''''''''''''''''''''
''Closing Connection...''
'''''''''''''''''''''''''
' Close Excel with the Quit method on the Application object.
objGroup.Application.Quit
' Release the object variable.
Set objGroup = Nothing
' Close Excel with the Quit method on the Application object.
objAward.Application.Quit
' Release the object variable.
Set objAward = Nothing
'Closing DB connection
cn.Close()
Now, when I run the script on it's own it runs perfectly. I can create both excel sheets and have them have correct data 100% of the time. However, when I run this script through scheduled tasks it brain farts everytime and never runs. Can someone please help me with this?
Mike
Re: Problem running vbscrt with windows scheduler
Does your script ever actually get run by the scheduler? Add something to your code that creates a log file and logs the status of each step, and see what happens.
