Sorting Data in an array

[kd4dna]

Problem: Data within a table in PowerPoint. Column 1 is date and time, in column 2 is a string signifying the event type, i.e. BMNT or SR or SS, etc.
I can read data from this table and put it into a multidimensional array if I make the data type Variant. when I run a bubble sort routine on the data as a variant it does not sort properly, i.e. chronological. If I dim the array as date and load the array with just the date column info, the bubble sort works fine. but I don't have the event string that matches the date/time. If I try to load the string data I get a type mismatch error.

Is there a way to specify different data types within an array?

Is there a better method to Sort the data than the bubble sort routine?

This would be so much easier in excel!

Any help would be appreciated.

Thanks
Ron

 

[Macropod]

You could, of course, automate an Excel session to do the sorting. Here's how you might do it by automating Word:

Sub Demo()
Dim wdApp As Word.Application
Set wdApp = New Word.Application
Dim wdDoc As Word.Document
Set wdDoc = wdApp.Documents.Add
wdApp.Visible = False
Dim Shp As Shape, i As Long, j As Long
Set Shp = ActivePresentation.Slides(1).Shapes(1)
Shp.Copy
With wdDoc
  .Range.Paste
  With .Tables(1)
    .Sort ExcludeHeader:=True, FieldNumber:=2, SortFieldType:=wdSortFieldAlphanumeric, SortOrder:=wdSortOrderAscending, _
    FieldNumber2:=1, SortFieldType2:=wdSortFieldDate, SortOrder2:=wdSortOrderDescending
    For i = 2 To .Rows.Count
      For j = 1 To .Columns.Count
        Shp.Table.Cell(i, j).Shape.TextFrame.TextRange.Text = Left(.Cell(i, j).Range.Text, Len(.Cell(i, j).Range.Text) - 2)
      Next
    Next
  End With
  .Close Savechanges:=False
End With
wdApp.Quit
Set wdDoc = Nothing: Set wdApp = Nothing
End Sub

As the code uses early binding, you'd need to set a reference to the Word Object model.

 

[kd4dna]

Paul,Thanks this procedure does work, but I have since discovered that I need to do some manipulation of the numbers which would be better done in excel. How do I automate an instance of excel so I can further manipulate the numbers?


Ron

 

[Macropod]

How do I automate an instance of excel so I can further manipulate the numbers?

Basically the same as I've shown for Word. That said, the real issue with Excel is that you then have to invest a lot of extra effort into managing column widths, row heights used ranges, etc, which using a Word table handles automatically. And Word is quite capable of manipulating table data too ...

 

[Joe4]

I've removed your email address from your first post. Generally speaking, you should never post your email address publicly on the internet like that, unless you really enjoy getting lots of Spam (Spam-bots routinely patrol forums on the internet looking for email addresses to harvest).

If you wish to share your email address with anyone, it is best to do that via a Private Message.

 

[kd4dna]

Thank for that Joe4, I wasn't thinking of that.

I got the code to work in excel, and accomplished what I needed.

Thanks for all the help.

Ron