none
VBA script to filter information RRS feed

  • Question

  • Here's the bankground. My group is responsible for assigning IP's to servers that we are getting built. We work for a group that was acquired by a larger company. The larger company has a very good way of handing out IP's, but this group we work for doesn't want to use it. So I have to find a way to make assigning IP's for my group easier. There is a system of record (SOR) that we use, but there are IP's that aren't in it that do respond to ping.  So I created a spreadsheet that compares 3 columns (all IP's, SOR IPs, Ping) and shows which IP's are in all 3 (cells in All IPs turn red if they are in either of the other 2).   The Ping column is an export from an internal website that uses Netscan.  The problem is that the output of this website is not user friendly for this.  It gives "IP address, several spaces, (server name, IP Not Registered, or IP not registered but alive), spaces, Alive or Down, for each IP in the subnet.  I only want the IP's that are in use and not available for the compare spreadsheet.  I did it manually for a while, replacing the spaces with a tab, then copy and pasting into Excel, sorting on the status and then erasing the columns that had the status of IP Not Registered.  Today I came up with a VB Script that would do it for me.  This lead me to the idea of having the code from the script in Excel's VBA.  Is there a way that I could paste the output of the Netscan into a new spreadsheet (in the same workbook) and then click a go button or something, and then the IP's that are left show up in the Ping column of the original spreadsheet?

    This is the code I used for my VBScript

    dim WshShell, fso, f, fx
    dim  inputFile, outputFile, strNetscanOutput, strNewformat, strIPArray
    Const ForReading = 1, ForWriting = 2, ForAppending = 8

    On Error Resume Next

    Set WshShell = Wscript.CreateObject("Wscript.Shell")
    Set fso = CreateObject("Scripting.FileSystemObject")

    inputFile = "C:\Temp\serverList.txt"
    outputFile = "C:\Temp\Results.xls"

    Set f = fso.OpenTextFile(inputFile, ForReading, True)
    Set fx = fso.OpenTextFile(outputFile, ForWriting, True)

     wshShell.Run "notepad " & InputFile, 1, True
      Do While f.AtEndOfLine <> True
       strNetscanOutput = f.Readline
       strNewformat = Replace(strNetscanOutput, "  ", " ")
       strIPArray = Split(strNewformat," ")

       If strIPArray(1) <> "IP not registered" Then
        fx.WriteLine strIPArray(0)
       End If
      Loop
    f.close
    fx.close

    wshShell.Run "excel " & OutputFile

    Thursday, February 11, 2016 7:34 PM

Answers

  • Hi Carl,

    As far as I know, the Excel use Tab delimiter when we paste the data into a workbook. And we can use the paste option or import text file to modify it.

    For example, we can specify the space as the delimiter when we import a text file via setting the TextFileSpaceDelimiter property of table to true. And here is an example to import the text file with Space as delimiter:

     With ActiveSheet.QueryTables.Add(Connection:= _
            "TEXT;C:\a.txt", Destination:=Range("$C$12"))
    
            .Name = "a"
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .TextFilePromptOnRefresh = False
            .TextFilePlatform = 936
            .TextFileStartRow = 1
            .TextFileParseType = xlDelimited
            .TextFileTextQualifier = xlTextQualifierDoubleQuote
            .TextFileConsecutiveDelimiter = True
            .TextFileTabDelimiter = False
            .TextFileSemicolonDelimiter = False
            .TextFileCommaDelimiter = False
            .TextFileSpaceDelimiter = True
            .TextFileColumnDataTypes = Array(1, 1)
            .TextFileTrailingMinusNumbers = True
            .Refresh BackgroundQuery:=False
        End With

    And we can using record macro to get a demo code quickly then modify it based on our requirment.

    Hope it is helpful.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Friday, February 12, 2016 6:05 AM
    Moderator

All replies

  • One thing I forgot to state was that if I just copy and paste the Netscan info into Excel directly, all the info for each line goes into 1 cell,  (IP_address      server_name    Alive).  So after pasting only Column A is populated.
    Thursday, February 11, 2016 7:40 PM
  • Hi Carl,

    As far as I know, the Excel use Tab delimiter when we paste the data into a workbook. And we can use the paste option or import text file to modify it.

    For example, we can specify the space as the delimiter when we import a text file via setting the TextFileSpaceDelimiter property of table to true. And here is an example to import the text file with Space as delimiter:

     With ActiveSheet.QueryTables.Add(Connection:= _
            "TEXT;C:\a.txt", Destination:=Range("$C$12"))
    
            .Name = "a"
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .TextFilePromptOnRefresh = False
            .TextFilePlatform = 936
            .TextFileStartRow = 1
            .TextFileParseType = xlDelimited
            .TextFileTextQualifier = xlTextQualifierDoubleQuote
            .TextFileConsecutiveDelimiter = True
            .TextFileTabDelimiter = False
            .TextFileSemicolonDelimiter = False
            .TextFileCommaDelimiter = False
            .TextFileSpaceDelimiter = True
            .TextFileColumnDataTypes = Array(1, 1)
            .TextFileTrailingMinusNumbers = True
            .Refresh BackgroundQuery:=False
        End With

    And we can using record macro to get a demo code quickly then modify it based on our requirment.

    Hope it is helpful.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Friday, February 12, 2016 6:05 AM
    Moderator