Access VBA not finding Excel files on server when using Task Scheduler

Question

Hello I am getting a 1004 error when trying to run my code via the task scheduler. I can run the macro that executes the code directly from Access and it works fine but when running from Task Scheduler it will not find the file path and error handler kicks in and emails me. Any thoughts as to how to fix this? The reason for opening each of these excel files is to run their VBA which updates them to text only from another excel file that contains a lot of complex formulas. Linking to the excel file that had all of the formulas proved too slow.

Here is the code.

Option Compare Database
Option Explicit


Public Function UpdateData()

    Dim xlApp As Object
    Dim mail As CDO.Message
    Dim config As CDO.Configuration

    On Error GoTo 100

    Set xlApp = CreateObject("Excel.Application")

    With xlApp
        .Application.Visible = False
        .Workbooks.Open "V:\DHP\Boards\Access Data\CV1Data.xlsm"
        .Workbooks("CV1Data.xlsm").Save
        .Workbooks.Open "V:\DHP\Boards\Access Data\CV2Data.xlsm"
        .Workbooks("CV2Data.xlsm").Save
        .Workbooks.Open "V:\DHP\Boards\Access Data\CV3Data.xlsm"
        .Workbooks("CV3Data.xlsm").Save
        .Workbooks.Open "V:\DHP\Boards\Access Data\CV4Data.xlsm"
        .Workbooks("CV4Data.xlsm").Save
        .Workbooks.Open "V:\DHP\Boards\Access Data\602Data.xlsm"
        .Workbooks("602Data.xlsm").Save
        .Workbooks.Open "V:\DHP\Boards\Access Data\PVGData.xlsm"
        .Workbooks("PVGData.xlsm").Save
        .Workbooks.Open "V:\DHP\Boards\Access Data\PV24Data.xlsm"
        .Workbooks("PV24Data.xlsm").Save
        .Workbooks.Open "V:\DHP\Boards\Access Data\ReasonCodes.xlsm"
        .Workbooks("ReasonCodes.xlsm").Save
    End With

    xlApp.Quit

    Set xlApp = Nothing

    Application.SetOption "Auto compact", True
    Application.Quit
    Exit Function

100:

    Set mail = CreateObject("CDO.Message")
    Set config = CreateObject("CDO.Configuration")

    config.Fields(cdoSendUsingMethod).Value = cdoSendUsingPort
    config.Fields(cdoSMTPServer).Value = "mr.domain.com"
    config.Fields(cdoSMTPServerPort).Value = 25
    config.Fields.Update

    Set mail.Configuration = config

    With mail
        .To = "[email protected]"
        .From = "[email protected]"
        .Subject = "Error Occured - Error Number " & Err.Number
        .TextBody = "DHPTables Database -" & Err.Description
        .Send
    End With

    Set config = Nothing
    Set mail = Nothing

    Application.SetOption "Auto compact", True
    Application.Quit
End Function

Does the job run as System or with a specific user (have you entered a password for the job?) If it runs as System, you don't have access to network resources — FunThomas
@FunThomas I run it from a user and I have to enter the password when I save the task. I can see it open the db but as I said it goes to the error handler. — Drew Berger
unfortunately I am not the network admin so I am running it from a user workstation. — Drew Berger
Is it possible that the network drive is not mapped to V for this user? Maybe try to use the UNC path to access the file. — Vincent G

Drew Berger Drew Berger · Accepted Answer · 2018-05-23T19:10:03

I found on Microsoft's site that this is not possible for Office products with the "Run whether user is logged on or not" checked. The only way to do this is to leave the user logged on constantly. See the reply from Mark Burns.

https://social.msdn.microsoft.com/Forums/office/en-US/7d81395f-fc98-4e96-90e0-1331cb4dab3d/scheduled-macro-only-runs-if-user-is-logged-in-windows-server-2003r2?forum=accessdev

Access VBA not finding Excel files on server when using Task Scheduler

1 Answers