VBA and Windows Scheduler

[AdriLand]

Good day.

I have a functional Excel knowledge; however, is very new to task automations. Essentially, I want to automate the following process using Windows Scheduler and Excel Macros:
- Open Excel
- Open PERSONAL.XLSB
- Open latest file in the specified path
- Run Macro called OutstandingInvoiceReport
- Save and upload changes

I have done the following:
- Created a new module in the VBA editor in Excel, which contains the following:

Sub RunOutstandingInvoiceReport()
Dim xlApp As Object
Dim xlBook As Object
Dim personalBook As Object
Dim folderPath As String
Dim newestFile As String
Dim newestDate As Date
Dim fileDate As Date
Dim file As Object
Dim fso As Object

folderPath = "C:\Users\AdriL\OneDrive - FC\SEP\2024 2025\Outstanding Reports - Daily"
Set fso = CreateObject("Scripting.FileSystemObject")
newestDate = DateSerial(1900, 1, 1)

For Each file In fso.GetFolder(folderPath).Files
fileDate = file.DateLastModified
If fileDate > newestDate Then
newestDate = fileDate
newestFile = file.Path
End If
Next file

If newestFile <> "" Then
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = False ' Keep Excel hidden

' Open PERSONAL.XLSB
Set personalBook = xlApp.Workbooks.Open(xlApp.Application.StartupPath & "\PERSONAL.XLSB")

' Open the newest file
Set xlBook = xlApp.Workbooks.Open(newestFile)

' Run the macro
xlApp.Run "OutstandingInvoiceReport"

' Save and close the newest file
xlBook.Save
xlBook.Close

' Close PERSONAL.XLSB
personalBook.Close SaveChanges:=False

' Quit Excel
xlApp.Quit

Set xlBook = Nothing
Set personalBook = Nothing
Set xlApp = Nothing
Else
MsgBox "No files found in the specified folder."
End If

Set fso = Nothing
End Sub

I then created a new file in Notepad, pasted the same script into it and saved it as Macro For OutstandingInvoiceReport.vbs

When I try to run it, I receive the following warning:
Line 2
Char 15
Error Expected end of statement
Code 800A0401
Source Microsoft BBScript compilation error


Can someone please tell me where I am going wrong? Any advice will be greatly appreciated. I use Microsoft / Excel 365 and Windows 11, if it is makes a difference.

Thank you!

 

[Micron]

Maybe this is the reason?
RunOutstandingInvoiceReport() <<name of your sub that you posted
OutstandingInvoiceReport << Call that you are trying to make?