Any Excel Gurus?

[Lee]

I have a script that collects data from a list of servers. I would
like to accomplish two different things in Excel with this data.
Here is a sample of the data:

CNTXDC01,C:\winnt\$NtUninstallKB925902$,kb925902,05/04/2007 05:38:18,28
CNTXDC01,C:\winnt\$NtUninstallKB930178$,kb930178,05/04/2007 05:38:25,28

CNTXDC02,C:\winnt\$NtUninstallKB931836$,kb931836,05/30/2007 00:29:12,2
CNTXDC02,C:\winnt\$NtUninstallKB932168$,kb932168,05/30/2007 00:28:50,2

CNTXQA01,C:\winnt\$NtUninstallKB824151$,kb824151,11/11/2004 16:35:23,932
CNTXQA01,C:\winnt\$NtUninstallKB825119$,kb825119,11/11/2004 16:36:07,932
CNTXQA01,C:\winnt\$NtUninstallKB826232$,kb826232,11/11/2004 16:38:40,932

CNTXSQL01,C:\winnt\$NtUninstallKB828749$,kb828749,12/05/2004 10:46:41,908
CNTXSQL01,C:\winnt\$NtUninstallKB896424$,kb896424,04/28/2006 01:04:48,399
CNTXSQL01,C:\winnt\$NtUninstallKB914388$,kb914388,08/10/2006 22:37:37,295

First, I would like to sort each group of data by date (Column 4). Each
group is seperated by a blank line and should stay that way.

Second, and somewhate tougher I think, is I want to insert a new
worksheet
and down Column A I would like to have each UNIQUE item from Column 3 and
accross the top in ROW 1 I would like to have each UNIQUE item from
Column A in the original worksheet. Like this:

CNTXDC01 CNTXDC02 CNTXQA01 ...
kb925902
kb930178
kb828749
kb896424
....

And then with that matrix, place the date where it lines up with the
server
name column and the hotfix row.

Can anyone tackle this?

 

[moon]

Lee, are your sample data in a text file, or in a worksheet already?

 

[Lee]

The data comes from a CSV file, but at this point it is in a worksheet.
I am just good enough to do some formatting on it, so I am wanting to
append the code to what I have up to this point. Basically if you save
the data out as a CSV file and open it with Excel, that's where I am at.

 

[moon]

The data comes from a CSV file, but at this point it is in a worksheet.
I am just good enough to do some formatting on it, so I am wanting to
append the code to what I have up to this point. Basically if you save
the data out as a CSV file and open it with Excel, that's where I am at.

OK.

 

[moon]

Please test what I've got until now.
Make sure your CSV-data is in Sheet1.

Option Explicit

Private Type DataFields
f_Server As String
f_Path As String
f_Hotfix As String
f_Date As String
f_Time As String
f_KBytes As String
End Type

Private Sub SplitFields()
Dim wb As Workbook
Dim ws As Worksheet
Dim LastRow, r, l, i, j, k As Integer
Dim txtLine As String
Dim DATA As DataFields
Set wb = ThisWorkbook
Set ws = wb.Sheets(1)
ws.Activate
ws.Rows("1:1").Select
Selection.Insert Shift:=xlDown
ws.Cells(1, 9).Value = "Server"
ws.Cells(1, 10).Value = "Path"
ws.Cells(1, 11).Value = "Hotfix"
ws.Cells(1, 12).Value = "Date"
ws.Cells(1, 13).Value = "Time"
ws.Cells(1, 14).Value = "Kilobytes"
ws.Cells(65536, 1).Select
Selection.End(xlUp).Select
LastRow = ActiveCell.Row
i = 0: j = 1
ClearFields DATA
For r = 2 To LastRow Step 1
txtLine = ws.Cells(r, 1).Value
l = Len(txtLine)
If txtLine <> Empty Then
k = InStr(1, txtLine, Chr(44), vbTextCompare)
DATA.f_Server = Left(txtLine, (k - 1))
txtLine = Right(txtLine, (l - k))
l = Len(txtLine)
k = InStr(1, txtLine, Chr(44), vbTextCompare)
DATA.f_Path = Left(txtLine, (k - 1))
txtLine = Right(txtLine, (l - k))
l = Len(txtLine)
k = InStr(1, txtLine, Chr(44), vbTextCompare)
DATA.f_Hotfix = Left(txtLine, (k - 1))
txtLine = Right(txtLine, (l - k))
l = Len(txtLine)
k = InStr(1, txtLine, Chr(32), vbTextCompare)
DATA.f_Date = Left(txtLine, (k - 1))
txtLine = Right(txtLine, (l - k))
l = Len(txtLine)
k = InStr(1, txtLine, Chr(44), vbTextCompare)
DATA.f_Time = Left(txtLine, (k - 1))
DATA.f_KBytes = Right(txtLine, (l - k))
ws.Cells(r, 9).Value = DATA.f_Server
ws.Cells(r, 10).Value = DATA.f_Path
ws.Cells(r, 11).Value = DATA.f_Hotfix
ws.Cells(r, 12).Value = DATA.f_Date
ws.Cells(r, 13).Value = DATA.f_Time
ws.Cells(r, 14).Value = DATA.f_KBytes
ClearFields DATA
End If
Next r
Set ws = Nothing
Set wb = Nothing
SortData (LastRow)
End Sub

Private Sub ClearFields(d As DataFields)
d.f_Server = vbNullString
d.f_Path = vbNullString
d.f_Hotfix = vbNullString
d.f_Date = vbNullString
d.f_Time = vbNullString
d.f_KBytes = vbNullString
End Sub

Private Sub SortData(lr As Integer)
Dim wb As Workbook
Dim ws As Worksheet
Dim r, k, c As Integer
Set wb = ThisWorkbook
Set ws = wb.Sheets(1)
r = 2
ws.Cells(r, 9).Select
While r < lr
Range(Selection, Selection.End(xlDown)).Select
k = Selection.Count - 1
Range("I" & r & ":N" & (r + k)).Select
Selection.Sort Key1:=Range("L" & r), Order1:=xlAscending,
Key2:=Range("M" & r) _
, Order2:=xlAscending, Header:=xlNo, OrderCustom:=1,
MatchCase:=False, _
Orientation:=xlTopToBottom
r = r + k + 2
ws.Cells(r, 9).Select
Wend
Set ws = Nothing
Set wb = Nothing
End Sub

 

[juser69]

I get:

Run-time error '1004':

Select method of Range class failed

On line 29 "ws.Rows("1:1").Select"

I know I am probably doing something wrong, but I can't
figure it out yet. Maybe the way I am calling your
routines? I have yet to figure out how one sub can call
another, especially in a different module, so far, not
even in the same module

'-------------------------------------------------
Attribute VB_Name = "Test2"
Option Explicit
Private Type DataFields
f_Server As String
f_Path As String
f_Hotfix As String
f_Date As String
f_Time As String
f_KBytes As String
End Type

Sub Test2()

SplitFields

End Sub



Private Sub SplitFields()
Dim wb As Workbook
Dim ws As Worksheet
Dim LastRow, r, l, i, j, k As Integer
Dim txtLine As String
Dim DATA As DataFields
Set wb = ThisWorkbook
Set ws = wb.Sheets(1)
ws.Activate
ws.Rows("1:1").Select
Selection.Insert Shift:=xlDown
ws.Cells(1, 9).Value = "Server"
ws.Cells(1, 10).Value = "Path"
ws.Cells(1, 11).Value = "Hotfix"
ws.Cells(1, 12).Value = "Date"
ws.Cells(1, 13).Value = "Time"
ws.Cells(1, 14).Value = "Kilobytes"
ws.Cells(65536, 1).Select
Selection.End(xlUp).Select
LastRow = ActiveCell.Row
i = 0: j = 1
ClearFields DATA
For r = 2 To LastRow Step 1
txtLine = ws.Cells(r, 1).Value
l = Len(txtLine)
If txtLine <> Empty Then
k = InStr(1, txtLine, Chr(44), vbTextCompare)
DATA.f_Server = Left(txtLine, (k - 1))
txtLine = Right(txtLine, (l - k))
l = Len(txtLine)
k = InStr(1, txtLine, Chr(44), vbTextCompare)
DATA.f_Path = Left(txtLine, (k - 1))
txtLine = Right(txtLine, (l - k))
l = Len(txtLine)
k = InStr(1, txtLine, Chr(44), vbTextCompare)
DATA.f_Hotfix = Left(txtLine, (k - 1))
txtLine = Right(txtLine, (l - k))
l = Len(txtLine)
k = InStr(1, txtLine, Chr(32), vbTextCompare)
DATA.f_Date = Left(txtLine, (k - 1))
txtLine = Right(txtLine, (l - k))
l = Len(txtLine)
k = InStr(1, txtLine, Chr(44), vbTextCompare)
DATA.f_Time = Left(txtLine, (k - 1))
DATA.f_KBytes = Right(txtLine, (l - k))
ws.Cells(r, 9).Value = DATA.f_Server
ws.Cells(r, 10).Value = DATA.f_Path
ws.Cells(r, 11).Value = DATA.f_Hotfix
ws.Cells(r, 12).Value = DATA.f_Date
ws.Cells(r, 13).Value = DATA.f_Time
ws.Cells(r, 14).Value = DATA.f_KBytes
ClearFields DATA
End If
Next r
Set ws = Nothing
Set wb = Nothing
SortData (LastRow)
End Sub

Private Sub ClearFields(d As DataFields)
d.f_Server = vbNullString
d.f_Path = vbNullString
d.f_Hotfix = vbNullString
d.f_Date = vbNullString
d.f_Time = vbNullString
d.f_KBytes = vbNullString
End Sub

Private Sub SortData(lr As Integer)
Dim wb As Workbook
Dim ws As Worksheet
Dim r, k, c As Integer
Set wb = ThisWorkbook
Set ws = wb.Sheets(1)
r = 2
ws.Cells(r, 9).Select
While r < lr
Range(Selection, Selection.End(xlDown)).Select
k = Selection.Count - 1
Range("I" & r & ":N" & (r + k)).Select
Selection.Sort Key1:=Range("L" & r), Order1:
=xlAscending, Key2:=Range("M" & r) _
, Order2:=xlAscending, Header:=xlNo, OrderCustom:=1,
MatchCase:=False, _
Orientation:=xlTopToBottom
r = r + k + 2
ws.Cells(r, 9).Select
Wend
Set ws = Nothing
Set wb = Nothing
End Sub
'-------------------------------------------------

 

[moon]

The way you call SplitFields seems to be correct. You can also use:

Sub Test2()
Call SplitFields
End Sub

but that doesn't cause the run-time error.
I'm not sure, but maybe it's because I run Excel 2000.