Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Any Excel Gurus?
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
Lee Lee is offline
external usenet poster
  
Posts: 2
Default Any Excel Gurus?


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,0 5/04/2007 05:38:18,28
CNTXDC01,C:\winnt\$NtUninstallKB930178$,kb930178,0 5/04/2007 05:38:25,28

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

CNTXQA01,C:\winnt\$NtUninstallKB824151$,kb824151,1 1/11/2004 16:35:23,932
CNTXQA01,C:\winnt\$NtUninstallKB825119$,kb825119,1 1/11/2004 16:36:07,932
CNTXQA01,C:\winnt\$NtUninstallKB826232$,kb826232,1 1/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?
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 20
Default Any Excel Gurus?
Lee, are your sample data in a text file, or in a worksheet already?


"Lee" schreef in bericht
54...


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,0 5/04/2007 05:38:18,28
CNTXDC01,C:\winnt\$NtUninstallKB930178$,kb930178,0 5/04/2007 05:38:25,28

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

CNTXQA01,C:\winnt\$NtUninstallKB824151$,kb824151,1 1/11/2004 16:35:23,932
CNTXQA01,C:\winnt\$NtUninstallKB825119$,kb825119,1 1/11/2004 16:36:07,932
CNTXQA01,C:\winnt\$NtUninstallKB826232$,kb826232,1 1/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?


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
Lee Lee is offline
external usenet poster
  
Posts: 2
Default Any Excel Gurus?
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" wrote in
:

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


"Lee" schreef in bericht
54...


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,0 5/04/2007
05:38:18,28
CNTXDC01,C:\winnt\$NtUninstallKB930178$,kb930178,0 5/04/2007
05:38:25,28

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

CNTXQA01,C:\winnt\$NtUninstallKB824151$,kb824151,1 1/11/2004
16:35:23,932
CNTXQA01,C:\winnt\$NtUninstallKB825119$,kb825119,1 1/11/2004
16:36:07,932
CNTXQA01,C:\winnt\$NtUninstallKB826232$,kb826232,1 1/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?




Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 20
Default Any Excel Gurus?

"Lee" schreef in bericht
52...
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.


Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 20
Default Any Excel Gurus?
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


Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1
Default Any Excel Gurus?
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
'-------------------------------------------------



Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 20
Default Any Excel Gurus?

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.



"juser69" schreef in bericht
g.com...
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
'-------------------------------------------------





Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel Gurus-Please Help! Ginny Excel Discussion (Misc queries) 1 November 1st 07 05:22 PM
OK, a really tricky one now for Excel gurus! tim :/ Excel Discussion (Misc queries) 2 October 25th 05 12:55 PM
Excel VBA Gurus needed Career Capital Excel Discussion (Misc queries) 0 September 9th 05 05:46 AM
Looking for Excel VBA gurus Career Capital Excel Programming 0 September 9th 05 05:29 AM
For the Excel Query Gurus Andrew Excel Worksheet Functions 4 February 25th 05 06:14 AM


All times are GMT +1. The time now is 04:53 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement