CreateObject("Excel.Application")
Here's a script I use to stuff values into an existing spreadsheet.
'
'
' Name:
'
'
'
' TrendTemp.vbs
'
'
'
' Description:
'
'
'
' Show how accurate the weather forecast is by comparing the hourly
temperature readings '
' for Winnipeg against the actual values. The actual data consists of the
24 hour values '
' from yesterday. The hourly forecasts for the 48 hours prior to that are
compared to '
' the actual data. The output is in the form of an Excel spreadsheet.
'
'
'
' Audit:
'
'
'
' 2000/12/15 jdeg added TempRange to more easily modify all charts
'
' 2000/12/08 jdeg original code
'
'
'
TempRange = 10 'vertical axis range is from -TempRange to +TempRange
set wso = CreateObject("Wscript.Shell")
set env = wso.Environment("User")
'run the date picker program
if wso.run("\\ems-nt1\utilities\pickdate.exe -1D Trend what day?",,true) = 0
then
PickDate = env("PickDate")
'if the user really picked a date (instead of hitting the close button)
if len(PickDate) = 0 then
set env = Nothing
set wso = Nothing
Wscript.Quit
end if
PickDate = cDate(PickDate)
if Pickdate >= Date() then
wscript.Echo "The most recent date you can pick is yesterday"
set env = Nothing
set wso = Nothing
wscript.Quit
end if
end if
'cols is used to convert column numbers to names
cols = Split("A B C D E F G H I J K L M N O P Q R S T U V W X Y Z"," ")
ConnectionString = "Provider=SQLOLEDB.1;" _
& "Data Source=wpg-dbserver-09;" _
& "User ID=sa;Initial Catalog=weather;" _
& "Persist Security Info=False;"
set conn = CreateObject("ADODB.Connection")
set robj = CreateObject("ADODB.RecordSet")
Set xcel = CreateObject("Excel.Application")
conn.Open ConnectionString
'set the spreadsheet properties
with xcel
.Application.DisplayAlerts = False
.Visible = true
.Workbooks.Open "\\ems-nt1\weather\TrendTemp.xls"
end with
for s = 1 to 4
for c = 1 to 4
xcel.WorkSheets(s).ChartObjects(c).Chart.Axes(2).MinimumScale
= -TempRange
xcel.WorkSheets(s).ChartObjects(c).Chart.Axes(2).MaximumScale =
+TempRange
next
next
xcel.WorkSheets("RawData").Select
filedate = (PickDate + 1) & " 05"
fcstdate = PickDate
basedate = fcstdate & " 00:01"
'get the actual data for all hours for yesterday
if GetData(fcstdate,filedate) then
xcel.Cells(52,2) = "Actuals are for " & fcstdate
'put the actuals into the first data row of the spreadsheet
for hh = 1 to 24
xcel.Cells(2,hh+1) = robj(hh+1)
next
row = 2
'get the forecast data for each hour for the previous two days
for offset = -1 to -48 step -1
row = row + 1
filedate = DateAdd("h",offset,basedate)
filedate = Left(filedate,10) & " " &
Left(FormatDateTime(filedate,4),2)
if GetData(fcstdate,filedate) then
for hh = 1 to 24
xcel.Cells(row,hh+1) = xcel.Cells(2,hh+1) - robj(hh+1)
next
end if
next
end if
robj.Close: set robj = Nothing
conn.Close: set conn = Nothing
set xcel = Nothing
set env = Nothing
set wso = Nothing
Function GetData ( fcstdate, filedate )
dim cmd
cmd = "select * from trendtemp" _
& " where FcstDate = '" & fcstdate & "'" _
& " and FileDate = '" & filedate & "'" _
& " order by FileDate"
'wscript.Echo cmd
if robj.State = 1 then robj.Close
robj.Open cmd, conn, 1
GetData = not robj.EOF
End Function
