Does a source workbook in SharePoint need to be open in this case?

Hi,

Does a workbook stored in SharePoint need to be open in order to pull data from it using WITH(workbook path + name) ? I know it does not need to be open when using formulas - but what about VBA?

A little backstory: A benefit of OneDrive/SharePoint is that when working with formulas, the OD/SP reference workbook does not need to be open to pull data. That's huge to me - it makes things so much easier vs. opening every workbook in order to reference it when they're on a local/network drive. For example, in order to reference cells A1 and A2 in a workbook in D:folder/folder - that workbook needs to be open to grab the data. And if you need 5 workbooks in order to do that thing you want to do, then they all need to be open to grab the data. If they're not, you get error, error, error all over the place. What a silly pain in the butt.

I didn't expect it, but I found if the workbooks you need to reference are in SharePoint, they can remain closed - you just got to reference them in formula like: =SUM('https://abcde.sharepoint.com/folder/folder/folder/[WorkbookName.xlsx]SheetName'!$A$1:$A$2) to get the sum of A1 and A2. The ABCDE by the way is the name of your account/company/etc.

Ok, so like I said, I LOVE this feature. No longer a need to deal with REF and VALUE errors and crap.

But I'm running into trouble when I'm trying to do this with VBA macros. Referencing another workbook is done with the WITH command, and I'm getting errors: WITH(path/workbook.sheet....yadda yadda.... works great in a local/network setting. But I'm not able to get that to work with a CLOSED workbook that's on SharePoint. I'm not finding a lot on the internet, and I'm finding that sometimes there's some URL-to-path translation that needs to happen. So, I'm slowly figuring it out, but does anyone have some experience with this?

Ugh - why cant VBA by easy? That puts a dent in things. I'll need to do an intermediary step to pull data from a closed workbook in SharePoint into a macro then.

Well that's ok. I can apply formulas via my macro to pull data from closed SharePoint workbooks into a helper sheet, then pull those cell values into the macro, and continue.

Yeah - that'll work just fine with closed workbooks.

Thanks!