Running Excel Macro Code by SSIS package configured with SQL Server Job agent
Does the service account for the SQL Server Agent have access to the directories for the objects being modified by the Excel macro?  My experience is when code works in your development tools but fails when running through the SQL Server Agent, it is because the service account for the SQL Server Agent is missing permissions that your user account has when running interactively in the development tools.