1

below is a summary of my issue, any guidance you can provide would be greatly appreciated.

Objective: Use the task scheduler to open Excel and execute a macro that sends a PDF of the Excel sheet via Outlook.

Issue: The Excel file relies on a COM add-in (located in the same path as my file, but a few levels up) to pull data for the report that will be distributed. However, when the task scheduler opens an instance of Excel, the required add-in will not load and therefore the report does not update (other add-ins load just fine). All other parts of the process are working as expected.

What We've Tried: Disabled/enabled the update links prompt (automatically set to update w/o asking). I tried opening a random blank Excel file to see if the add-in would load (it did not). Ensured that all other instances of Excel (i.e., ghost Excels) are closed from the task manager. Reached out to the developers of the add-in, but have not heard back yet. FWIW, when I manually do exactly the steps outlined in the script, everything works fine. It only fails to load the add-in when the script is used to open Excel.

We're fairly new to using the task scheduler and would welcome your thoughts. I've included the script below for your reference. Please let us know if there is any additional information that might be useful.

Thank you for your time and your assistance.

    'Input Excel File's Full Path
  ExcelFilePath = "C:\mypath\myfile.xlsb"

'Input Module/Macro name within the Excel File
  MacroPath = "Module1.SendPDF"

'Create an instance of Excel
  Set ExcelApp = CreateObject("Excel.Application")

'Do you want this Excel instance to be visible?
  ExcelApp.Visible = True  'or "False"

'Prevent any App Launch Alerts (ie Update External Links)
  ExcelApp.DisplayAlerts = False

'Open Excel File
  Set wb = ExcelApp.Workbooks.Open(ExcelFilePath)

'Execute Macro Code
  ExcelApp.Run MacroPath

'Save Excel File (if applicable)
  wb.Save

'Reset Display Alerts Before Closing
  ExcelApp.DisplayAlerts = True

'Close Excel File
  wb.Close

'End instance of Excel
  ExcelApp.Quit
Pᴇʜ
  • 45,553
  • 9
  • 41
  • 62
Studentnyc26
  • 11
  • 2
  • I believe you need to open the add-in programatically. – BigBen Feb 04 '20 at 17:55
  • Thanks for your reply BigBen. Happy to try this, can you direct me to any threads that show how to amend the script to do that? – Studentnyc26 Feb 04 '20 at 18:05
  • Maybe helpful: https://stackoverflow.com/questions/213375/loading-addins-when-excel-is-instantiated-programmatically – BigBen Feb 04 '20 at 18:08
  • Thanks again @BigBen. Maybe this was explained in the thread and I missed it, apologies, if I did, but does the programmatic loading of the add-in, get included in the script that launches Excel or should that be part of the macro that I'm running within the excel file? – Studentnyc26 Feb 04 '20 at 20:24
  • I'd do it in the script that launches Excel. – BigBen Feb 04 '20 at 20:25
  • Thanks again for your help BigBen. I've tried to open the add-in programmatically, but as another fun twist, the add-in names keep changing so doing it in the script is not working (and I've possibly exhausted my knowledge). I spoke to the developers and they said this is not a use case they had envisioned and so it is not currently supported. As an alternative, the post you referenced had some info on a macro to cycle through all the available add-in options and enabling each one, but I haven't been able to make that work yet. Any additional suggestions are very welcome and appreciated. – Studentnyc26 Feb 04 '20 at 23:39
  • If you start your script with task scheduler you can specify under which user the script should run. Under which user do you run it? Is it the same user account as you use for working or is it another user like admin? Check in your task scheduler. If it is another user than your working account you might need to login as that user, start Excel and mark your desired add-in to load on Excel start. I'm not 100% sure but I think Add-In loading is user specific. Worth a try. – Pᴇʜ Feb 05 '20 at 07:31
  • Thank you @PEH, this was a great suggestion. Unfortunately, this does not appear to be my particular issue. As you've suggested I've confirmed that the task scheduler is using my account to run the script, and still no luck. – Studentnyc26 Feb 05 '20 at 14:34

1 Answers1

0

  • Make sure your file is either in the default add-in path of Excel

    C:\Users\<username>\AppData\Roaming\Microsoft\AddIns\

    you can get this path by running MsgBox Application.UserLibraryPath.

  • Or if you use a different path, make sure your custom path is a "Trusted Location" in Excel's Trust Center.

    1. In Excel, click the File Tab, and click Options
    2. In the list at the left, click Trust Center
    3. Click the Trust Center Settings button
    4. In the list at the left, click Trusted Locations
    5. If your folder is not list there, click the Add New Location button
    6. Click the Browse button
    7. Find and select your folder, and click OK
    8. Click OK again, and your folder should appear in the Trusted Location list.
    9. Click OK, twice, to close the windows.

If all this doesn't help, check additionally to the above if this helps: Excel COM add is not loading during startup and have to add it manually.

Pᴇʜ
  • 45,553
  • 9
  • 41
  • 62
  • Thanks again to everyone that posed a solution. Ultimately, what was similar to what was suggested here [https://stackoverflow.com/questions/213375/loading-addins-when-excel-is-instantiated-programmatically], I added a macro that just loaded each of the available addins on startup. Not ideal, but effective. Much appreciated. – Studentnyc26 Feb 07 '20 at 22:02