467,161 Members | 900 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,161 developers. It's quick & easy.

Excel-Macros-Loops #2

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
  • viewed: 1655
Share:
1 Reply
1GB
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.

Similar topics

13 posts views Thread by Allison Bailey | last post: by
6 posts views Thread by Matthew Wieder | last post: by
14 posts views Thread by pmud | last post: by
22 posts views Thread by Howard Kaikow | last post: by
7 posts views Thread by Alain \Mbuna\ | last post: by
16 posts views Thread by alexia.bee@gmail.com | last post: by
9 posts views Thread by Doug Glancy | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.