Array Increment

Forums

My Profile

Inbox

Address Book

My Subscription

My Forums

Array Increment

Logged in as: Guest

arrSession:exec spGetSession 2,2,47166

	Active Users: There are 0 members and 0 guests.
	Users viewing this topic: none

Printable Version

All Forums >> [Scripting] >> WSH & Client Side VBScript >> Array Increment
Do you like VisualBasicScript.com? Link to us and help spread the word about our forum. Thanks!

Page: [1]

Message

<< Older Topic Newer Topic >>

Array Increment - 5/17/2007 6:24:19 AM

mcds99

Posts: 441
Score: 4
Joined: 2/28/2006
Status: offline

This doesn't make sense (to me) if I us a For/Next to incrememt the the array pointer it works fine.
If I use a For/Next to incrmemet the array pointer and do some work it fails.
I get the error Subscript out of range on...
SET ObjWbk = objXls.ActiveWorkbook.Worksheets(ObjSht)

Is this a timing thing?

Option Explicit

DIM XlsFldr, i
DIM ObjXls, ObjSht, ObjWbk, XlsFileI, XlsPathI, iRow
DIM ObjXlsA, ObjShtA, ObjWbkA, XlsFileOA , XlsPathOA, iRowA
DIM ObjXlsB, ObjShtB, ObjWbkB, XlsFileOB , XlsPathOB, iRowB
DIM ClmA, ClmB, ClmC, ClmD
DIM TabName(24)
TabName(0) = "172.17.0.x"
TabName(1) = "172.17.1.x"
TabName(2) = "172.17.2.x"
TabName(3) = "172.17.3.x"
TabName(4) = "172.17.4.x"
TabName(5) = "172.17.6.x"
TabName(6) = "172.17.7.x"
TabName(7) = "172.17.8.x"
TabName(8) = "172.17.10.x"
TabName(9) = "172.17.12.x"
TabName(10) = "172.17.14.x"
TabName(11) = "172.17.18.x"
TabName(12) = "172.17.20.x"
TabName(13) = "172.17.22.x"
TabName(14) = "172.17.24.x"
TabName(15) = "172.17.28.x"
TabName(16) = "161.225.129.x"
TabName(17) = "161.225.130.x"
TabName(18) = "161.225.131.x"
TabName(19) = "161.225.132.x"
TabName(20) = "161.225.136.x"
TabName(21) = "161.225.139.x"
TabName(22) = "161.225.140.x"
TabName(23) = "161.225.141.x"
TabName(24) = "161.225.142.x"

XlsFldr = "C:\_SCRIPT\DNS\TrueUpDMZ\"
XlsFileI = "JL051607.xls"
XlsPathI = XlsFldr & XlsFileI
XlsFileOA = "PingNoDNS.xls"
XlsPathOA = XlsFldr & XlsFileOA
XlsFileOB = "NoPingDNS.xls"
XlsPathOB = XlsFldr & XlsFileOB

iRow = 2
iRowA = 1
iRowB = 1

[color="#009900"]'For i = 0 to Ubound(TabName)
  'ObjSht = TabName(i)
  'wscript.echo ObjSht
'Next[/color]

For i = 0 to Ubound(TabName)
   ObjSht = TabName(i)
SET ObjXls = CreateObject("Excel.Application")
   ObjXls.Workbooks.open XlsPathI
SET ObjWbk = objXls.ActiveWorkbook.Worksheets(ObjSht)
Do while ObjWbk.Cells(iRow, 1).Value <> ""
   ClmA = Trim(ObjWbk.Cells(iRow, 1).Value)
   ClmB = Trim(ObjWbk.Cells(iRow, 2).Value)
   ClmC = Trim(ObjWbk.Cells(iRow, 3).Value)
     If ClmB = "Host Pinged" and ClmC = "No DNS Entry" then
       Call PingNoDNS '### Write File A
     End If
     If ClmB = "Host did NOT ping" and ClmC <> "No DNS Entry" then
       Call NoPingDNS '### Write File B
     End If
     iRow = iRow + 1
Loop
Next

_____________________________

Sam

Keep it Simple Make it Fun KiSMiF

Post #: 1

RE: Array Increment - 5/17/2007 2:06:32 PM

dm_4ever

Posts: 2724
Score: 46
Joined: 6/29/2006
From: Orange County, California
Status: offline

What if you moved this line: SET ObjXls = CreateObject("Excel.Application") before your For Each...Next statement?

_____________________________

dm_4ever

My philosophy: K.I.S.S - Keep It Simple Stupid
Read Me: http://www.visualbasicscript.com/m_24727/tm.htm
Frequently Asked Stuff: http://www.visualbasicscript.com/m_47117/tm.htm

(in reply to mcds99)

Post #: 2

RE: Array Increment - 5/18/2007 5:59:30 AM

mcds99

Posts: 441
Score: 4
Joined: 2/28/2006
Status: offline

Same results.
It never gets to the second element in the array...

_____________________________

Sam

Keep it Simple Make it Fun KiSMiF

(in reply to dm_4ever)

Post #: 3

RE: Array Increment - 5/21/2007 12:49:11 AM

ebgreen

Posts: 5250
Score: 31
Joined: 7/12/2005
Status: offline

What if you try to echo the value of TabName(i) just before you try to use it?

_____________________________

"... when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick
Goog places to start:http://www.visualbasicscript.com/m_24727/tm.htm
http://www.visualbasicscript.com/m_47117/tm.htm

(in reply to mcds99)

Post #: 4

RE: Array Increment - 5/21/2007 9:12:28 AM

Skie

Posts: 58
Score: 0
Joined: 3/2/2006
Status: offline

One of your tabs is missing or mislabelled. Are you wanting to open multiple instances of the same file? If not, you should probably only set the excel object once and only open the file once.

(in reply to mcds99)

Post #: 5

RE: Array Increment - 5/22/2007 5:24:06 AM

mcds99

Posts: 441
Score: 4
Joined: 2/28/2006
Status: offline

Very strange...
SET ObjXls = CreateObject("Excel.Application")
ObjXls.Workbooks.open XlsPathI
For i = 0 to Ubound(TabName)
ObjSht = TabName(i)
wscript.echo ObjSht
SET ObjWbk = objXls.ActiveWorkbook.Worksheets(ObjSht)
wscript.echo ObjSht
WScript.Sleep(500)
wscript.echo ObjSht
                                    It increments ObjSht to the next IP but never Gets to the Do While??? I'm going to Put the Do While in a function and see...
Do while ObjWbk.Cells(iRow, 1).Value <> ""
    ClmA = Trim(ObjWbk.Cells(iRow, 1).Value)
    ClmB = Trim(ObjWbk.Cells(iRow, 2).Value)
    ClmC = Trim(ObjWbk.Cells(iRow, 3).Value)
Loop
Next

_____________________________

Sam

Keep it Simple Make it Fun KiSMiF

(in reply to Skie)

Post #: 6

RE: Array Increment - 5/22/2007 5:59:20 AM

mcds99

Posts: 441
Score: 4
Joined: 2/28/2006
Status: offline

OMG!

iRow value...

IP V4 networks are 256 - 2 addresses long 254, after the first workbook iRow was set to 255 so the pointer was at the end of the next work sheet.

DOE!