Problem with XLOpen & XLGet with SharePoint?

Technical support and scripting issues

Moderators: Dorian (MJT support), JRL

Post Reply
DreamTheater
Newbie
Posts: 19
Joined: Mon Oct 14, 2019 6:23 am

Problem with XLOpen & XLGet with SharePoint?

Post by DreamTheater » Mon Oct 14, 2019 6:46 am

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!

User avatar
Dorian (MJT support)
Automation Wizard
Posts: 1348
Joined: Sun Nov 03, 2002 3:19 am
Contact:

Re: Problem with XLOpen & XLGet with SharePoint?

Post by Dorian (MJT support) » Mon Oct 21, 2019 10:27 am

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.
Yes, we have a Custom Scripting Service. Message me or go here

DreamTheater
Newbie
Posts: 19
Joined: Mon Oct 14, 2019 6:23 am

Re: Problem with XLOpen & XLGet with SharePoint?

Post by DreamTheater » Thu Oct 24, 2019 12:48 am

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

Post Reply
Sign up to our newsletter for free automation tips, tricks & discounts