Excel Macro for ping results from .bat file

[mcranda]

Currently, I have the results of a bat file to ping all of my IPs that show a result in column A, starting at A2.

I have the following formula starting in B2.

=ISNUMBER(SEARCH("100%", A2))

. This will indicate which lines did not ping. However, the data I need is also one cell above that line.

I will need another formula in row C2 that states if B3 was true, make the cell before (B2) true and down the column.

The number of rows will be different each week, is there a way in the macro you can say extend to the last line in column A? Here is what the spreadsheet currently looks like below.

Pinging 10.105.205.79 with 32 bytes of data:	FALSE
Request timed out.	FALSE
	FALSE
Ping statistics for 10.105.205.79:	FALSE
Packets: Sent = 1, Received = 0, Lost = 1 (100% loss),	TRUE
	FALSE
Pinging 10.105.136.231 with 32 bytes of data:	FALSE
Request timed out.	FALSE
	FALSE

<tbody>
</tbody><colgroup><col><col></colgroup>

Thank you

 

[HackSlash]

I think what you are asking for is:

ActiveSheet.Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Row

You can replace "ActiveSheet" with a defined worksheet object to be more specific.

Might I suggest running a legit IP scanner that can output to CSV like Angry IP Scanner?

Angry IP Scanner - the original IP scanner for Windows, Mac and Linux

With a real IP scanner you will get better results than from ping and because it has a command line option you could pull in the data in a similar fashion to how you currently do. Your resultant data will be in CSV and it will import much cleaner in to excel. Save you lots of time.

 

[mcranda]

Could you put the formula I have in column B in the macro also?

I would love an easier way, but I am trying to work around my IT folks. They will not allow download of programs like this

 

[HackSlash]

What? You are writing a network scanner in DOS\excel to get around your IT folks? You know they might not want you running automated network tests at all. You could cause problems on accident if you aren't careful. Granted, it would be hard to do with ping but I've seen it done.

Why can't you get the uptime information you need from IT? They likely have a network scanner and a way of monitoring system uptime. Maybe if you ask nicely they will just do the thing you are trying to do for you?

 

[mcranda]

Could you put this formula in excel?

ActiveSheet.Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Row

. I will just paste it every time I run the file, no worries. I would love to ask my IT for assistance, but they are not tracking all of my IP addresses. I guess I am not working around them, but just doing the ping tests for the equipment that they are not keeping track of. Hard to explain. I have told all my IT support tiers that I am running my own ping tests, so we are covered there. If you cannot assist, I totally understand. Thank you for all your assistance.

 

[HackSlash]

That's macro code (VBA) for the last used row in column A. It returns an integer.

Formula is placed in a cell
"Macro" is VBA code

What you are asking for is not clear. If you are importing this information using a macro you can use my last row example to "in the macro you can say extend to the last line in column A" like you said.

 

[mcranda]

Sorry for the confusion. Earlier I asked to have the following formula =ISNUMBER(SEARCH("100%", A2)) that I input in cell b2 also added into the macro. Thought maybe that wasn't possible. So, I then asked if you could just change what you wrote for the macro to put in excel.

 

[HackSlash]

You can find the search sting ("100%") from a VBA macro. You could also copy your formula down the whole page with the drag box in the lower right hand corner of the cell. Or you could use VBA to copy your formula down all used cells.

What are you trying to do? What is the end goal?

 

[mcranda]

The end goal is vba, so a macro can be ran with formulas in both columns b and c. Sorry I was not clear.

 

[Michael M]

Maybe something like this.....UNTESTED....I don't have EXcel at the moment !!!

Sub MM1()
Dim lr As Long, r As Long
lr = Cells(Rows.Count, "A").End(xlUp).Row
Range("B2:B" & lr).Formula = "=ISNUMBER(SEARCH(""100%"", A2))"
For r = 2 To lr
    If Range("B" & r).Value = "True" Then Range("B" & r - 1).Value = "True"
Next r
End Sub