Excel or Applescript sorting?

Scripting Forums AppleScript | Mac OS X
,
#1

The following script does some fairly simple stuff.

  1. Find all unique records with the latest date (from Details)
  2. Puts those records on Worksheet2 (aka Summary)
  3. Converts dates from text to date form

It is soooo slow. I have Applescript launch it and usually it times out.

I wonder if an Applescript is possible…

Dave

Option Explicit
Sub Summary()
Dim lastrow As Long, path As String, today As Long, Ans As Variant

Application.ScreenUpdating = False
Sheets(“Summary”).Select
Rows(“2:3000”).ClearContents

Sheets(“Details”).Select
lastrow = Cells(3000, 1).End(xlUp).Row
Range(Cells(2, 5), Cells(lastrow, 5)).Select
Selection.FormulaR1C1 = “=DATEVALUE(MID(RC[-3],FIND(”"
“”,RC[-3],FIND(“” “”,RC[-3])+1+1)+1,FIND(“”,“”,RC[-3],FIND(“”
“”,RC[-3],FIND(“” “”,RC[-3])+1+1))-FIND(“” “”,RC[-3],FIND(“” “”,RC[-3])
+1+1)-1) & “”-“” & LEFT(MID(RC[-3],FIND(“” “”,RC[-3])+1,FIND(“”
“”,RC[-3],FIND(“” “”,RC[-3])+1+1)-FIND(“” “”,RC[-3])+1),3) & “” -“” &
MID(RC[-3],FIND(“”,“”,RC[-3],FIND(“” “”,RC[-3],FIND(“” “”,RC[-3])+1+1))
+2,FIND(“” “”,RC[-3],FIND(“”,“”,RC[-3],FIND(“” “”,RC[-3],FIND(“”
“”,RC[-3])+1+1))+2)-FIND(“”,“”,RC[-3],FIND(“” “”,RC[-3],FIND(“”
“”,RC[-3])+1+1))-2))+TIMEVALUE(MID(RC[-3],FIND(“”
“”,RC[-3],FIND(“”,“”,RC[-3],FIND(“” “”,RC[-3],FIND(“” “”,RC[-3])+1+1))
+2)+1,15))"
Selection.NumberFormat = “mm/dd/yy hh:mm:ss AM/PM”
Selection.Copy
Cells(2, 2).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Columns(“B:B”).Select
Selection.NumberFormat = “mm/dd/yy hh:mm:ss AM/PM”
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Columns(“E:E”).ClearContents

Sheets(“Summary”).Select
Range(Sheets(“Details”).Cells(3, 1), Sheets(“Details”).Cells(lastrow,
4)).Copy Cells(2, 1)

lastrow = Cells(3000, 1).End(xlUp).Row
Columns(“A:E”).Select
Selection.Sort Key1:=Range(“A2”), Order1:=xlAscending,
Key2:=Range(“B2”) _
, Order2:=xlDescending, Header:=xlYes, OrderCustom:=1,
MatchCase:= _
False, Orientation:=xlTopToBottom
Range(Cells(2, 5), Cells(lastrow, 5)).FormulaR1C1 =
“=IF(RC1<>R[-1]C1,”“Y”“,”“N”“)”
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Selection.Sort Key1:=Range(“E2”), Order1:=xlDescending,
Key2:=Range(“A2”) _
, Order2:=xlAscending, Header:=xlYes, OrderCustom:=1,
MatchCase:= _
False, Orientation:=xlTopToBottom

Ans = Application.CountIf(Columns(“E”), “Y”)
Rows(Ans + 2 & “:” & 3000).ClearContents
Columns(“E”).ClearContents
ActiveWindow.ScrollRow = 2
Cells(2, 1).Select
End Sub

#2

Doable? Absolutely. There are some good threads round here about getting data in and out of Excel. I would suggest starting with those. Also MacTech has a very good guide on AppleScripting MS Office and how to convert VB macros. I would take a look at that as well.

#3

The link for the MacTech article is here. James is correct about using AppleScript to gather/modify/sort the data, and then populate an Excel SS with it. Plenty of scripters here are ready and willing to help you, once you are a little more clear on how you would like to proceed.

#4

I’m sold. It’s a lot easier dealing only in Applescript vs. trying to make it jibe with MS Office.

I need some script that:
-Sorts Worksheet1
-by Column E
-Descending
-No header row

This goes into a larger script that I am trying to fully automate, so I’d like to automate any dialog boxes that might stop things.

Ideas?

#5

This script will sort column 5 (E column) of the first worksheet in descending order:


tell application "Microsoft Excel"
	tell worksheet 1 to sort column 5 key1 column 5 order1 sort descending
end tell
#6

It says Worksheet doesn’t understand sort.
Hmmm.

	tell application "Microsoft Excel"
		tell active sheet
			sort range "Worksheet1" order1 sort descending key1 column 5 ¬
				order2 sort descending key2 column 2 header header no ¬
				without match case
		end tell
#7

CAS,
I went with your script and got this error:
Microsoft Excel got an error: column 5 of sheet “Details” doesn’t understand the sort message.
Thanks for your help!
dzelnio

#8

It works except for the sort descending thing.

It sorts ascending even thought the script says descending.

	tell application "Microsoft Excel"
		tell sheet "Details" to sort column 5 key1 column 5 order1 sort descending
	end tell