I have an excel file which is connected to data source use an ODBC driver. I can open the excel and refresh the data withought any issues. However I want to automate the process to refresh the excel the data. I tried macros and it worked fine when I run it manually but when I call it from a SQL Agent job the refresh fails as the excel file has to open then refresh and then close with updated records and this might not be happening when running from a job.
How can I now refresh my excel file on a schedule?