Trying to Sort and Subtotal with Vbscript in Excel - found a working solution

[Jeremy Smith]

2 solutions.

The one that I finally figured out, will work with the spreadsheet not
saved yet.

objExcel.worksheets(Var1).Activate
' Set the sort key to be G
objExcel.Columns("G:G").Select
Set Key1 = objExcel.Range("G1")
objExcel.Columns("A:G").Sort Key1,1

' Subtotal where column G changes and count rows
objExcel.Columns("A:G").Select
objExcel.Columns("A:G").Subtotal 7, -4112,7, True, False, True
objExcel.ActiveSheet.Outline.ShowLevels 2

or ' Got the below from Dave Patrick. This works on a spreadsheet that
has been saved already.

Option Explicit
Dim filePath, oExcel, oSheet

filePath = "c:\Test2.xls"
Set oExcel = CreateObject("Excel.Application")
oExcel.Workbooks.Open filepath
Set oSheet = oExcel.ActiveWorkbook.Worksheets(1)
oSheet.Activate
oExcel.DisplayAlerts = False
oSheet.Columns("A:G").Subtotal 7, -4112, Array(7), True, False, True
oExcel.DisplayAlerts = True
oExcel.ActiveWorkbook.Save
oExcel.ActiveWorkbook.Close
set oSheet = Nothing
Set oExcel = Nothing


Last night it came to me that VBS more then likley didn't even need
the array() info. Since array() is a default and that I only needed to
pass the value 7.