How to open an Excel file stored in SharePoint

[mnswaminathan]

Friends,   I have an excel file stored in SharePoint, I need to read the excel file and get the data into collections.   How can I do that?   Thanks Swaminathan

[StephenColeman]

Hi, You should just be able to use the path to the file on sharepoint the same as you do for a file stored on a local or network drive.  Try opening the file in excel, going to info and then right click on Open File Location and then copy path to get the path you need.  It may start with file:/// and then \\domain.  If it does, try deleting the file:/// from the path you enter in BP. Hope that helps. Thanks, Steve

[Mrugesh_NitinPa]

Did this work?

[MichalSzumski]

Hi Swaminathan,   1. One of the option was already provided by Steve (SJC44) and it seems as the simplest one to use. 2. Second one is to navigate with file URL to the file with IE,open it from there and then attach to it. It worked well for me for many months. Unfortunately it can be unstable due to Microsoft updates (so You would want to make action as universal as possible). There was a time that all my UIA elements (and whole UIA spy mode) just stopped working after update and I had to do everything with HTML (which was better anyway). 3. Another option is to use object ""Utility - File Management"" -> ""Download File"", download it on computer and then treat as normal excel. I had to modify code stage and add below line before DownloadFile: wc.UseDefaultCredentials = True Unfortunately it worked for me only with specific sharepoint sites. On Sharepoint online it failed due to some kind of authorization issue (I have tried many different solutions but everything failed - it may be caused by our shp online settings). Nevertheless it worked perfectly on sharepoint set on server. You can try it to see if this works (In my opinion it is the best out of these 4 solutions - it avoids using excel on shp which can cause problems in some situations). 4. Last option is to use OLEDB object to read excel file to collection. Problem with it is that default windows settings causes that it detects data type basing on first 8 rows (for me at least). Due to that there is a risk of data loss. It can be fixed from registry level but it requires admin rights.   Maybe there are other solutions but I tried these 4 to overcome my problems :)   Best regards, Michal Szumski

[AmiBarrett]

One thing to look into, is to make sure the WebClient service is installed and running. While this was on by default on our work-issued laptops, it was not enabled on the VMs we deployed Blue Prism on. I had to go through and install some missing Windows features to get it enabled. Fair warning though, the connection will need to be refreshed on reboot. For sake of persistence, you'll probably want to make a network drive reference to the Sharepoint location. Then on logon, kick off a batch file containing ""start z:\ or whatever your network drive letter is.