Getting subscript out of range [ number : 0 ] error

[bolobaboo]

Hi
I have following code ...Don't know where is problem. Can anybody guide me ? I am getting error at line 64 and char 5 and code 800A0009

'==========================================================================
'
' VBScript Source File -- Created and maintained
'
'
' COMMENT: version 4.0
'
'==========================================================================
Option Explicit

Dim fs,objTextFile,newdate,newlocation,sctape,iResponce,strLogFile,progr
Dim strText0, arrStr,objExcel,objSpread,intRow,X,exlsheet,objSheet,newnum
Set fs=CreateObject("Scripting.FileSystemObject")
'ForReading=1, you must use values
Set objTextFile = fs.OpenTextFile("list.txt", 1)
' Open the Excel spreadsheet
'
exlsheet = "C:\Documents and Settings\brw-operator\my documents\vb\totaltapes.xls"
Set objExcel = CreateObject("Excel.Application")
objExcel.Workbooks. Open exlsheet
Set objSheet = objExcel.ActiveWorkbook.WorkSheets(1)

progr = "progressbar.txt"

Set strLogFile = Fs.OpenTextFile("update.log", 8, True)

Do
newdate = inputbox ("New date for these tapes", "Enter New date ")
If newdate = "" Then
Wscript.Echo "You must enter a Date."
Else
Exit Do
End If
Loop

Do
newlocation = inputbox ("New location for these tapes", "Enter New location for these Tapes ")
If newlocation = "" Then
Wscript.Echo "You must enter Newlocation for Tapes."
Else
Exit Do
End If
Loop


iResponce = MsgBox("Do You want to continue updating sheet ???", vbYesNo, "Updating sheet with date and location.")
If iResponce = vbYes Then ' They Clicked YES!

Else

objExcel.Application.Quit
If fs.FileExists (progr) Then
fs.DeleteFile progr, True
End If
set fs = Nothing
wscript.quit(1)
End If

Do Until objTextFile.AtEndOfStream
'You should check rather than guessing
intRow = 2 'Row 1 often contains headings
arrStr = Split(objTextFile.ReadLine,vbcrlf)
strText0 = arrStr(0)
' Here is the loop that cycles through the cells
strLogFile.WriteLine strText0 & " Done"
Do Until objExcel.Cells(intRow, 1).Value = ""
sctape = objExcel.Cells(intRow, 2).Value
If InStr(sctape,strText0) Then
objExcel.Cells(intRow, 5) = newdate
objExcel.Cells(intRow, 4) = newlocation

End If
intRow = intRow + 1
Loop

objExcel.ActiveWorkbook.Save

Loop
'Clean Up
objTextFile.Close
set objTextFile = Nothing
'Save, close, and exit.
objExcel.ActiveWorkbook.Close
objExcel.Application.Quit
strLogFile.close

If fs.FileExists (progr) Then
fs.DeleteFile progr, True
End If

set fs = Nothing
set arrstr = nothing
wscript.quit

 

[Roadki11]

What line is 64?



RoadKi11

"This apparent fear reaction is typical, rather than try to solve technical problems technically, policy solutions are often chosen." - Fred Cohen

 

[bolobaboo]

Line 64 is
strText0 = arrStr(0)

Don't know what is wrong with this ?

 

[EBGreen]

Well, at a first guess I would say that since .Readline reads up to the next line break, it is not likely that

arrStr = Split(objTextFile.ReadLine,vbcrlf)

will actually find any vbCrLf to split on so you won't get an array back at all.

[red]"... isn't sanity really just a one trick pony anyway?! I mean, all you get is one trick, rational thinking, but when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick[/red]

 

[HarleyQuinn]

As long as there is data in your objTextFile.ReadLine, then Split() will return the whole thing if it finds no instances of vbcrlf, into element 0 of your array.

You will however receive a subscript out of range error if objTextFile.ReadLine evaulates into a blank line (e.g. "")

In your code the example could be:

Dim strTemp
strTemp = "Hello"

arrStr = Split(strTemp,vbcrlf)
strText0 = arrStr(0) ' strText0 will = Hello

strTemp = ""

arrStr = Split(strTemp,vbcrlf) ' this will error
strText0 = arrStr(0)

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[bolobaboo]

Hi
Harleyquinn
Here is part of code .
.
.
.
Do Until objTextFile.AtEndOfStream
intRow = 2 'Row 1 often contains headings
arrStr = Split(objTextFile.ReadLine,vbcrlf)
strText0 = arrStr(0)
strLogFile.WriteLine strText0 & " Done"
Do Until objExcel.Cells(intRow, 1).Value = ""
sctape = objExcel.Cells(intRow, 2).Value
If InStr(sctape,strText0) Then
objExcel.Cells(intRow, 5) = newdate
objExcel.Cells(intRow, 4) = newlocation

End If
intRow = intRow + 1
Loop
objExcel.ActiveWorkbook.Save

Loop
.
.
.

where objTextFile is a text file and contains following ..
000013
000014
000015
000016
000017
000018

How do i correct above code ?

 

[HarleyQuinn]

You could do it a number of ways.

I'm assuming that the file must have blank lines in somewhere to cause the error, do you want to keep the blank line in the output or ignore? If the file doesn't have blank lines does it run for any records or fail at the first one?

A quick fix without knowing the answers to the above would be to check the length, something like (just written off the top of my head)

arrStr = Split(objTextFile.ReadLine & " ",vbcrlf)
strText0 = Trim$(arrStr(0))
If Len(strText0) > 0 then ...
' Execute the rest of the code

This code adds a space to ReadLine so the Split() isn't working with a blank line, then Trims the returned array element to remove the space. If the trimmed string is longer than 0 characters then it passes the If block.

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[EBGreen]

To be honest, with the data that you presented, I can't see any need for the split anyway.

[red]"... isn't sanity really just a one trick pony anyway?! I mean, all you get is one trick, rational thinking, but when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick[/red]

 

[HarleyQuinn]

That is a very good point EBGreen

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.