TEXT and Data converted to only data

[aiden_dar]

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.
(e-mail address removed)

 

[Don Guillett]

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

 

[Toppers]

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]

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.

 

[Dave Peterson]

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