Richard
Tue Jan 03 17:21:09 CST 2006
Rubes wrote:
>I am new to vbscript and am trying to do the following:
>
> I have an Excel worksheet with Column A being a range of dates and
> Column B being the backup tape to use on that date. I would like to
> run a vbscript that:
> (1) Finds today's date from Column A
> (2) Reports back the tape number for that row from Column B
Hi,
Assuming there is one date in the first column of each row:
==========================
Option Explicit
Dim objExcel, strExcelPath, objSheet, intRow
Dim dtmDate, dtmToday, strBackupTape
' Determine date.
dtmToday = Date
' Bind to Excel object.
On Error Resume Next
Err.Clear
Set objExcel = CreateObject("Excel.Application")
If Err.Number <> 0 Then
Err.Clear
Wscript.Echo "Excel application not found."
Wscript.Quit
End If
On Error GoTo 0
' Specify the spreadsheet.
strExcelPath = "c:\Test\XLTest.xls"
' Open specified spreadsheet and select the first worksheet.
objExcel.WorkBooks.Open strExcelPath
Set objSheet = objExcel.ActiveWorkbook.Worksheets(1)
' Iterate through the rows of the spreadsheet, until the
' first blank entry in the first column.
intRow = 1
Do While objSheet.Cells(intRow, 1).Value <> ""
' Read the date in the first column.
dtmDate = CDate(objSheet.Cells(intRow, 1).Value)
' Check if the date is today.
If (dtmDate = dtmToday) Then
' Read backup tape and display.
strBackupTape = objSheet.Cells(intRow, 2).Value
Wscript.Echo "Backup tape for today is " & strBackupTape
' Close workbook and quit Excel.
objExcel.ActiveWorkbook.Close
objExcel.Application.Quit
' Clean up and exit.
Set objExcel = Nothing
Set objSheet = Nothing
Wscript.Quit
End If
intRow = intRow + 1
Loop
' Alert user that date not found in spreadsheet.
Wscript.Echo "Backup tape for today not found"
' Close workbook and quit Excel.
objExcel.ActiveWorkbook.Close
objExcel.Application.Quit
' Clean up.
Set objExcel = Nothing
Set objSheet = Nothing
=========================
--
Richard
Microsoft MVP Scripting and ADSI
Hilltop Lab -
http://www.rlmueller.net