By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,986 Members | 1,522 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,986 IT Pros & Developers. It's quick & easy.

VB script help needed

P: 14
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
Nov 6 '06 #1
Share this Question
Share on Google+
19 Replies


Expert 5K+
P: 8,434
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?
Nov 6 '06 #2

P: 14
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
Nov 7 '06 #3

Expert 5K+
P: 8,434
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?
Nov 7 '06 #4

P: 14
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.
Nov 7 '06 #5

Expert 5K+
P: 8,434
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.
Expand|Select|Wrap|Line Numbers
  1. 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:
Expand|Select|Wrap|Line Numbers
  1. Set colSettings = objWMIService.ExecQuery _
  2. ("Select * from Win32_ComputerSystem WHERE Column1 Between " _
  3. & StartRecord & " And " & EndRecord & " Sort By Column1")
Nov 7 '06 #6

P: 14
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.
Nov 7 '06 #7

Expert 5K+
P: 8,434
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.
Nov 8 '06 #8

P: 14
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?
Nov 9 '06 #9

100+
P: 1,646
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...

Expand|Select|Wrap|Line Numbers
  1. objRecordset.Open "SELECT * FROM tbl_inventory WHERE ip BETWEEN 10 AND 20" , objConnection, _
  2. adOpenStatic, adLockOptimistic
  3.  
  4.  
Nov 9 '06 #10

Expert 5K+
P: 8,434
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.
Nov 9 '06 #11

P: 14
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.
Nov 9 '06 #12

P: 14
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
Nov 9 '06 #13

100+
P: 1,646
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

Expand|Select|Wrap|Line Numbers
  1. Dim stInput As String
  2. Dim intStart As Integer
  3. Dim intEnd As Integer
  4. Dim intTotalRecs As Integer
  5.  
  6. intTotalRecs = 999 'put what the total number is here
  7.  
  8. stInput = InputBox("Please enter the start row")
  9. If IsNumeric(stInput) AND Len(stInput) < 4 Then
  10.  intStart = CInt(stInput)
  11. Else
  12.   MsgBox "Please enter a number between 1 and " & intTotalRecs - 1
  13.   Exit Sub
  14. End If
  15.  
  16. If intStart >= intTotalRecs Then
  17.   MsgBox "Please enter a number between 1 and " & intTotalRecs - 1
  18.   Exit Sub
  19. End If
  20.  
  21. stInput = InputBox("Please enter the end row")
  22. If IsNumeric(stInput) AND Len(stInput) < 4 Then
  23.  intEnd = CInt(stInput)
  24. Else
  25.   MsgBox "Please enter a number between " & intStart + 1 & " and " & intTotalRecs
  26.   Exit Sub
  27. End If
  28.  
  29. If intStart >= intEnd Then
  30.    MsgBox "The end row must be greater than the start row"
  31.    Exit Sub
  32. End If
  33.  
  34. objRecordset.Open "SELECT * FROM tbl_inventory " _
  35.    & "WHERE ip BETWEEN  " & intStart & " AND " & intEnd, _
  36.     objConnection, adOpenStatic, adLockOptimistic
  37.  
  38.  
  39.  
Nov 9 '06 #14

Expert 5K+
P: 8,434
Expand|Select|Wrap|Line Numbers
  1. ...
  2. stInput = InputBox("Please enter the start row")
  3. If IsNumeric(stInput) AND Len(stInput) < 4 Then
  4.  intStart = CInt(stInput)
  5. Else
  6.   MsgBox "Please enter a number between 1 and " & intTotalRecs - 1
  7.   Exit Sub
  8. 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.
Nov 9 '06 #15

P: 14
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!
Nov 10 '06 #16

100+
P: 1,646
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))
Nov 10 '06 #17

P: 14
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

Expand|Select|Wrap|Line Numbers
  1. Set oRegistry = GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & strComputer & "/root/default:StdRegProv")
  2.  
Is there any idea how can i authenticate this part with alternate credentials

I have come to this point....
Expand|Select|Wrap|Line Numbers
  1. Const WbemAuthenticationLevelPktPrivacy = 6
  2. StrLine = Trim(inputbox("Enter name or IP address of local or remote computer"))
  3. strNamespace = "root\cimv2" 
  4. strUser = "administrator"
  5. strPassword = "***********"
  6.  
  7. Set objWbemLocator = CreateObject("WbemScripting.SWbemLocator")
  8. Set objWMIService = objwbemLocator.ConnectServer _
  9. (strLine, strNamespace,  strUser, strPassword)
  10. objWMIService.Security_.authenticationLevel = WbemAuthenticationLevelPktPrivacy
  11.  
Now can u guys help me to get the first code implement into the 2nd code.

Thanks a lot.
Nov 11 '06 #18

100+
P: 1,646
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

Expand|Select|Wrap|Line Numbers
  1. Set oRegistry = GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & strComputer & "/root/default:StdRegProv")
  2.  
Is there any idea how can i authenticate this part with alternate credentials

I have come to this point....
Expand|Select|Wrap|Line Numbers
  1. Const WbemAuthenticationLevelPktPrivacy = 6
  2. StrLine = Trim(inputbox("Enter name or IP address of local or remote computer"))
  3. strNamespace = "root\cimv2" 
  4. strUser = "administrator"
  5. strPassword = "***********"
  6.  
  7. Set objWbemLocator = CreateObject("WbemScripting.SWbemLocator")
  8. Set objWMIService = objwbemLocator.ConnectServer _
  9. (strLine, strNamespace,  strUser, strPassword)
  10. objWMIService.Security_.authenticationLevel = WbemAuthenticationLevelPktPrivacy
  11.  
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.
Nov 11 '06 #19

Expert 5K+
P: 8,434
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.
Nov 11 '06 #20

Post your reply

Sign in to post your reply or Sign up for a free account.