automating macros for an excel file in sftp location

You can’t. The SFTP server is a file store, not an application server.

So, download the file in question to a local folder, run your macro using this local file, and – when done – upload it to the SFTP server overwriting the old file.