Problem with XLOpen & XLGet with SharePoint?

[DreamTheater]

Hi there,

I've been using Macro Scheduler for the last year or so on an older version and recently purchased a new licence for the lastest version (14.5.4).

After upgrading versions I noticed that some of my macros which use SharePoint to load & modify files no longer work.

Example of what I was using successfully:

[snippet]
Let>LogFPShare=https://team.sharepoint.com/teams/.......file.xlsx
XLOpen>%LogFPShare%,1,xlLog

... Do stuff

XSC>xlLog,Log,%CurrentRow%,7,%AHT%,xlres
XLS>xlLog,%LogFPShare%
[/snippet]

After the upgrade the result is "ERR: File does not exist" for XLOpen.
I tried using XLGet which wasn't in the version I was previously using, this only returns "ERR" when trying to attach to a SharePoint URL. Local files work fine.

I saw in the version history for 14.4 (04/10/17):
- Fixed: XLOpen not checking that file exists before attempting to open - causes variant error
Could this have caused problems accessing Sharepoint files?

I can load the Excel file using Run> or VBScript, but I won't be able to use XLSetCell to modify it.
I'd rather load & save directly to Sharepoint instead of saving locally then manually uploading to Sharepoint each time the macro is run.

Do you know if there's another way to access & modify Excel files hosted on SharePoint using the inbuilt Excel functions?

Cheers!

[Dorian (MJT support)]

I'm quite surprised that your method worked in the first place. I don't think we've ever had any support requests from anyone using XLOpen that way.

Your assumptions are correct. The update now checks for the file. Being able to open remote files appears to be an undocumented side effect of Macro Scheduler previously not checking. And of course now it checks, that undocumented side effect no longer exists.

The solution would be to either download the file and re-upload it, or revert to the previous version.

[DreamTheater]

Me too, I thought I'd give it a go while testing and it worked!

I'm currently running the older version still and will look at that sort of workaround on the new version.

Thanks for the update