By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,986 Members | 1,487 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,986 IT Pros & Developers. It's quick & easy.

Excel-Macros-Loops #2

P: 1
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
Nov 9 '06 #1
Share this Question
Share on Google+
1 Reply


100+
P: 1,646
dRecord = Cells(row+top,3).value
Hi Mike,
You have this set to column C (3). i.e.
Cells(Row, Col)

You mention row C but there is no such row. Only a column

Hope this helps
Nov 9 '06 #2

Post your reply

Sign in to post your reply or Sign up for a free account.