VB script help needed

[delwar66]

Hi everyone,
I am a novice in vb scripting and trying to get something from it. I have a scripts that read an existing table, take the argument from 2nd column ( Firs colum is sequential number) and then populate the other colulm with info. Now my question: what would be the code to ask the scripts to read the table read the certain row number for example from row 10-20.
Could someone pls help me?
Thanks in advance

[Killer42]

Hi everyone,
I am a novice in vb scripting and trying to get something from it. I have a scripts that read an existing table, take the argument from 2nd column ( Firs colum is sequential number) and then populate the other colulm with info. Now my question: what would be the code to ask the scripts to read the table read the certain row number for example from row 10-20.
Could someone pls help me?
Thanks in advance

What kind of "tables" are we talking about here? Excel?

[delwar66]

What kind of "tables" are we talking about here? Excel?

This is a access 2002 table. what i am trying to do is-- I have a table with ip number. I run my scripts which read that table line by line and populate info from WMI into the table. What I am lookin is to read certain record eg: from 10-20.

here is the scripts....

On Error Resume Next
Const adOpenStatic = 3
Const adLockOptimisti c = 3
Const adUseClient = 3
Set objConnection = CreateObject("A DODB.Connection ")
Set objRecordset = CreateObject("A DODB.Recordset" )
objConnection.O pen "DSN=test;"
objRecordset.Cu rsorLocation = adUseClient
objRecordset.Op en "SELECT * FROM tbl_inventory" , objConnection, _
adOpenStatic, adLockOptimisti c
'objRecordSet.M oveFirst

Do While Not objRecordSet.EO F
strComputer = objRecordSet("C omputerIP")
'************** *************** *************** *************** *************
Set objShell = CreateObject("W Script.Shell")
strCommand = "%comspec% /c ping -n 3 -w 1000 " & strComputer & ""
Set objExecObject = objShell.Exec(s trCommand)
strText = objExecobject.S tdOut.ReadAll()
IF Instr(strText, "Reply") > 0 Then
'************** *************** *************** *************** *************** **
Set objWMIService = GetObject("winm gmts:" & "{impersonation Level=impersona te}!\\" & strComputer& "\root\cimv 2")
'ADD CONDITION HERE --- IF CONNECTION IS MADE THEN DO CODE BELOW OTHERWISE SKIP TO .MOVENEXT
If Err.Number <> 0 Then
objRecordSet("S tatus") = "Not Accessable"'Err .Description
Err.Clear

Else
Set colSettings = objWMIService.E xecQuery ("Select * from Win32_Bios")
strComplete = "Active on: " & Date & " at " & time

For Each objsetting in colSettings
'objRecordset.A ddNew
objRecordSet("C omputerIP") = strComputer
objRecordSet("S tatus") = strComplete
objRecordSet("S erialNumber") = objsetting.Seri alNumber
objRecordSet("M anufacturer") = objsetting.Manu facturer
objRecordset.Up date

Next
Set colSettings = objWMIService.E xecQuery ("Select * from Win32_ComputerS ystem")
For Each objsetting in colSettings
objRecordSet("M odelNumber") = objsetting.Mode l
objRecordset("H ostName") = objsetting.Name
objRecordset("U serName") = objsetting.User Name
objRecordset.Up date
Next

'END CONDITION HERE --- END IF
End if

'************** *************** *************** *************** *************** *
Else
objRecordSet("S tatus") = "Not Online"
End if
objRecordSet.Mo veNext
Loop
objrecordse.clo se
objconnection.c lose

[Killer42]

This is a access 2002 table. what i am trying to do is-- I have a table with ip number. I run my scripts which read that table line by line and populate info from WMI into the table. What I am lookin is to read certain record eg: from 10-20.

here is the scripts....

I suspect that all you need is a WHERE clause in your SELECT statement, but can you expand on what you mean by records 10 to 20? Do you just mean that starting from the beginning you want to skip the first 9 records, then process the next 10? Or is that "10" and so on an actual value in one of your fields?

[delwar66]

Thanks killer42. What I want is to say for run from row 10 through row 20 and in times it may change from row something to row something. I hope that clears and pls show me exactly how the code would be. My table first colum is sequential number 1..2..3..4.. then the 2nd row is computer ip. So the code should be read the row 10 ping the compuer ip then do what it supposed to do and move to next row and so on until row 20.

Thanks again.

[Killer42]

Thanks killer42. What I want is to say for run from row 10 through row 20 and in times it may change from row something to row something. I hope that clears and pls show me exactly how the code would be. My table first colum is sequential number 1..2..3..4.. then the 2nd row is computer ip. So the code should be read the row 10 ping the compuer ip then do what it supposed to do and move to next row and so on until row 20.
Thanks again.

The actual record selection should be a matter of doing something along these lines. Note, I don't know the name of your first column, so I'll just call it Column1.

Set colSettings = objWMIService.ExecQuery ("Select * from Win32_ComputerSystem WHERE Column1 Between 10 And 20 Sort By Column1")

To make it more flexible by using variables for the start and end values, you just concatentate them into the string. For example:

Set colSettings = objWMIService.ExecQuery _
("Select * from Win32_ComputerSystem WHERE Column1 Between " _
& StartRecord & " And " & EndRecord & " Sort By Column1")

[delwar66]

Hi Killer42
Thanks for your reply. But I am not quite sure exactly where you want to put your

Set colSettings = objWMIService.E xecQuery ("Select * from Win32_ComputerS ystem WHERE Column1 Between 10 And 20 Sort By Column1")

in my script to get the result I want. My table first colum is sequential number like 1 and so on. 2nd colum is the ip address. So I want the script read the table from say for row 10 to row 20 and ping the ip address of the corresponding row and do the things it supposed to do. Pls see my scripts in my earlier post and let me know exactly where to put that code.

thanks in advance.

[Killer42]

Hi Killer42
Thanks for your reply. But I am not quite sure exactly where you want to put your ...

This was a variation on your existing query. Your one returns everything, I just pulled it out of your code and modified it to return the 10th to 20th records. It was just a quick attempt at a solution, and may not be what you are after.

[delwar66]

Thanks for your reply. what I understand from your comment is it is retrivening 10th to 20th record from the querry but I want it should read the 10th to 20th row from the table and retrieve all that from the wmi what I have mentioned to get. So I am looking to modify something in my scripts at

[quote]
objRecordset.Op en "SELECT * FROM tbl_inventory" , objConnection, _
adOpenStatic, adLockOptimisti c
[/qoute]
at this part if I am not wrong, what do you think?

[willakawill]

objRecordset.Op en "SELECT * FROM tbl_inventory" , objConnection, _
adOpenStatic, adLockOptimisti c

at this part if I am not wrong, what do you think?

Hi, I am jumping in (mostly to irritate killer42 for fun. :))
This should work...

objRecordset.Open "SELECT * FROM tbl_inventory WHERE ip BETWEEN 10 AND 20" , objConnection, _
adOpenStatic, adLockOptimistic

[Killer42]

Hi, I am jumping in mostly to irritate killer for fun. :)

Grrr..... ;)

Seriously though, I hope it works. I'm a bit lost on this one. I think I was on the right lines with the between, but put it in the wrong query.

[delwar66]

Thanks willakawill,

Thank you very much, I knew it could be very simple things but you know as I am a novice in this and learning everyday, it has save me a lot of hassle. I was not comfortable until find this. I tried and it works fine. Again, thank you killer42 and willakawill for responding my postings.

[delwar66]

Hi Willakawill,

It just came in my mind, how do you implement an input box so that you can input the start and end row number when you double click the scripts which will replace the 10 and 20 in the follwoing code;

objRecordset.Op en "SELECT * FROM tbl_inventory WHERE ip BETWEEN 10 AND 20" , objConnection, _
adOpenStatic, adLockOptimisti c

thanks

[willakawill]

Hi Willakawill,

It just came in my mind, how do you implement an input box so that you can input the start and end row number when you double click the scripts which will replace the 10 and 20 in the follwoing code;

thanks

Hi. This brings many more things into play.
You will need to write code to validate all user input

Dim stInput As String
Dim intStart As Integer
Dim intEnd As Integer
Dim intTotalRecs As Integer

intTotalRecs = 999 'put what the total number is here

stInput = InputBox("Please enter the start row")
If IsNumeric(stInput) AND Len(stInput) < 4 Then
 intStart = CInt(stInput)
Else
  MsgBox "Please enter a number between 1 and " & intTotalRecs - 1
  Exit Sub
End If

If intStart >= intTotalRecs Then
  MsgBox "Please enter a number between 1 and " & intTotalRecs - 1
  Exit Sub
End If

stInput = InputBox("Please enter the end row")
If IsNumeric(stInput) AND Len(stInput) < 4 Then
 intEnd = CInt(stInput)
Else
  MsgBox "Please enter a number between " & intStart + 1 & " and " & intTotalRecs
  Exit Sub
End If

If intStart >= intEnd Then
   MsgBox "The end row must be greater than the start row"
   Exit Sub
End If

objRecordset.Open "SELECT * FROM tbl_inventory " _
   & "WHERE ip BETWEEN  " & intStart & " AND " & intEnd, _
    objConnection, adOpenStatic, adLockOptimistic

[Killer42]

...
stInput = InputBox("Please enter the start row")
If IsNumeric(stInput) AND Len(stInput) < 4 Then
 intStart = CInt(stInput)
Else
  MsgBox "Please enter a number between 1 and " & intTotalRecs - 1
  Exit Sub
End If

Hi Willakawill. :)

The validation is good, and people so often overlook it. But, though I haven't checked, I have a feeling that negative numbers might slip past you here.