SOLVED
Home

Import Data from Excel Online to Excel (offline)

%3CLINGO-SUB%20id%3D%22lingo-sub-303011%22%20slang%3D%22en-US%22%3EImport%20Data%20from%20Excel%20Online%20to%20Excel%20(offline)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-303011%22%20slang%3D%22en-US%22%3E%3CP%3EScenario%3A%3CBR%20%2F%3EI%20have%20an%20office%20365%20excel%20document%20that%20is%20synced%20up%20with%20a%20Microsoft%20survey%20form.%20I%20then%20have%20an%20offline%20excel%20file%20that%20I%20would%20like%20to%20read%20those%20survey%20responses%20in.%20The%20offline%20excel%20file%20is%20required%20because%20we%20need%20to%20run%20excel%20macros.%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3EIs%20there%20a%20way%20to%20import%20the%20contents%20of%20an%20entire%20sheet%20from%20an%20Excel%20Online%20document%20into%20my%20offline%20excel%20file%3F%20I%20have%20looked%20through%20the%20Data%20%26gt%3B%20Get%20Data%20options%2C%20and%20I%20don't%20see%20anything%20that%20jumps%20out%20at%20me%20that%20could%20be%20of%20use.%20But%20I%20have%20not%20dealt%20with%20getting%20information%20from%20external%20sources%20that%20were%20not%20also%20located%20on%20the%20same%20computer.%20Please%20note%2C%20that%20the%20intent%20of%20all%20this%20is%20to%20send%20the%20excel%20file%20to%20other%20computers%2C%20but%20still%20maintain%20that%20connection%20to%20the%20Excel%20Online%20information.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-303011%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20Online%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-303072%22%20slang%3D%22en-US%22%3ERe%3A%20Import%20Data%20from%20Excel%20Online%20to%20Excel%20(offline)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-303072%22%20slang%3D%22en-US%22%3E%3CP%3EYes%2C%20exactly%2C%20finally%20that%20will%20be%26nbsp%3BExcel.Workbook(Web.Contents(%22https%3A%2F%2F...).%20But%20you%20may%20use%20From%20File-%26gt%3BFrom%20Workbook%20connector%20if%20select%20cloud%20path%20instead%20of%20local%20one%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-303069%22%20slang%3D%22en-US%22%3ERe%3A%20Import%20Data%20from%20Excel%20Online%20to%20Excel%20(offline)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-303069%22%20slang%3D%22en-US%22%3E%3CP%3EThat's%20since%20your%20source%20is%20local%20folder%20synced%20with%20OneDrive.%20That%20shall%20be%20cloud%20part%20like%3C%2FP%3E%0A%3CPRE%3Ehttps%3A%2F%2Fcontoso-my.sharepoint.com%2Fpersonal...%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-303068%22%20slang%3D%22en-US%22%3ERe%3A%20Import%20Data%20from%20Excel%20Online%20to%20Excel%20(offline)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-303068%22%20slang%3D%22en-US%22%3E%3CP%3EI%20believe%20I%20have%20solved%20my%20problem.%20I'm%20not%20sure%20if%20it%20was%20the%20proper%20way%2C%20but%20I%20was%20able%20to%20grant%20access%20to%20both%20myself%20on%20a%20different%20machine%2C%20and%20another%20user%20on%20his%20machine%20(using%20Office%202010%20w%2F%20the%20power%20query%20plugin).%20What%20I%20did%3A%3C%2FP%3E%3CP%3E1.%20Open%20Data%20%26gt%3B%20Queries%20%26amp%3B%20Connections%3C%2FP%3E%3CP%3E2.%20Right%20click%20my%20query%20and%20click%20edit.%26nbsp%3B%3C%2FP%3E%3CP%3E3.%20In%20the%20power%20query%20editor%20window%2C%20click%20Advanced%26nbsp%3BEditor%3C%2FP%3E%3CP%3E4.%20Modified%20the%20source%20parameter%20value%20to%20Excel.Workbook(Web.Contents(%22the%20sharepoint%26nbsp%3Baddress%20of%20the%20file%22).%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThat%20seemed%20to%20work%20for%20me.%20Please%20let%20me%20know%20if%20there%20is%20a%20better%20way!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20all.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-303052%22%20slang%3D%22en-US%22%3ERe%3A%20Import%20Data%20from%20Excel%20Online%20to%20Excel%20(offline)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-303052%22%20slang%3D%22en-US%22%3E%3CP%3ESergei%2C%3C%2FP%3E%3CP%3EUnderstood.%20I%20believe%20the%20process%20you%20outline%20is%20what%20I%20followed%20(stumbled%20through)%20originally.%20I%20had%20both%20files%20saved%20on%20my%20OneDrive%2C%20and%20I%20added%20the%20data%20using%20Data%20%26gt%3B%20Get%20Data%20%26gt%3B%20From%20File%20%26gt%3B%20From%20Workbook.%20Everything%20works%20fine%20on%20the%20machine%20where%20I%20imported%2Flinked%20that%20data.%20However%2C%20If%20I%20open%20that%20.xlsm%20file%20on%20another%20machine%2C%20or%20share%20it%20with%20another%20user%2C%20they%20cannot%20refresh%20the%20data%20because%20the%20source%20is%20not%20found.%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3EIs%20there%20a%20way%20to%20use%20relative%20paths%20with%20the%20data%20source%3F%20When%20I%20open%20the%20.xlsm%26nbsp%3Bfile%20on%20my%20other%20computer%2C%20with%20a%20different%20username%2C%20the%20source%20fails%20to%20load%20because%20the%20data%20source%20is%20using%20absolute%20paths%2C%20which%20points%20to%20my%20synced%20folder%20on%20OneDrive.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20there%20a%20workaround%20for%20that%3F%20I%20apologize%20if%20I%20am%20understanding%20this%20concept%20incorrectly.%26nbsp%3B%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F62970i0713F52A43909AC6%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D-1%22%20border%3D%220%22%20width%3D%22999%22%20height%3D%22524%22%20title%3D%22Annotation%202018-12-17%20152152.jpg%22%20alt%3D%22Annotation%202018-12-17%20152152.jpg%22%20%2F%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-303040%22%20slang%3D%22en-US%22%3ERe%3A%20Import%20Data%20from%20Excel%20Online%20to%20Excel%20(offline)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-303040%22%20slang%3D%22en-US%22%3E%3CP%3EFrom%20terminology%20point%20of%20view%20-%20there%20are%20no%20excel%20online%20files%20and%20excel%20native%20files.%20There%20are%20only%20excel%20files%20which%20you%20may%20access%20from%20Excel%20online%20web%20app%20(and%20in%20this%20case%20it%20shall%20be%26nbsp%3Bon%20OneDrive%20or%20Sharepoint)%20or%20from%20Excel%20desktop%20app.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EGenerated%20by%20Microsoft%20Forms%20file%20is%20on%20OneDrive%20by%20default%2C%20you%20may%20pick-up%20the%20table%20from%20that%20file%20(and%20transform%20as%20necessary)%20by%20Power%20Query%20using%20Excel%20desktop%20app%20from%20within%20the%20file%20located%20at%20any%20place.%20The%20only%20you%20need%20is%20the%20connection%20to%20you%20OneDrive.%20If%20you%20share%20that%20file%20with%20other%20people%20you%20also%20need%20to%20share%20the%20OneDrive%20file%20with%20these%20people%20to%20give%20them%20possibility%20to%20refresh%20the%20data.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-303025%22%20slang%3D%22en-US%22%3ERe%3A%20Import%20Data%20from%20Excel%20Online%20to%20Excel%20(offline)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-303025%22%20slang%3D%22en-US%22%3E%3CP%3EI%20was%20already%20aware%20of%20the%20ability%20to%20edit%20the%20excel%20using%20native%20excel%2C%20however%2C%20the%20file%20is%20not%20a%20macro-enabled%26nbsp%3Bdocument.%20So%20I'm%20wondering%20if%20there%20is%20a%20way%20to%20avoid%20re-creating%20everything%20by%20just%20extracting%20the%20data%20I%20need%20from%20the%20excel%20online.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20ideas%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-303022%22%20slang%3D%22en-US%22%3ERe%3A%20Import%20Data%20from%20Excel%20Online%20to%20Excel%20(offline)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-303022%22%20slang%3D%22en-US%22%3EWhen%20you%20open%20your%20Online%20Excel%20file%20there%20is%20an%20option%20called%20%22Edit%22%20drop%20down%3CBR%20%2F%3EEdit%20in%20excel%3CBR%20%2F%3EEdit%20in%20Online%20Excel%3CBR%20%2F%3EChoose%20to%20edit%20in%20Online%20Excel%20and%20then%20it%20will%20open%20in%20desktop%20excel%20now%20you%20will%20have%20all%20the%20options%3CBR%20%2F%3E%3CBR%20%2F%3ELet%20me%20know%3C%2FLINGO-BODY%3E
phague
New Contributor

Scenario:
I have an office 365 excel document that is synced up with a Microsoft survey form. I then have an offline excel file that I would like to read those survey responses in. The offline excel file is required because we need to run excel macros. 

Is there a way to import the contents of an entire sheet from an Excel Online document into my offline excel file? I have looked through the Data > Get Data options, and I don't see anything that jumps out at me that could be of use. But I have not dealt with getting information from external sources that were not also located on the same computer. Please note, that the intent of all this is to send the excel file to other computers, but still maintain that connection to the Excel Online information. 

 

Thanks

7 Replies
When you open your Online Excel file there is an option called "Edit" drop down
Edit in excel
Edit in Online Excel
Choose to edit in Online Excel and then it will open in desktop excel now you will have all the options

Let me know

I was already aware of the ability to edit the excel using native excel, however, the file is not a macro-enabled document. So I'm wondering if there is a way to avoid re-creating everything by just extracting the data I need from the excel online. 

 

Any ideas?

Solution

From terminology point of view - there are no excel online files and excel native files. There are only excel files which you may access from Excel online web app (and in this case it shall be on OneDrive or Sharepoint) or from Excel desktop app.

 

Generated by Microsoft Forms file is on OneDrive by default, you may pick-up the table from that file (and transform as necessary) by Power Query using Excel desktop app from within the file located at any place. The only you need is the connection to you OneDrive. If you share that file with other people you also need to share the OneDrive file with these people to give them possibility to refresh the data.

Sergei,

Understood. I believe the process you outline is what I followed (stumbled through) originally. I had both files saved on my OneDrive, and I added the data using Data > Get Data > From File > From Workbook. Everything works fine on the machine where I imported/linked that data. However, If I open that .xlsm file on another machine, or share it with another user, they cannot refresh the data because the source is not found. 

Is there a way to use relative paths with the data source? When I open the .xlsm file on my other computer, with a different username, the source fails to load because the data source is using absolute paths, which points to my synced folder on OneDrive. 

 

Is there a workaround for that? I apologize if I am understanding this concept incorrectly. Annotation 2018-12-17 152152.jpg

I believe I have solved my problem. I'm not sure if it was the proper way, but I was able to grant access to both myself on a different machine, and another user on his machine (using Office 2010 w/ the power query plugin). What I did:

1. Open Data > Queries & Connections

2. Right click my query and click edit. 

3. In the power query editor window, click Advanced Editor

4. Modified the source parameter value to Excel.Workbook(Web.Contents("the sharepoint address of the file"). 

 

That seemed to work for me. Please let me know if there is a better way!

 

Thanks all.

That's since your source is local folder synced with OneDrive. That shall be cloud part like

https://contoso-my.sharepoint.com/personal...

 

Yes, exactly, finally that will be Excel.Workbook(Web.Contents("https://...). But you may use From File->From Workbook connector if select cloud path instead of local one

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
16 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
11 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies