Help needed in simple script 1

[naikadit]

hi guys I am newbie in vbscript I desperately need your help I have written a small code which checks the columns, decimal numbers, and tabs in an excel sheet also it converts cell values into values (to remove formulas) . also right now I am taking arbitary number of rows and colums (10000 rows and 13 columms) the colums will remain fix but I want to chenge the no of rows depending upon how many data ins there in the sheet. please help guys
Right now it runs but it is running endlessly no stop

<PACKAGE>
<JOB ID=Submit_Reminder>
<SCRIPT LANGUAGE="VBScript" SRC="c:\test\clearquest.bas"/>
<SCRIPT >
dim flag_rnc, objSheet, rxfl
dim flag_rxi, strRNC , strMarket, strRegion, strMarketPhase, strRXI, final_ch
sy = 0
final_ch = 0
rxfl = 0

'--------------------------------------------------------------------------------------------
Set objFSO = createobject("scripting.filesystemobject")
Set objtxtStream = objFSO.CreateTextFile("c:\test\log.txt", True)



'--------------------------------------------------------------------------------------------

Set WSHShell = WScript.CreateObject("WScript.Shell")

' We are ready to use the InputBox-function
' InputBox (prompt, title, default, xpos, ypos)
' prompt: the text shown in the input box
' title: the title shown in the input box
' default: the value shown as default in the input field
' xpos/xpos: upper left corner of the input box
' if some values are omitted, WSH uses default values

filnam = InputBox("Enter the Input file name","File Check","File name", 100, 100)

pathnm = "c:\test\"&filnam&".xls"
'dim fs
Set fs = CreateObject("scripting.filesystemobject")
if not fs.fileexists(pathnm) then
Wscript.echo "File doesn't exist - Check file name!!"
Wscript.Quit
end if



'end if

' end if 'end of successful session

'--------------------------------------------------------------------------------------------

' Bind to Excel object.
On Error Resume Next
Set objExcel = CreateObject("Excel.Application")
If (Err.Number <> 0) Then
On Error GoTo 0
Wscript.Echo "Excel application not found."
Wscript.Quit
End If
On Error GoTo 0

strExcelPath = "c:\test\"&filnam&".xls"

' Open specified spreadsheet and select the first worksheet.
objExcel.WorkBooks.Open strExcelPath

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("c:\test\"&filnam&".xls")

Dim chkFl
chkFl = 0

Dim cnt
Dim mark1

cnt = objWorkbook.Worksheets.Count
mark1 = 1

Do While mark1 <= cnt

If objworkbook.Worksheets(mark1).Name = "RBS Site" Then
chkFl = 1
end if
Loop

if chkFl = 0 then
objtxtStream.Writeline "RBS Site Tab not found"

else

Set objSheet = objExcel.ActiveWorkbook.Worksheets("RBS Site")
objExcel.ActiveWorkbook.Worksheets("RBS Site").Range("A1:T10000").Value = objExcel.ActiveWorkbook.Worksheets("RBS Site").Range("A1:T10000").Value

' checks decimal values

dim i,k ,j, isFloat, toTest
isfloat=false
toTest = "hello"
i=0

For j= 2 to 10000 ----- I want to make sure that this no changes according to the number of rows in the sheet
For k= 2 to 11

toTest = objExcel.ActiveWorkbook.Worksheets("RBS Site").cells(j,k).Value
i=instr(1,toTest,".",vbTextCompare)
if i>0 and k <>5 then
isFloat=true
objtxtStream.Writeline "Decimal number in RBS Site - Row: " & j & " Column : " & k
k=k+8
end If
Next
Next

' Iterate through the rows of the spreadsheet after the first, until the
' first blank entry in the first column. For each row, bind to the user
' specified in the first column and set attributes.
intRow = 2





if (objSheet.Cells(1,1).Value <> "rncId" ) then

objtxtStream.Writeline "Error in RBS Site : Cells(1,1)"
end if

if (objSheet.Cells(1,2).Value <> "rbsId" ) then

objtxtStream.Writeline "Error in RBS Site : Cells(1,2)"
end if

if (objSheet.Cells(1,3).Value <> "Site Id" ) then

objtxtStream.Writeline "Error in RBS Site: Cells(1,3)"
end if

if (objSheet.Cells(1,4).Value <> "logicalName" ) then

objtxtStream.Writeline "Error in RBS Site: Cells(1,4)"
end if

if (objSheet.Cells(1,5).Value <> "Street Address" ) then

objtxtStream.Writeline "Error in RBS Site: Cells(1,5)"
end if

if (objSheet.Cells(1,6).Value <> "City" ) then

objtxtStream.Writeline "Error in RBS Site: Cells(1,6)"
end if

if (objSheet.Cells(1,7).Value <> "Structure Type" ) then

objtxtStream.Writeline "Error in RBS Site: Cells(1,7)"
end if

if (objSheet.Cells(1,8).Value <> "rbsType" ) then

objtxtStream.Writeline "Error in RBS Site: Cells(1,8)"
end if

if (objSheet.Cells(1,9).Value <> "redundancy" ) then

objtxtStream.Writeline "Error in RBS Site: Cells(1,9)"
end if

if (objSheet.Cells(1,10).Value <> "typeOfPowerSupply" ) then

objtxtStream.Writeline "Error in RBS Site: Cells(1,10)"
end if

if (objSheet.Cells(1,11).Value <> "noOfPsu" ) then

objtxtStream.Writeline "Error in RBS Site: Cells(1,11)"
end if

if (objSheet.Cells(1,12).Value <> "batteryBackup" ) then

objtxtStream.Writeline "Error in RBS Site: Cells(1,12)"
end if

if (objSheet.Cells(1,13).Value <> "batteryCapacity" ) then

objtxtStream.Writeline "Error in RBS Site: Cells(1,13)"
end if

if (objSheet.Cells(1,14).Value <> "xalmConfig" ) then

objtxtStream.Writeline "Error in RBS Site: Cells(1,14)"
end if

end if








Wscript.Echo "Input File Check Done. Please View the log file! "

objExcel.ActiveWorkbook.Close
objExcel.Application.Quit

' Clean up.
Set objExcel = Nothing
Set objSheet = Nothing
Set objUser = Nothing



</SCRIPT>
</JOB>
</PACKAGE>

 

[tsuji]

[1] Main problem here.
[tt]
Do While mark1 <= cnt
If objworkbook.Worksheets(mark1).Name = "RBS Site" Then
chkFl = 1
[blue]exit do[/blue] 'for efficiency
end if
[red]mark1=mark1+1[/red]
Loop
[/tt]
[2] There are multiples instances of filesystemobject, path etc... Duplication, though works, betrays the skill-under of the scripter.
[2.1]
>Set objFSO = createobject("scripting.filesystemobject")
>Set fs = CreateObject("scripting.filesystemobject")
One is sufficient. Refer consistent to the one chosen would do.
[2.2]
>pathnm = "c:\test\"&filnam&".xls"
>strExcelPath = "c:\test\"&filnam&".xls"
The same.
[2.3] This is more expensive. Take those 2 lines out.
[tt]
' Open specified spreadsheet and select the first worksheet.
[red]'[/red]objExcel.WorkBooks.Open strExcelPath

[red]'[/red]Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open([blue]strExcelPath[/blue]) 'to be more integrated
[/tt]
[2.4] The rest, I have not looked closer.

 

[naikadit]

hi tsuji,

thanks a ton for your help...really appreciate it... I need your help in one more small thing as explained earlier can I know how can i make sure that it checks only the number of rows which are present in the sheet rather than me running the loop till like 100000

For j= 2 to 10000 ----- I want to make sure that this no changes according to the number of rows in the sheet
For k= 2 to 11

 

[tsuji]

[3]
>For j= 2 to 10000 ----- I want to make sure that this no changes according to the number of rows in the sheet
There may be various ways to determine the largest non-empty row with a given column. This is one.
[tt]
'xlup=&HFFFFEFBE
nmaxrow=objSheet.range("a65536").end(xlup).row
For j=2 to nmaxrow
'etc etc...
Next
[/tt]

 

[naikadit]

hi tsuji ,

thanks a lot you are really helping me a lot I used this and I was trying something will this work. Thanks alot once again

Set objSheet = objExcel.ActiveWorkbook.Worksheets("RBS Site")
dim i,k ,j, isFloat, toTest, xlup, nmaxrow
xlup=&HFFFFEFBE
nmaxrow=objSheet.range("a65536").end(xlup).row
objExcel.ActiveWorkbook.Worksheets("RBS Site").Range("A1:T(nmaxrow)").Value = objExcel.ActiveWorkbook.Worksheets("RBS Site").Range("A1:T(nmaxrow)").Value

' checks decimal values

isfloat=false
toTest = "hello"
i=0

For j= 2 to nmaxrow