Hi All New to Forum,
Need a little help with my script if you could any one can help.
I have created a script to look for dates. current date and past 5 dates.
the script only works if the dates are in row "C" how do I change the script to reflect all rows in the excel document.
When it loops it skips records that don't reflect past 5 days and current date.
when I run the cscript excel.vbs where it reads the skiped records it leaves a gap between records.
how to fix???
here is the script:
Option Explicit
Dim objExcel
Dim excelPath
Dim worksheetCount
Dim counter
Dim currentWorkSheet
Dim usedColumnsCount
Dim usedRowsCount
Dim row
Dim column
Dim top
Dim left
Dim Cells
Dim curCol
Dim curRow
Dim word
Rem current date & Record
Dim dCurrent
Dim dRecord
WScript.Echo "Reading Data from " & excelPath
excelPath = "C:\VBTest\Book2.xls"
Set objExcel = CreateObject("Excel.Application")
objExcel.DisplayAlerts = 1
objExcel.Workbooks.open excelPath, false, true
workSheetCount = objExcel.Worksheets.Count
WScript.Echo "We have " & workSheetCount & " worksheets"
For counter = 1 to workSheetCount
WScript.Echo "-----------------------------------------------"
WScript.Echo "Reading data from worksheet " & counter & vbCRLF
Set currentWorkSheet = objExcel.ActiveWorkbook.Worksheets(counter)
usedColumnsCount = currentWorkSheet.UsedRange.Columns.Count
usedRowsCount = currentWorkSheet.UsedRange.Rows.Count
top = currentWorksheet.UsedRange.Row
left = currentWorksheet.UsedRange.Column
Set Cells = currentWorksheet.Cells
For row = 0 to (usedRowsCount -1)
dCurrent = date ()
dRecord = Cells(row+top,3).value
if DateDiff("d", dCurrent, dRecord) >= -5 And DateDiff("d", dCurrent, dRecord) <= 0 then
For column = 0 to usedColumnsCount-1
curRow = row+top
curCol = column+left
word = Cells(curRow,curCol).Value
WScript.Echo Word
Next
end if
WScript.Echo
rem WScript.Echo (Date)
Next
Set currentWorkSheet = Nothing
Next
objExcel.Workbooks(1).Close
objExcel.Quit
Set currentWorkSheet = Nothing
Set objExcel = Nothing
Thanks
Mike