Log In
Register
Home
Excel Expert
Power Query
April 16, 2016
Offline
Hi all, am in a bit challenging situation, we have a file that extracts information from multiple spreadsheets. All these files are moving into sharepoints now, I am looking at relinking them all from local G drive into a sharepoint structure. What's best way to go about this?
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
November 8, 2013
Offline
Hi Yifan,
From sharepoint, you should be able to Sync that folder or documents library with OneDrive, there should be a Sync option in sharepoint menu.
A new onedrive folder will be created on your computer and that will be automatically syncronized with sharepoint, so you can point again to local folder, let OneDrive update your files from that folder.
Excel Expert
Power Query
April 16, 2016
Offline
Thank you Catalin, yes I managed to find that Sync button and it does exactly what you suggested. However I need another option in this instance to refresh files that is on Sharepoint already. E.g. File A (on sharepoint) looking at File B, C,D (all on sharepoint). Is this the best practice? I don't know aye.
Excel Expert
Power Query
April 16, 2016
Offline
Another consideration is that everyone in the team needs to refresh this File, so if I create just in onedrive, would other people able to refresh the file the same way I am able to?!
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
November 8, 2013
Offline
You mean that the file that extracts data from sharepoint folder is also located in sharepoint, and multiple users will work on it in the same time?
That sounds like a conflict, if more than one user will open and refresh the same file in the same time.
What version of excel are you using? Is it a standalone version or a Microsoft 365 account?
With Microsoft 365, there is an option to get data from a sharepoint folder.
Excel Expert
Power Query
April 16, 2016
Offline
Hi Catalin,
Thank you for your reply, my reply below:
You mean that the file that extracts data from sharepoint folder is also located in sharepoint, and multiple users will work on it in the same time?
That is correct
That sounds like a conflict, if more than one user will open and refresh the same file in the same time.
No, only one refresh happen at certain point in time, but multiple user needs to make comments to the same file once refresh is complete.
What version of excel are you using? Is it a standalone version or a Microsoft 365 account?
At moment, we use Excel 2010, and it is not an option like you attached. (Please see my attachment). I managed to use Extract from Sharepoint list, but it is not quite getting the information that I want. I could not find the reports that I saved in the share point folder for some reason.
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
November 8, 2013
Offline
and multiple users will work on it in the same time?
That is correct
That sounds like a conflict, if more than one user will open and refresh the same file in the same time.
No, only one refresh happen at certain point in time,
I'll take the last response, as it contradicts the first one.
Try this query:
let
Source = SharePoint.Files("https://{yourdomainname}.sharepoint.com/sites/{sitename}",[ApiVersion=15])
in
Source
you will be asked for credentials, you should select Microsoft account and perform the sign in.
Excel Expert
Power Query
April 16, 2016
Offline
Thank you Catalin. Much appreciated.
I had use the above query code, it worked, however identify the files that I want to select is not on the list, even though I can see it and I uploaded the files. Spoke to someone with cloud experience, they were saying it seems there is a potential delay of of updating the directory, and we logged a support call with MS to look into this.
Sorry I don't have a lot of experience in using the interaction between SharePoint and Excel files. ..it is just a whole new world to me. Your earlier suggestion to use OneDrive is not an option after consulting with our IT department Unfornately otherwise things will be a lot easier.
1 Guest(s)
