Forum Discussion
How To Link a CSV file to an Excel Workbook on OneDrive
Hi,
I'm trying to create an excel workbook that sits on Onedrive (via my web broswer and not the desktop app), that imports it's data via a linked csv file, that also sits on Onedrive.
My Goal is to run some code that will upload 5 CSV files labelled 1.csv,2.csv,3.csv,4.csv,5.csv and have each importing their data into a 1 excel workbook via their respective sheet (i.e. sheet 1, sheet 2 etc). By keeping the file names the same the links to the excel file should remain intact (based on testing I did with a powerpoint on onedrive, hooked to an excel file on onedrive).
I'm hoping to have a way to do this without having to open each of the individual CSV files first in order to update the excel workbook (as this is the case if I link a chart from excel to a ppt, unless the excel sits on my PC).
I'm aware that in order to do any linking I need to open my OneDrive files using the Desktop app and not the Online webversion, however I haven't been able to get the sheet to link correctly using 'Data->Get Data->From Other Source->From Web->Basic URL'.
During testing I attempted 2 approaches when providing the URL:
- With my CSV file on OneDrive 'Right Click File->Share->Copy->Copy Link->Copy'.
- When opening the CSV file I copied the URL present in my webbrowser. (This approach didn't seem ideal as it coverted the file to an xlsx file, meaning it would mean any future files I upload would have to be converted to xlsx before hand).
In both instances I was shown I followed the below screenshots but didn't get the output I was expecting (shown in screenshot 2).
Can someone confirm where I am going wrong in my approach and if what I am attempting to do is even possible, or if I have to take a different approach in order to have a workbook on onedrive have automatically updating data?
3 Replies
Excel for web: Include data from linked workbooks
The browser version of Excel has been expanded to include another functionality that was previously only available in the desktop versions for Windows and Mac. In the future, it will be possible to include data from linked workbooks and have it updated automatically.
The new function is already finished and is now being gradually distributed to all users. Logically, the linked workbooks must be stored in OneDrive or SharePoint so that they can be accessed in the browser.
If you have opened two workbooks and copied data from one to the other, these data are automatically linked to one another. If they change later in the source, they are also updated at the destination - just as we have known from the desktop version of Excel for years.
You can of course also create the link "on foot" by entering the reference to the data to be integrated in the familiar Excel format:
= ‘https: //domain/folder/[filename.xlsx] sheet‘! Cell_or_area
Of course, you need appropriate access rights to be able to integrate the data, but that's not new either. The links created in this way can then be managed via the menu bar; here, for example, you can set whether the update should take place automatically or manually. References that are no longer required can also be removed at this point.
I hope I could help you with this information.
Nikolino
I know I don't know anything (Socrates)
* Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here.
Thanks for the suggestion. I can confirm that the approach works to an extent however I have to open each file and copy the range over, which in turn converts my csv to an excel. I'm guessing that the only way I'm going to be able to hook things up is with an excel file then?
***editted after running more tests and confirming you can adjust the book name in the linked url formula to relate to pull data from another file.***
- ups now i see, it is for a csv file ... i send for 2 workbooks.
