There are 4 places that you can use as Target Folders in XLPublish…
1. The Source File’s folder
This is the default and most common place to place your Target file for many obvious reasons, most notably that the files stay together in your own personal Workspace.
This of course becomes a problem (or a complexity anyway) if you wish multiple people to collect your Target files in a common location separate from their Source files completely.
2. A Network Shared Folder
In this case you could just enter the path to a Network Share Folder (either as a UNC or a Drive-letter based designation). It is ALWAYS better to use a UNC but if your company uses standard drive letters you should be fine using that syntax.
3. An On-Premises SharePoint Document Library
Alternatively you may set your Target Folder to be an on-Premises SharePoint Folder.
All of the above options are acceptable locations for your Target files IF YOU ARE GOING TO PUBLISH YOUR DATA DIRECTLY TO https://app.powerbi.com (the Power BI Service) DIRECTLY…
You cannot publish to https://app.powerbi.com directly from a Cloud folder – full stop! This is not a limitation of XLPublish but of Excel, but not a problem really.
4. A SharePoint or OneDrive Folder in the Cloud
If however you wish to make a SharePoint or OneDrive for Business folder in the Cloud you can do this as well. There are two issues around this option that you must know:
- The syntax for these Folders MUST be like this :
- You cannot use the kind of reference you would get from your Browser address like this:
- This reference WILL fail in Power BI (not an XLPublish issue).
- You cannot publish directly to https://app.powerbi.com (the Power BI Service) from a file located in the Cloud. This is a limitation of Excel not XLPublish. This sort of makes sense because there is no point in having your Corporate data in the Cloud in the Power BI Service and ALSO in a Cloud-base SharePoint site. The Target file should be behind the Firewall anyway.
But why would I NOT want to publish directly to the Power BI Service?
That would be if you are using Power BI Desktop and wish to source your data directly from the Target file rather than thru a Published Power BI Service Dataset. You can produce the same results either way in Power BI Desktop but there are different standards in different organizations and different Power BI Developers that may lead you using SharePoint or OneDrive as your Target Folders.
But we have found that with XLPublish there are advantages to using Power BI Service for Data storage when you use XLPublish and you will be able to accomplish everything you could do in Power BI Desktop regardless.
This is discussed further here: