none
Excel macro does not run properly when started by task scheduler in Windows 10 RRS feed

  • Question

  • I have a task set to open a excel xlsm file in Windows 10. The macro in the file loads up imported data and saves a backup file in a different location. It runs with no problems when I run it manually in Excel. But when it runs as a task in the Windows 10 scheduler it gives me a runtime error 1004.

    Here is my macro code:

    Sub Workbook_Open()
    'Working in Excel 97-2016
        Dim FileExtStr As String
        Dim FileFormatNum As Long
        Dim Sourcewb As Workbook
        Dim Destwb As Workbook
        Dim TempFilePath As String
        Dim TempFileName As String
    
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With
    
    Set Sourcewb = ActiveWorkbook
    ActiveWorkbook.RefreshAll
        Do While Application.CalculationState <> xlDone
            DoEvents
        Loop
    
    'Copy the sheet to a new workbook
    ActiveSheet.Copy
    Set Destwb = ActiveWorkbook
    
    'Determine the Excel version and file extension/format
    With Destwb
        If Val(Application.Version) < 12 Then
            'You use Excel 97-2003
            FileExtStr = ".xls": FileFormatNum = -4143
        Else
            'You use Excel 2007-2016
            FileExtStr = ".xls": FileFormatNum = 56
            End If
    End With
    
        'Change all cells in the worksheet to values if you want
        With Destwb.Sheets(1).UsedRange
            .Cells.Copy
            .Cells.PasteSpecial xlPasteValues
            .Cells(1).Select
        End With
        Application.CutCopyMode = False
    
    'Delete Empty Rows and first column
    [1:2].SpecialCells(xlBlanks).EntireRow.Delete
    Destwb.Sheets(1).Columns(1).EntireColumn.Delete
    
    'Save the new workbook and close it
    TempFilePath = "Y:\\Google Drive\ExcelSMS\"
    TempFileName = "AutoSMS-" & Format(DateAdd("n", 10, Now()), "mm-dd-yyyy-hh-nn")
    
    With Destwb
        .SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum
        .Close SaveChanges:=False
    End With
    
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
    End Sub


    These are my scheduler settings: General - Run only when user is logged on - Configure for windows 10

    Actions - Program "C:\Program Files\Microsoft Office\root\Office16\EXCEL.EXE" - Argurment "c:\Users\dl7l\OneDrive\AutoRun\Late_Member_Fee_SMS.xlsm"

    Conditions - Wake - Start on any connection

    Setting - On demand - Stop if longer than 3 days - Force stop

    This macro was running fine on windows 7 but have been hitting alot of problems since I upgraded to 10.





    • Edited by aetheramd Wednesday, December 21, 2016 1:51 AM
    Wednesday, December 21, 2016 1:41 AM

Answers

  • Hi aetheramd,

    I find that this error occurs when Programmatic access to the   Visual Basic Project is not trusted.

    The default setting is to not trust programmatic access to   the Visual Basic project.

    please click on "File" Tab.

    click on "Options".

    then click on "Trust Center". then click on "Trust Center Settings".

    then click on "Macro Settings".

    make sure "Trust access to the VBA project object model " is checked.

    Try to check this and let me know about results.

    also to Enable all data connections in "External content" Tab.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by aetheramd Tuesday, January 10, 2017 4:01 AM
    Friday, January 6, 2017 6:11 AM
    Moderator

All replies

  • Hello,

    Microsoft does not currently recommend, and does not support, Automation of Microsoft Office applications from any unattended, non-interactive client application or component (including ASP, ASP.NET, DCOM, and NT Services), because Office may exhibit unstable behavior and/or deadlock when Office is run in this environment.

    If you are building a solution that runs in a server-side context, you should try to use components that have been made safe for unattended execution. Or, you should try to find alternatives that allow at least part of the code to run client-side. If you use an Office application from a server-side solution, the application will lack many of the necessary capabilities to run successfully. Additionally, you will be taking risks with the stability of your overall solution. Read more about that in the Considerations for server-side Automation of Office article.

    As a workaround you may consider using Open XML SDK if you deal with open MXL documents only, see Welcome to the Open XML SDK 2.5 for Office for more information. Or just consider using any third-party components designed for the server-side or service execution.


    profile for Eugene Astafiev at Stack Overflow, Q&A for professional and enthusiast programmers


    Wednesday, December 21, 2016 12:45 PM
  • Hi aetheramd,

    from your description it looks like you have following settings in task scheduler.

    first I suggest you to debug the code when error occurs and try to see on which line you got the error.

    I agree with the suggestion given by the Eugene  Astafiev.

    so try to check for this and if possible then we can try to correct it so that it can work on win10.

    we can't reproduce the issue because we don't have the file used by you and also you try to save the file on google drive.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, December 22, 2016 1:55 AM
    Moderator
  • so I tried debugging and the error is in this line:

    .SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum

    I am not building anything significant. Just automating a pesky task that repeats every 5 days. I am not a developer either so I don't fully understand what Eugene said... 

    Thursday, January 5, 2017 7:49 AM
  • Hi aetheramd,

    I find that this error occurs when Programmatic access to the   Visual Basic Project is not trusted.

    The default setting is to not trust programmatic access to   the Visual Basic project.

    please click on "File" Tab.

    click on "Options".

    then click on "Trust Center". then click on "Trust Center Settings".

    then click on "Macro Settings".

    make sure "Trust access to the VBA project object model " is checked.

    Try to check this and let me know about results.

    also to Enable all data connections in "External content" Tab.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by aetheramd Tuesday, January 10, 2017 4:01 AM
    Friday, January 6, 2017 6:11 AM
    Moderator
  • Thanks Deepak

    That solved it! 

    Tuesday, January 10, 2017 4:01 AM