Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
TEXT and Data converted to only data
I am the sys administrator for a very big network. Each time one of
the computers is restarted on my network a small DOS batch file runs. The first part of this DOS batch file updates a specific directory's exe's with the newest versions from a shared network directory. The second part of this batch file updates a central text file. I can then check this text file to make sure that all the computers have been updated on my network. Here is an example of the text file: (TESTAAA.txt) -------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------- THE DATE: 2007/03/01 ----------- THE TIME: 13:59 ----------- THE COMPUTER BEING SYNCHRONIZED: Windows IP Configuration Host Name . . . . . . . . . . . . : cpf8-wks Primary Dns Suffix . . . . . . . : tantis.local Node Type . . . . . . . . . . . . : Unknown IP Routing Enabled. . . . . . . . : No WINS Proxy Enabled. . . . . . . . : No DNS Suffix Search List. . . . . . : tantis.local Ethernet adapter Local Area Connection: Connection-specific DNS Suffix . : Description . . . . . . . . . . . : Broadcom NetXtreme 57xx Gigabit Controller Physical Address. . . . . . . . . : 00-14-22-5E-F2-4E Dhcp Enabled. . . . . . . . . . . : No IP Address. . . . . . . . . . . . : 192.168.11.191 Subnet Mask . . . . . . . . . . . : 255.255.255.0 Default Gateway . . . . . . . . . : 192.168.11.1 DHCP Class ID . . . . . . . . . . : Internet Users DNS Servers . . . . . . . . . . . : 192.168.11.155 192.168.11.175 -------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------- THE DATE: 2007/03/01 ----------- THE TIME: 14:01 ----------- THE COMPUTER BEING SYNCHRONIZED: Windows IP Configuration Host Name . . . . . . . . . . . . : cpf1-wks Primary Dns Suffix . . . . . . . : tantis.local Node Type . . . . . . . . . . . . : Unknown IP Routing Enabled. . . . . . . . : No WINS Proxy Enabled. . . . . . . . : No DNS Suffix Search List. . . . . . : tantis.local Ethernet adapter Local Area Connection: Connection-specific DNS Suffix . : Description . . . . . . . . . . . : Broadcom NetXtreme 57xx Gigabit Controller Physical Address. . . . . . . . . : 00-14-22-5F-25-E0 Dhcp Enabled. . . . . . . . . . . : No IP Address. . . . . . . . . . . . : 192.168.11.214 Subnet Mask . . . . . . . . . . . : 255.255.255.0 Default Gateway . . . . . . . . . : 192.168.11.1 DNS Servers . . . . . . . . . . . : 192.168.11.155 192.168.11.175 -------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------- THE DATE: 2007/03/01 ----------- THE TIME: 15:19 ----------- THE COMPUTER BEING SYNCHRONIZED: Windows IP Configuration Host Name . . . . . . . . . . . . : cpf8-wks Primary Dns Suffix . . . . . . . : tantis.local Node Type . . . . . . . . . . . . : Unknown IP Routing Enabled. . . . . . . . : No WINS Proxy Enabled. . . . . . . . : No DNS Suffix Search List. . . . . . : tantis.local Ethernet adapter Local Area Connection: Connection-specific DNS Suffix . : Description . . . . . . . . . . . : Broadcom NetXtreme 57xx Gigabit Controller Physical Address. . . . . . . . . : 00-14-22-5E-F2-4E Dhcp Enabled. . . . . . . . . . . : No IP Address. . . . . . . . . . . . : 192.168.11.191 Subnet Mask . . . . . . . . . . . : 255.255.255.0 Default Gateway . . . . . . . . . : 192.168.11.1 DHCP Class ID . . . . . . . . . . : Internet Users DNS Servers . . . . . . . . . . . : 192.168.11.155 192.168.11.175 -------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------- THE DATE: 2007/03/01 ----------- THE TIME: 15:19 ----------- THE COMPUTER BEING SYNCHRONIZED: Windows IP Configuration Host Name . . . . . . . . . . . . : cpf8-wks Primary Dns Suffix . . . . . . . : tantis.local Node Type . . . . . . . . . . . . : Unknown IP Routing Enabled. . . . . . . . : No WINS Proxy Enabled. . . . . . . . : No DNS Suffix Search List. . . . . . : tantis.local Ethernet adapter Local Area Connection: Connection-specific DNS Suffix . : Description . . . . . . . . . . . : Broadcom NetXtreme 57xx Gigabit Controller Physical Address. . . . . . . . . : 00-14-22-5E-F2-4E Dhcp Enabled. . . . . . . . . . . : No IP Address. . . . . . . . . . . . : 192.168.11.191 Subnet Mask . . . . . . . . . . . : 255.255.255.0 Default Gateway . . . . . . . . . : 192.168.11.1 DHCP Class ID . . . . . . . . . . : Internet Users DNS Servers . . . . . . . . . . . : 192.168.11.155 192.168.11.175 -------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------- THE DATE: 2007/03/01 ----------- THE TIME: 15:19 ----------- THE COMPUTER BEING SYNCHRONIZED: Windows IP Configuration Host Name . . . . . . . . . . . . : cpf3-wks Primary Dns Suffix . . . . . . . : tantis.local Node Type . . . . . . . . . . . . : Unknown IP Routing Enabled. . . . . . . . : No WINS Proxy Enabled. . . . . . . . : No DNS Suffix Search List. . . . . . : tantis.local Ethernet adapter Local Area Connection: Connection-specific DNS Suffix . : Description . . . . . . . . . . . : Broadcom NetXtreme 57xx Gigabit Controller Physical Address. . . . . . . . . : 00-13-72-70-2F-25 Dhcp Enabled. . . . . . . . . . . : No IP Address. . . . . . . . . . . . : 192.168.11.196 Subnet Mask . . . . . . . . . . . : 255.255.255.0 Default Gateway . . . . . . . . . : 192.168.11.1 DNS Servers . . . . . . . . . . . : 192.168.11.155 192.168.11.175 It is clear that the same information gets received from the computers and gets repeated. My question is this: I would like to be able to get only the critical information in table form. For example: Date Time IP Host Name 2007/03/01 13:59 192.168.11.1 cpf3-wks 2007/03/01 14:10 192.168.11.3 cpf7-wks 2007/03/01 14:30 192.168.11.18 cpf2-wks If somebody can assist me in having a way to generate this report automatically from a program I would be very very gratefull!!! PLEASE HELP ME!!! Thank you. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
TEXT and Data converted to only data
This is a "quick and dirty" macro to get your data. I copied your text to
NOTEPAD , saved as text and opened the text file in Excel with all data in column A. It llops throught the data 4 times searching for the 4 field you require and stores the data in "StoreData". The output is from Column F of the same text file. Hopefully this will get you started Sub GetData() ' ChDir "C:\Documents and Settings\John\Desktop" Workbooks.OpenText Filename:= _ "C:\Documents and Settings\John\Desktop\Testaa.txt", Origin:=437, StartRow _ :=1, DataType:=xlFixedWidth, FieldInfo:=Array(0, 1), TrailingMinusNumbers _ :=True Dim StoreData() lastrow = Cells(Rows.Count, "A").End(xlUp).Row n = 0 With Worksheets(1).Range("a1:a" & lastrow) Set c = .Find("THE DATE", LookIn:=xlValues) If Not c Is Nothing Then firstAddress = c.Address Do n = n + 1 ReDim Preserve StoreData(4, n) mydate = c.Offset(1, 0).Value StoreData(1, n) = mydate Set c = .FindNext(c) Loop While Not c Is Nothing And c.Address < firstAddress End If End With n = 0 With Worksheets(1).Range("a1:a" & lastrow) Set c = .Find("THE TIME", LookIn:=xlValues) If Not c Is Nothing Then firstAddress = c.Address Do n = n + 1 mydate = c.Offset(1, 0).Value StoreData(2, n) = mydate Set c = .FindNext(c) Loop While Not c Is Nothing And c.Address < firstAddress End If End With n = 0 With Worksheets(1).Range("a1:a" & lastrow) Set c = .Find("IP Address", LookIn:=xlValues) If Not c Is Nothing Then firstAddress = c.Address Do n = n + 1 IP_Address = Right(c.Value, 14) StoreData(3, n) = IP_Address Set c = .FindNext(c) Loop While Not c Is Nothing And c.Address < firstAddress End If End With n = 0 With Worksheets(1).Range("a1:a" & lastrow) Set c = .Find("Host", LookIn:=xlValues) If Not c Is Nothing Then firstAddress = c.Address Do n = n + 1 Host_Name = Right(c.Value, 8) StoreData(4, n) = Host_Name Set c = .FindNext(c) Loop While Not c Is Nothing And c.Address < firstAddress End If End With hdrs = Array("Date", "Time", "IP Address", "Host Name") With Worksheets(1) .Cells(1, "F").Resize(1, 4) = hdrs For i = 1 To n For j = 1 To 4 .Cells(i + 1, j + 5) = StoreData(j, i) Next j Next i ..Columns("G:G").NumberFormat = "h:mm" End With End Sub "Don Guillett" wrote: You could do this with a loop that finds "THE DATE" and uses offset to find the data desired for each. Then reset the starting row to a row lower than the last find and FIND, or even FINDNEXT, "THE DATE" again. I have done this for clients. sr=1 find "THE DATE" get data using offsets sr=10 loop -- Don Guillett SalesAid Software wrote in message ups.com... I am the sys administrator for a very big network. Each time one of the computers is restarted on my network a small DOS batch file runs. The first part of this DOS batch file updates a specific directory's exe's with the newest versions from a shared network directory. The second part of this batch file updates a central text file. I can then check this text file to make sure that all the computers have been updated on my network. Here is an example of the text file: (TESTAAA.txt) -------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------- THE DATE: 2007/03/01 ----------- THE TIME: 13:59 ----------- THE COMPUTER BEING SYNCHRONIZED: Windows IP Configuration Host Name . . . . . . . . . . . . : cpf8-wks Primary Dns Suffix . . . . . . . : tantis.local Node Type . . . . . . . . . . . . : Unknown IP Routing Enabled. . . . . . . . : No WINS Proxy Enabled. . . . . . . . : No DNS Suffix Search List. . . . . . : tantis.local Ethernet adapter Local Area Connection: Connection-specific DNS Suffix . : Description . . . . . . . . . . . : Broadcom NetXtreme 57xx Gigabit Controller Physical Address. . . . . . . . . : 00-14-22-5E-F2-4E Dhcp Enabled. . . . . . . . . . . : No IP Address. . . . . . . . . . . . : 192.168.11.191 Subnet Mask . . . . . . . . . . . : 255.255.255.0 Default Gateway . . . . . . . . . : 192.168.11.1 DHCP Class ID . . . . . . . . . . : Internet Users DNS Servers . . . . . . . . . . . : 192.168.11.155 192.168.11.175 -------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------- THE DATE: 2007/03/01 ----------- THE TIME: 14:01 ----------- THE COMPUTER BEING SYNCHRONIZED: Windows IP Configuration Host Name . . . . . . . . . . . . : cpf1-wks Primary Dns Suffix . . . . . . . : tantis.local Node Type . . . . . . . . . . . . : Unknown IP Routing Enabled. . . . . . . . : No WINS Proxy Enabled. . . . . . . . : No DNS Suffix Search List. . . . . . : tantis.local Ethernet adapter Local Area Connection: Connection-specific DNS Suffix . : Description . . . . . . . . . . . : Broadcom NetXtreme 57xx Gigabit Controller Physical Address. . . . . . . . . : 00-14-22-5F-25-E0 Dhcp Enabled. . . . . . . . . . . : No IP Address. . . . . . . . . . . . : 192.168.11.214 Subnet Mask . . . . . . . . . . . : 255.255.255.0 Default Gateway . . . . . . . . . : 192.168.11.1 DNS Servers . . . . . . . . . . . : 192.168.11.155 192.168.11.175 -------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------- THE DATE: 2007/03/01 ----------- THE TIME: 15:19 ----------- THE COMPUTER BEING SYNCHRONIZED: Windows IP Configuration Host Name . . . . . . . . . . . . : cpf8-wks Primary Dns Suffix . . . . . . . : tantis.local Node Type . . . . . . . . . . . . : Unknown IP Routing Enabled. . . . . . . . : No WINS Proxy Enabled. . . . . . . . : No DNS Suffix Search List. . . . . . : tantis.local Ethernet adapter Local Area Connection: Connection-specific DNS Suffix . : Description . . . . . . . . . . . : Broadcom NetXtreme 57xx Gigabit Controller Physical Address. . . . . . . . . : 00-14-22-5E-F2-4E Dhcp Enabled. . . . . . . . . . . : No IP Address. . . . . . . . . . . . : 192.168.11.191 Subnet Mask . . . . . . . . . . . : 255.255.255.0 Default Gateway . . . . . . . . . : 192.168.11.1 DHCP Class ID . . . . . . . . . . : Internet Users DNS Servers . . . . . . . . . . . : 192.168.11.155 192.168.11.175 -------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------- THE DATE: 2007/03/01 ----------- THE TIME: 15:19 ----------- THE COMPUTER BEING SYNCHRONIZED: Windows IP Configuration Host Name . . . . . . . . . . . . : cpf8-wks Primary Dns Suffix . . . . . . . : tantis.local Node Type . . . . . . . . . . . . : Unknown IP Routing Enabled. . . . . . . . : No WINS Proxy Enabled. . . . . . . . : No DNS Suffix Search List. . . . . . : tantis.local Ethernet adapter Local Area Connection: Connection-specific DNS Suffix . : Description . . . . . . . . . . . : Broadcom NetXtreme 57xx Gigabit Controller Physical Address. . . . . . . . . : 00-14-22-5E-F2-4E Dhcp Enabled. . . . . . . . . . . : No IP Address. . . . . . . . . . . . : 192.168.11.191 Subnet Mask . . . . . . . . . . . : 255.255.255.0 Default Gateway . . . . . . . . . : 192.168.11.1 DHCP Class ID . . . . . . . . . . : Internet Users DNS Servers . . . . . . . . . . . : 192.168.11.155 192.168.11.175 -------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------- THE DATE: 2007/03/01 ----------- THE TIME: 15:19 ----------- THE COMPUTER BEING SYNCHRONIZED: Windows IP Configuration Host Name . . . . . . . . . . . . : cpf3-wks Primary Dns Suffix . . . . . . . : tantis.local Node Type . . . . . . . . . . . . : Unknown IP Routing Enabled. . . . . . . . : No WINS Proxy Enabled. . . . . . . . : No DNS Suffix Search List. . . . . . : tantis.local Ethernet adapter Local Area Connection: Connection-specific DNS Suffix . : Description . . . . . . . . . . . : Broadcom NetXtreme 57xx Gigabit Controller Physical Address. . . . . . . . . : 00-13-72-70-2F-25 Dhcp Enabled. . . . . . . . . . . : No IP Address. . . . . . . . . . . . : 192.168.11.196 Subnet Mask . . . . . . . . . . . : 255.255.255.0 Default Gateway . . . . . . . . . : 192.168.11.1 DNS Servers . . . . . . . . . . . : 192.168.11.155 192.168.11.175 It is clear that the same information gets received from the computers and gets repeated. My question is this: I would like to be able to get only the critical information in table form. For example: |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
TEXT and Data converted to only data
And, assuming that all "batches" are the same size and with the same
offsets, this could be done with only one FINDNEXT for "THE DATE" and the offsets, thereby making it faster. -- Don Guillett SalesAid Software "Toppers" wrote in message ... This is a "quick and dirty" macro to get your data. I copied your text to NOTEPAD , saved as text and opened the text file in Excel with all data in column A. It llops throught the data 4 times searching for the 4 field you require and stores the data in "StoreData". The output is from Column F of the same text file. Hopefully this will get you started Sub GetData() ' ChDir "C:\Documents and Settings\John\Desktop" Workbooks.OpenText Filename:= _ "C:\Documents and Settings\John\Desktop\Testaa.txt", Origin:=437, StartRow _ :=1, DataType:=xlFixedWidth, FieldInfo:=Array(0, 1), TrailingMinusNumbers _ :=True Dim StoreData() lastrow = Cells(Rows.Count, "A").End(xlUp).Row n = 0 With Worksheets(1).Range("a1:a" & lastrow) Set c = .Find("THE DATE", LookIn:=xlValues) If Not c Is Nothing Then firstAddress = c.Address Do n = n + 1 ReDim Preserve StoreData(4, n) mydate = c.Offset(1, 0).Value StoreData(1, n) = mydate Set c = .FindNext(c) Loop While Not c Is Nothing And c.Address < firstAddress End If End With n = 0 With Worksheets(1).Range("a1:a" & lastrow) Set c = .Find("THE TIME", LookIn:=xlValues) If Not c Is Nothing Then firstAddress = c.Address Do n = n + 1 mydate = c.Offset(1, 0).Value StoreData(2, n) = mydate Set c = .FindNext(c) Loop While Not c Is Nothing And c.Address < firstAddress End If End With n = 0 With Worksheets(1).Range("a1:a" & lastrow) Set c = .Find("IP Address", LookIn:=xlValues) If Not c Is Nothing Then firstAddress = c.Address Do n = n + 1 IP_Address = Right(c.Value, 14) StoreData(3, n) = IP_Address Set c = .FindNext(c) Loop While Not c Is Nothing And c.Address < firstAddress End If End With n = 0 With Worksheets(1).Range("a1:a" & lastrow) Set c = .Find("Host", LookIn:=xlValues) If Not c Is Nothing Then firstAddress = c.Address Do n = n + 1 Host_Name = Right(c.Value, 8) StoreData(4, n) = Host_Name Set c = .FindNext(c) Loop While Not c Is Nothing And c.Address < firstAddress End If End With hdrs = Array("Date", "Time", "IP Address", "Host Name") With Worksheets(1) .Cells(1, "F").Resize(1, 4) = hdrs For i = 1 To n For j = 1 To 4 .Cells(i + 1, j + 5) = StoreData(j, i) Next j Next i .Columns("G:G").NumberFormat = "h:mm" End With End Sub "Don Guillett" wrote: You could do this with a loop that finds "THE DATE" and uses offset to find the data desired for each. Then reset the starting row to a row lower than the last find and FIND, or even FINDNEXT, "THE DATE" again. I have done this for clients. sr=1 find "THE DATE" get data using offsets sr=10 loop -- Don Guillett SalesAid Software wrote in message ups.com... I am the sys administrator for a very big network. Each time one of the computers is restarted on my network a small DOS batch file runs. The first part of this DOS batch file updates a specific directory's exe's with the newest versions from a shared network directory. The second part of this batch file updates a central text file. I can then check this text file to make sure that all the computers have been updated on my network. Here is an example of the text file: (TESTAAA.txt) -------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------- THE DATE: 2007/03/01 ----------- THE TIME: 13:59 ----------- THE COMPUTER BEING SYNCHRONIZED: Windows IP Configuration Host Name . . . . . . . . . . . . : cpf8-wks Primary Dns Suffix . . . . . . . : tantis.local Node Type . . . . . . . . . . . . : Unknown IP Routing Enabled. . . . . . . . : No WINS Proxy Enabled. . . . . . . . : No DNS Suffix Search List. . . . . . : tantis.local Ethernet adapter Local Area Connection: Connection-specific DNS Suffix . : Description . . . . . . . . . . . : Broadcom NetXtreme 57xx Gigabit Controller Physical Address. . . . . . . . . : 00-14-22-5E-F2-4E Dhcp Enabled. . . . . . . . . . . : No IP Address. . . . . . . . . . . . : 192.168.11.191 Subnet Mask . . . . . . . . . . . : 255.255.255.0 Default Gateway . . . . . . . . . : 192.168.11.1 DHCP Class ID . . . . . . . . . . : Internet Users DNS Servers . . . . . . . . . . . : 192.168.11.155 192.168.11.175 -------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------- THE DATE: 2007/03/01 ----------- THE TIME: 14:01 ----------- THE COMPUTER BEING SYNCHRONIZED: Windows IP Configuration Host Name . . . . . . . . . . . . : cpf1-wks Primary Dns Suffix . . . . . . . : tantis.local Node Type . . . . . . . . . . . . : Unknown IP Routing Enabled. . . . . . . . : No WINS Proxy Enabled. . . . . . . . : No DNS Suffix Search List. . . . . . : tantis.local Ethernet adapter Local Area Connection: Connection-specific DNS Suffix . : Description . . . . . . . . . . . : Broadcom NetXtreme 57xx Gigabit Controller Physical Address. . . . . . . . . : 00-14-22-5F-25-E0 Dhcp Enabled. . . . . . . . . . . : No IP Address. . . . . . . . . . . . : 192.168.11.214 Subnet Mask . . . . . . . . . . . : 255.255.255.0 Default Gateway . . . . . . . . . : 192.168.11.1 DNS Servers . . . . . . . . . . . : 192.168.11.155 192.168.11.175 -------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------- THE DATE: 2007/03/01 ----------- THE TIME: 15:19 ----------- THE COMPUTER BEING SYNCHRONIZED: Windows IP Configuration Host Name . . . . . . . . . . . . : cpf8-wks Primary Dns Suffix . . . . . . . : tantis.local Node Type . . . . . . . . . . . . : Unknown IP Routing Enabled. . . . . . . . : No WINS Proxy Enabled. . . . . . . . : No DNS Suffix Search List. . . . . . : tantis.local Ethernet adapter Local Area Connection: Connection-specific DNS Suffix . : Description . . . . . . . . . . . : Broadcom NetXtreme 57xx Gigabit Controller Physical Address. . . . . . . . . : 00-14-22-5E-F2-4E Dhcp Enabled. . . . . . . . . . . : No IP Address. . . . . . . . . . . . : 192.168.11.191 Subnet Mask . . . . . . . . . . . : 255.255.255.0 Default Gateway . . . . . . . . . : 192.168.11.1 DHCP Class ID . . . . . . . . . . : Internet Users DNS Servers . . . . . . . . . . . : 192.168.11.155 192.168.11.175 -------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------- THE DATE: 2007/03/01 ----------- THE TIME: 15:19 ----------- THE COMPUTER BEING SYNCHRONIZED: Windows IP Configuration Host Name . . . . . . . . . . . . : cpf8-wks Primary Dns Suffix . . . . . . . : tantis.local Node Type . . . . . . . . . . . . : Unknown IP Routing Enabled. . . . . . . . : No WINS Proxy Enabled. . . . . . . . : No DNS Suffix Search List. . . . . . : tantis.local Ethernet adapter Local Area Connection: Connection-specific DNS Suffix . : Description . . . . . . . . . . . : Broadcom NetXtreme 57xx Gigabit Controller Physical Address. . . . . . . . . : 00-14-22-5E-F2-4E Dhcp Enabled. . . . . . . . . . . : No IP Address. . . . . . . . . . . . : 192.168.11.191 Subnet Mask . . . . . . . . . . . : 255.255.255.0 Default Gateway . . . . . . . . . : 192.168.11.1 DHCP Class ID . . . . . . . . . . : Internet Users DNS Servers . . . . . . . . . . . : 192.168.11.155 192.168.11.175 -------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------- THE DATE: 2007/03/01 ----------- THE TIME: 15:19 ----------- THE COMPUTER BEING SYNCHRONIZED: Windows IP Configuration Host Name . . . . . . . . . . . . : cpf3-wks Primary Dns Suffix . . . . . . . : tantis.local Node Type . . . . . . . . . . . . : Unknown IP Routing Enabled. . . . . . . . : No WINS Proxy Enabled. . . . . . . . : No DNS Suffix Search List. . . . . . : tantis.local Ethernet adapter Local Area Connection: Connection-specific DNS Suffix . : Description . . . . . . . . . . . : Broadcom NetXtreme 57xx Gigabit Controller Physical Address. . . . . . . . . : 00-13-72-70-2F-25 Dhcp Enabled. . . . . . . . . . . : No IP Address. . . . . . . . . . . . : 192.168.11.196 Subnet Mask . . . . . . . . . . . : 255.255.255.0 Default Gateway . . . . . . . . . : 192.168.11.1 DNS Servers . . . . . . . . . . . : 192.168.11.155 192.168.11.175 It is clear that the same information gets received from the computers and gets repeated. My question is this: I would like to be able to get only the critical information in table form. For example: |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
TEXT and Data converted to only data
It would make it easier if you actually used the data that was in your sample
file to generate the report. I'm guessing that you really meant this: Date Time IP Host Name 03/01/2007 13:59:00 192.168.11.191 cpf8-wks 03/01/2007 14:01:00 192.168.11.214 cpf1-wks 03/01/2007 15:19:00 192.168.11.191 cpf8-wks 03/01/2007 15:19:00 192.168.11.191 cpf8-wks 03/01/2007 15:19:00 192.168.11.196 cpf3-wks If that's the case, then this seemed to work ok for me: Option Explicit Sub testme() Dim myFileName As Variant Dim OrigWks As Worksheet Dim RptWks As Worksheet Dim myHeaders As Variant Dim iCtr As Long Dim iRow As Long Dim FirstRow As Long Dim LastRow As Long Dim oRow As Long Dim oCol As Long Dim ColonPos As Long Dim myStr As String myFileName = Application.GetOpenFilename("Txt Files, *.txt") If myFileName = False Then Beep Exit Sub End If myHeaders = Array("the date:", "the time:", "ip address", "host name") Set OrigWks = Workbooks.Open(Filename:=myFileName).Worksheets(1) Set RptWks = Workbooks.Add(1).Worksheets(1) RptWks.Range("a1").Resize(1, 4).Value _ = Array("Date", "Time", "IP", "Host Name") oRow = 1 With OrigWks FirstRow = 1 LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row For iRow = FirstRow To LastRow myStr = Trim(.Cells(iRow, "A").Value) oCol = 0 For iCtr = LBound(myHeaders) To UBound(myHeaders) oCol = oCol + 1 If LCase(Left(myStr, Len(myHeaders(iCtr)))) _ = LCase(myHeaders(iCtr)) Then Select Case oCol Case Is = 1 'Date field oRow = oRow + 1 RptWks.Cells(oRow, oCol).NumberFormat = "mm/dd/yyyy" RptWks.Cells(oRow, oCol).Value _ = Trim(.Cells(iRow + 1, "A").Value) Case Is = 2 'time field RptWks.Cells(oRow, oCol).NumberFormat = "hh:mm:ss" RptWks.Cells(oRow, oCol).Value _ = Trim(.Cells(iRow + 1, "A").Value) Case Is = 3, 4 'IP field or host are about the same ColonPos = InStr(1, myStr, ":", vbTextCompare) If ColonPos 0 Then RptWks.Cells(oRow, oCol).Value _ = Mid(myStr, ColonPos + 1) End If Case Else MsgBox "something horrible happened!" End Select Exit For 'start looking again End If Next iCtr Next iRow End With OrigWks.Parent.Close savechanges:=False End Sub wrote: <snipped Date Time IP Host Name 2007/03/01 13:59 192.168.11.1 cpf3-wks 2007/03/01 14:10 192.168.11.3 cpf7-wks 2007/03/01 14:30 192.168.11.18 cpf2-wks If somebody can assist me in having a way to generate this report automatically from a program I would be very very gratefull!!! PLEASE HELP ME!!! Thank you. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Text Converted to a Picture | Excel Discussion (Misc queries) | |||
Text data being converted to Scientic Notation | Excel Worksheet Functions | |||
Converted data to labels. The 0 in zip codes does not print | New Users to Excel | |||
How do I import fractions without data being converted to dates? | Excel Discussion (Misc queries) |