[SOLVED] Why is this excel macro failing to run non-interactively via task scheduler since a windows update

Why is this excel macro failing to run non-interactively via task scheduler since a windows update

I have a bat file, that executes a vbscript, that runs an excel macro (I'm using Excel 2016). This is running on a Windows Server. Everything worked ok for years until the server was upgraded from Windows Server 2012 to Windows Server 2022.

Since then, the script works perfectly when I run it myself, but crashes at a certain point when I run via task scheduler with the 'run whether the user is logged in or not option'

The macro is:

Sub MegaMacro()
    metaData = Range("metaData")

    'load metadata
    Drive = "D:\Build\SAP\Broadcasts\"
    
    Workbooks.Open Filename:=Drive & metaData, UpdateLinks:=False, ReadOnly:=True
    Workbooks(metaData).Close
    
    ActiveWorkbook.SaveCopyAs Filename:="D:\Build\SAP\Broadcasts\test.xls"
    
End Sub

The point in the macro that the script crashes is when it tries to open another file. If I exclude this row line and the line below that closes the file, it works fine. I've tried to open several files this way and the behaviour is the same, so I don't think the file itself is to blame.

I have created the following folders as I know they're required for this:

C:\Windows\System32\config\systemprofile\Desktop
C:\Windows\SysWOW64\config\systemprofile\Desktop

I'm thinking it must be a difference in the environments between running a application interactively and non-interactively, but I'm unable to determine what the difference is.

Solution

I found this old thread which mentioned some other folders that needed creating. After checking with ProcMon to confirm that my machine was also looking for these folders, I created:

C:\Windows\SysWOW64\config\systemprofile\Documents

C:\Windows\SysWOW64\config\systemprofile\AppData\Local\Microsoft\Windows\INetCache

Now my script is working as expected when run non-interactively