naikadit
11-19-2008, 03:23 PM
hi guys I am pretty new to vbscript and I need help I am trying to check the tab name if it is equal to rbs site then it should do all the stuff which is given beloe but if it is not found it should give an error message and go to next tab this is what i got till now
objExcel.WorkBooks.Open strExcelPath
Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("c:\test\"&filnam&".xls")
Dim chkFl
chkFl = 0
For Each objWorksheet in objWorkbook.Worksheets -- checks each tab
If objWorksheet.Name = "RBS Site" Then
chkFl = 1
Next ----- error here
if chkFl = 0 then
objtxtStream.Writeline "RBS Site Tab not found" -- checks if the rbs site tab is found or not if not found should go to the last which is marked as @ below
else --- if thetab is found then should do all this
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
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>
Edit Lucas: vba tags added
objExcel.WorkBooks.Open strExcelPath
Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("c:\test\"&filnam&".xls")
Dim chkFl
chkFl = 0
For Each objWorksheet in objWorkbook.Worksheets -- checks each tab
If objWorksheet.Name = "RBS Site" Then
chkFl = 1
Next ----- error here
if chkFl = 0 then
objtxtStream.Writeline "RBS Site Tab not found" -- checks if the rbs site tab is found or not if not found should go to the last which is marked as @ below
else --- if thetab is found then should do all this
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
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>
Edit Lucas: vba tags added