You may be able to run the macro if you first sync the SharePoint folder containing your files to File Explorer, then, in File Explorer, determine the file path and use that. When you sync, you get a local, standard file path, not a URL like you get with SharePoint.

For example, if you sync a SharePoint-based folder called Acme Project, then, in File Explorer, the path becomes something along the lines of:
Sync SharePoint files and folders

Note that ALL of the files you need to run the macro need to be in the sync'd folder

WARNING: If you sync a SharePoint folder to File Explorer and you no longer want the sync, in File Explorer, DO NOT DELETE THE FOLDER -- that deletes it on SharePoint, too. Unsync using the following instructions:

How to cancel or stop sync in OneDrive

Caveats: your org may have restrictions in place that prevent this, your version of SharePoint may work differently then my org's, etc.

If running the macro is a critical part of your workflow and you cannot work on the files locally, then tell your org and maybe they can convert the VBA code to work in another environment, e.g., Office Scripts or Power Automate.