excel vba ping电脑列表 [英] excel vba ping list of computers
问题描述
所以这里是我的代码
主要:
Sub pingall_Click()
Dim c作为范围
c = Target.Name
对于每个c In范围(A1:N50)
如果(左(c,1)=C或左(c ,1)=T)和IsNumeric(右(c,6))和Len(c)= 7然后
c = sPing(c)
如果c =timeout然后
MsgBoxtimeout
ElseIf c < 16&c> -1然后
MsgBoxok
ElseIf c> 15和c < 51然后
MsgBox不好
ElseIf c> 50和c < 4000然后
MsgBox大延迟
Else
MsgBox错误
结束如果
结束如果
下一个c
End Sub
功能:
公共函数sPing(sHost)As String
Dim oPing As Object,oRetStatus As Object
Set oPing = GetObject(winmgmts:{impersonationLevel = $$$$$$$$$ IsNull(oRetStatus.StatusCode)或oRetStatus.StatusCode<> 0然后
sPing =timeout'oRetStatus.StatusCode
Else
sPing = sPing& vbTab& oRetStatus.ResponseTime& Chr(10)
结束如果
下一个
结束函数
如果我写了sPing(),我可以得到结果,但是我想让它得到列表中的pc的名字。
这只是脚本的测试版本,我现在用一个pc进行测试,这就是为什么我使用MsgBox。
谢谢
Sub pingall_Click()
子例程中的第二行是一个抛出 Object Required
错误。例如以下行。
c = Target.Name
如果你评论或删除它,它的工作。 (我试过)。
此外,您不应将函数 sPing
中的返回值分配给 C
。
因为这样做会覆盖您在单元格中的服务器/ IP地址的名称,因为forloop一次循环使用1个单元格,使用 c
所以,将它分配回一个新的字符串变量,然后用它做任何你想要的。
I am working on a project. My goal is, to ping all of the computers from an excel list, but can't figure out why it isn't working. I am quite new at this programming language, and I am sure that I miss out something, because I get the error message: Object required
so here is my code
the main:
Sub pingall_Click()
Dim c As Range
c = Target.Name
For Each c In Range("A1:N50")
If (Left(c, 1) = "C" Or Left(c, 1) = "T") And IsNumeric(Right(c, 6)) And Len(c) = 7 Then
c = sPing(c)
If c = "timeout" Then
MsgBox "timeout"
ElseIf c < 16 And c > -1 Then
MsgBox "ok"
ElseIf c > 15 And c < 51 Then
MsgBox "not ok"
ElseIf c > 50 And c < 4000 Then
MsgBox "big delay"
Else
MsgBox "error"
End If
End If
Next c
End Sub
The function:
Public Function sPing(sHost) As String
Dim oPing As Object, oRetStatus As Object
Set oPing = GetObject("winmgmts:{impersonationLevel=impersonate}").ExecQuery _
("select * from Win32_PingStatus where address = '" & sHost & "'")
For Each oRetStatus In oPing
If IsNull(oRetStatus.StatusCode) Or oRetStatus.StatusCode <> 0 Then
sPing = "timeout" 'oRetStatus.StatusCode
Else
sPing = sPing & vbTab & oRetStatus.ResponseTime & Chr(10)
End If
Next
End Function
I can get the result if I write sPing(""), but I want it to get the name of pc-s that are in the list. This is just a test version of the script, I am testing it with one pc for now, that is why I use "MsgBox".
Thank you
The 2nd line inside the Sub pingall_Click()
subroutine is the one throwing the Object Required
error. i.e. the following line.
c = Target.Name
If you comment it out or delete it, it works. (I tried it.)
Also, you should not be assigning the return value from the function sPing
back to c
.
Because doing so will overwrite the name of the Server / IP address you have in the cell, since the forloop is looping over 1 cell at a time using the c
variable.
So instead, assign it back to a new string variable, and then do whatever you want with it.
这篇关于excel vba ping电脑列表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!