1

Goal

The goal of this task is to:

  1. Find the most recent Excel file in the directory,
  2. Sort that file using a custom list (defined in code below),
  3. Save the sorted file under the same name, and
  4. Have Windows Scheduler run the VB script every 10 minutes.

My attempt

I have compiled this code, yet I get an error message when I try to run it: 

'Find the most recent file in directory
Option Explicit  
Dim fso, path, file, recentDate, recentFile, objExcel, objWorkbook, objRange, objWorksheet, SortCol
Set fso = CreateObject("Scripting.FileSystemObject")
Set recentFile = Nothing
For Each file in fso.GetFolder("C:\Users\joe\Documents\test\").Files
  If (recentFile is Nothing) Then
    Set recentFile = file
  ElseIf file.DateLastModified > recentFile.DateLastModified Then
  If Left(recentFile.name, 2) = "~$" Then
    Set recentFile = file
  End If
  End If
Next

'Open the most recent file
Set objExcel = CreateObject("Excel.Application") 
Set objWorkbook = objExcel.Workbooks.Open(recentFile.Path)
Set objWorksheet = objWorkbook.Worksheets("Sheet1")

'Sort by custom array
objWorksheet.Sort.SortFields.Clear
objWorksheet.Sort.SortFields.Add key:=Range("A1:A20") _
        , SortOn:=xlSortOnValues, Order:=xlDescending, CustomOrder:= _
        "alpha,bravo,charlie,delta,echo,foxtrot,golf,hotel,india,juliet", DataOption _
        :=xlSortNormal
    With objWorksheet.Sort
        .SetRange Range("A1:B20")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

'Save Excel workbook with same name
objWorkbook.save 
objExcel.quit

Error message

Line: 23
Char: 38
Error: Expected statement
Code: 800A0400
Source: Microsoft VBScript compilationerror

amanda52
  • 11
  • 3

1 Answers1

1

You are using, in parts, a syntax VBS doesn't understand. VBS only does positional not named parameters. Also Excel constants are not available to external programs.

objWorksheet.Sort.SortFields.Add key:=Range("A1:A20") _
        , SortOn:=xlSortOnValues, Order:=xlDescending, CustomOrder:= _
        "alpha,bravo,charlie,delta,echo,foxtrot,golf,hotel,india,juliet", DataOption _
        :=xlSortNormal

So all those constants need to be looked up in Excel's Object Browser and the value used.

Const xlSortNormal = 0
    Member of Excel.XlSortDataOption

You can look up the others.

Assuming your positions are correct

objWorksheet.Sort.SortFields.Add Range("A1:A20"), Enter Value, Enter Value, "alpha,bravo,charlie,delta,echo,foxtrot,golf,hotel,india,juliet", 0

My answer here has VBA tips and a link to a page with Excel tips. Pointers needed for speeding up nested loop macro in VBA

Also VBS help can be downloaded at http://download.microsoft.com/download/winscript56/Install/5.6/W982KMeXP/EN-US/scrdoc56en.exe.

Community
  • 1
  • 1