I had an MS Access app that was running a macro via Task Scheduler using the /x parameter. The macro was calling a public function, which is the only way you can call code from outside MS Access. The job would import a CSV file and it would work fine through the command prompt or via a batch file. BUT Task Scheduler would NOT run it. I would always get this issue!!

Microsoft Excel cannot access the file 'C:\Development\MSAccess\Import\ImportFile.csv'. There are several possible reasons:

• The file name or path does not exist. • The file is being used by another program. • The workbook you are trying to save has the same name as a currently open workbook.

So the solution for me was to download this: https://www.splinterware.com/products/scheduler.html

It's working now! If I have to pay for it at some point then $30 USD is well worth it for the days that I have spent trying to figure out why Task Scheduler won't run the job properly!

I posted this here too: Scheduled MS Access macro to run query and export to CSV - Stack Overflow

I recently had the need to create workbooks and worksheets from scratch so this is what I did. No error trapping was added in order to keep the example simple!

Public Sub CreateExcelWorkBookAndSheetsTest()

    Dim xlsNew As Excel.Application
    Dim wkbNew As Excel.Workbook
    Dim wksNew As Excel.Worksheet
    Dim strNewWorkBook As String
    Dim strWorkBookName As String
    
    ' Create the New Workbook Name
    strWorkBookName = "TEST EXCEL FROM MSACCESS VBA"
    strNewWorkBook = CurrentProject.Path & "\" & strWorkBookName & ".xlsx"
        
    If Dir(strNewWorkBook) <> "" Then
        Kill strNewWorkBook
    End If
    
    ' Create a New Excel File and add the WorkSheet to it. Then add the WorkSheet.
    Set xlsNew = New Excel.Application
    
    ' New Workbook
    Set wkbNew = xlsNew.Workbooks.Add
    
    ' Create Workbook and name the first Worksheet Index. By default it will be Sheet1
    ' Reference Worksheets(1) because this is the first worksheet that is created with the initial workbook!
    Set wksNew = wkbNew.Worksheets(1)
    wksNew.Name = "Index"
    ' Use SaveAs only once at the beginning!
    wkbNew.SaveAs strNewWorkBook, xlOpenXMLWorkbook
    
    ' Add another Worksheet and name it Main Data 1. Create a new reference to the worksheet using Count!
    Set wksNew = wkbNew.Worksheets(wkbNew.Sheets.Count)
    ' Add the worksheet to the workbook "After" the wksNew reference that we created above.
    wkbNew.Sheets.Add After:=wksNew
    wkbNew.Sheets(wkbNew.Sheets.Count).Name = "Main Data 1"
    ' Just need to use Save now as we have already saved it once.
    wkbNew.Save
    
    ' Add another Worksheet and name it Main Data 2. Same comments as above.
    ' You can use After:= to reference the [After] parameter or you can just used the parameter like below.
    Set wksNew = wkbNew.Worksheets(wkbNew.Sheets.Count)
    wkbNew.Sheets.Add , wksNew
    wkbNew.Sheets(wkbNew.Sheets.Count).Name = "Main Data 2"
    ' Just need to use Save now as we have already saved it once.
    wkbNew.Save
    
    ' Add a Final Worksheet call Total
    Set wksNew = wkbNew.Worksheets(wkbNew.Sheets.Count)
    wkbNew.Sheets.Add , wksNew
    wkbNew.Sheets(wkbNew.Sheets.Count).Name = "Total"
    ' Just need to use Save now as we have already saved it once.
    wkbNew.Save

    ' Now you can create another worksheet and put it before or after another one!
    Set wksNew = wkbNew.Worksheets(wkbNew.Sheets.Count)
    wkbNew.Sheets.Add Before:=wksNew
    ' We use the -1 in this case as we want to make sure we Name the sheet before the reference "Before" using wksNew
    wkbNew.Sheets(wkbNew.Sheets.Count - 1).Name = "Final Test"
    ' Just need to use Save now as we have already saved it once.
    wkbNew.Save

    ' Now we just add 5 new sheets! These are added starting at the beginning of the last Sheet (workbook) we added, Final Test
    wkbNew.Sheets.Add Count:=5

    Set xlsNew = Nothing

    Application.FollowHyperlink strNewWorkBook
End Sub