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 adLockOptimistic = 3
Const adUseClient = 3
Set objConnection = CreateObject("ADODB.Connection")
Set objRecordset = CreateObject("ADODB.Recordset")
objConnection.Open "DSN=test;"
objRecordset.CursorLocation = adUseClient
objRecordset.Open "SELECT * FROM tbl_inventory" , objConnection, _
adOpenStatic, adLockOptimistic
'objRecordSet.MoveFirst

Do While Not objRecordSet.EOF
strComputer = objRecordSet("ComputerIP")
'************************************************* ***********************
Set objShell = CreateObject("WScript.Shell")
strCommand = "%comspec% /c ping -n 3 -w 1000 " & strComputer & ""
Set objExecObject = objShell.Exec(strCommand)
strText = objExecobject.StdOut.ReadAll()
IF Instr(strText, "Reply") > 0 Then
'************************************************* ***************************
Set objWMIService = GetObject("winmgmts:" & "{impersonationLevel=impersonate}!\\" & strComputer& "\root\cimv2")
'ADD CONDITION HERE --- IF CONNECTION IS MADE THEN DO CODE BELOW OTHERWISE SKIP TO .MOVENEXT
If Err.Number <> 0 Then
objRecordSet("Status") = "Not Accessable"'Err.Description
Err.Clear

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

For Each objsetting in colSettings
'objRecordset.AddNew
objRecordSet("ComputerIP") = strComputer
objRecordSet("Status") = strComplete
objRecordSet("SerialNumber") = objsetting.SerialNumber
objRecordSet("Manufacturer") = objsetting.Manufacturer
objRecordset.Update

Next
Set colSettings = objWMIService.ExecQuery ("Select * from Win32_ComputerSystem")
For Each objsetting in colSettings
objRecordSet("ModelNumber") = objsetting.Model
objRecordset("HostName") = objsetting.Name
objRecordset("UserName") = objsetting.UserName
objRecordset.Update
Next

'END CONDITION HERE --- END IF
End if

'************************************************* **************************
Else
objRecordSet("Status") = "Not Online"
End if
objRecordSet.MoveNext
Loop
objrecordse.close
objconnection.close

[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.ExecQuery ("Select * from Win32_ComputerSystem 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.Open "SELECT * FROM tbl_inventory" , objConnection, _
adOpenStatic, adLockOptimistic
[/qoute]
at this part if I am not wrong, what do you think?

[willakawill]

objRecordset.Open "SELECT * FROM tbl_inventory" , objConnection, _
adOpenStatic, adLockOptimistic

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.Open "SELECT * FROM tbl_inventory WHERE ip BETWEEN 10 AND 20" , objConnection, _
adOpenStatic, adLockOptimistic

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.

[delwar66]

Hi willakawill

Thank you very much. You guys are ausome. I will check it over the weekend as I will be very busy Friday. I will let u know if there is any problem. But it looks to me would work perfectly.
Again thanks a lot.
I must say this is a wonderful forum.
Keep it up guys!

[willakawill]

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.

You are absolutely right K. Well spotted. It should read:
intStart = Abs(CInt(stInput))

and

intEnd = Abs(CInt(stInput))

[delwar66]

Hi Guys,
I hope you can help me little more. I have a script that run and collect remote computer monitor information but only if I am logged on the local machine as administrator and if the admin password of the remote and local machine is same. I have work around with this and got stuck at the point where I need to authenticate with the remote machine for the following entry

Set oRegistry = GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & strComputer & "/root/default:StdRegProv")
 

Is there any idea how can i authenticate this part with alternate credentials

I have come to this point....

Const WbemAuthenticationLevelPktPrivacy = 6
StrLine = Trim(inputbox("Enter name or IP address of local or remote computer"))
strNamespace = "root\cimv2" 
strUser = "administrator"
strPassword = "***********"
 
Set objWbemLocator = CreateObject("WbemScripting.SWbemLocator")
Set objWMIService = objwbemLocator.ConnectServer _
(strLine, strNamespace,  strUser, strPassword)
objWMIService.Security_.authenticationLevel = WbemAuthenticationLevelPktPrivacy
 

Now can u guys help me to get the first code implement into the 2nd code.

Thanks a lot.

[willakawill]

Hi Guys,
I hope you can help me little more. I have a script that run and collect remote computer monitor information but only if I am logged on the local machine as administrator and if the admin password of the remote and local machine is same. I have work around with this and got stuck at the point where I need to authenticate with the remote machine for the following entry

Set oRegistry = GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & strComputer & "/root/default:StdRegProv")
 

Is there any idea how can i authenticate this part with alternate credentials

I have come to this point....

Const WbemAuthenticationLevelPktPrivacy = 6
StrLine = Trim(inputbox("Enter name or IP address of local or remote computer"))
strNamespace = "root\cimv2" 
strUser = "administrator"
strPassword = "***********"
 
Set objWbemLocator = CreateObject("WbemScripting.SWbemLocator")
Set objWMIService = objwbemLocator.ConnectServer _
(strLine, strNamespace,  strUser, strPassword)
objWMIService.Security_.authenticationLevel = WbemAuthenticationLevelPktPrivacy
 

Now can u guys help me to get the first code implement into the 2nd code.

Thanks a lot.

Hi. Sorry. Not for me this one. I never support potential breaches of security.

[Killer42]

Hi. Sorry. Not for me this one. I never support potential breaches of security.

Well don't look at me - I don't even understand it. Never had much to do with networking, unfortunately.

I'd suggest starting a new thread with a subject line that gives a clear indication of the problem, to attract someone who can help.