2

Is there a way to force excel from automatically "rewriting" the address of the external reference?

Example

When I mouse over and select a table in another workbook as a source, the formula bar shows this:

=[WorbookName.xlsx]SheetName!TblName

Once I hit enter, the formula bar shows this:

=WorbookName.xlsx!TblName [#Data]

Brackets and sheet names get removed and [#Data] gets added.

I confirmed that excel understands the address with the sheet name by using indirect and forcing excel to use the full reference

=INDIRECT([WorbookName.xlsx]SheetName!TblName)

So its not a limitation by excel. However this only proved that excel can handle that style of reference. The problem is that when you use indirect, youre not actually creating a "link" to the source file, your workbook will never ask to update the values if the source file is closed.

I need to force excel to keep the table name in the reference because I will be uploading to OneDrive and co-authoring the workbook. When you reference a table in another workbook, and that workbook is closed, the reference is changed to include the web address of your OneDrive like this

source file open:   =WorbookName.xlsx!TblName [#Data]
source file closed: =https://d.docs.live.net/location/FileName.xlsx'!TableName

I believe this is causing errors, preventing excel from updating the values. when I first open the workbook that contains the links, it will try to update to the latest data. When I use regular ranges, it has no issues. When I use tables, I get "we cant update some of the links in your workbook right now". If you go to edit the links, it looks like this: Edit Link enter image description here

If you try to update the values, you are shown this, Notice it is looking for a worksheet but the name its expecting is actually the name of the workbook. Select Sheet

enter image description here

I believe this is because when you reference a range, the address is written like this:

Rance referance =https ://d.docs.live.net/Loacation/[FileName.xlsx]SheetName!$A$1:$D$20
table referance =https ://d.docs.live.net/location/FileName.xlsx'!TableName

Notice there is a difference in the location of exclamation ! point. I believe this is the reason why excel shows the workbook name as the worksheet is looking for. again if you use indirect, youre able to force excel to use the "long" address, so we know excel recognizes it with no issues.

Really hoping to find a solution, thanks in advance!

OverflowStacker
  • 1,340
  • 1
  • 10
  • 17
UEExcel
  • 23
  • 3
  • Why don’t you keep using named ranges? – Ricardo Diaz Feb 21 '20 at 00:15
  • Its looking like im going to have to. I have some long nestested if functions and it helps having the table and column name while writing and reading. Plus im hoping there is a simple solution to this :-/ – UEExcel Feb 21 '20 at 13:40
  • You could use powerquery to get the data into the local workbook and reference the local table – Ricardo Diaz Feb 21 '20 at 15:48
  • I was under the impression that Power Query requiers the file to be closed in order to update. Correct me if im wrong! I did try that as a solution early on but received the "being used by another process" error unless I closed the source file While this allows me to update without requirering the source file to be open, it also requires the file to be closed. The issue is that a user may need to look at the file after editing the source file. it could be cumbersom to requiere they close the file before they could see the effect. I need it to update if the source file is open OR closed :( – UEExcel Feb 21 '20 at 17:44
  • You’re right. It needs to be closed. Maybe through Microsoft Query. Old tech, but it’s an alternative – Ricardo Diaz Feb 21 '20 at 18:18
  • Does anyone know if its posible to use the https://d.docs.live.net/ address (when the file its saved to onedrive for co-authering) in a query? I attempted to add it as a Get Data>From other soursecs>From Web. No joy trying that approach. – UEExcel Feb 21 '20 at 18:55

1 Answers1

1

This is an alternative to what you're trying to do.

I did some tests and if the file is saved in OneDrive, you could have it open (from OneDrive not locally) and just refresh the data with PowerQuery.

Steps to create the query:

In Chrome:

  1. Locate the source file in Onedrive (aka: https://onedrive.live.com/)
  2. In google Chrome, download the file (we need to get the download link in the next step)
  3. Click the elipsis in Chrome's top right corner and click on Downloads (or press Ctrl + J)
  4. Right click the link of the downloaded file, and select Copy

In Excel...

  1. Click Data | Get Data | From other sources | From web (I don't have the english version right now, but you should be able to locate it)
  2. Paste the URL
  3. Click Ok
  4. Select the Sheet/Table (do any transformation you may require)
  5. Click Close and Load to...
  6. Select a sheet or any location
  7. Press Ok

Now do any formulation against that table

As said, I did some tests with the file opened in another instance and then refreshing the table, and it worked.

Let me know if yours do.

EDIT:

As per your comments, (though I didn't test it) I did some research on how to make this work with the default sharing links.

Here are the steps:

In Chrome:

  1. Locate and select the source file in Onedrive (aka: https://onedrive.live.com/)
  2. Click on Share and generate the view/edit link
  3. Copy that link

As of this date, the link should look something like this:

https://1drv.ms/x/s!ArAKssDW3T7wnIIEvmhHrMxfvhowww?e=UsaATm

The key here is to replace the ms with ws, so the link that you need to use in Excel should be something like this:

https://1drv.ws/x/s!ArAKssDW3T7wnIIEvmhHrMxfvhowww?e=UsaATm

In Excel:

  1. Click Data | Get Data | From other sources | From web (I don't have the english version right now, but you should be able to locate it)
  2. Paste the URL
  3. Click OK
  4. Righ click the icon that says 1dr.ws
  5. Select and click Excel
  6. Continue with the other transformations
Ricardo Diaz
  • 5,658
  • 2
  • 19
  • 30
  • Thank you for this! it worked!! I was just coming to post a very similar workaround I found. your solution is easier I have a question for you. how unsecure is this? If the file is open from another PC (another excel account) will the link in the query work? or will it be blocked? You never know how things will change later and it might be helpful to someone. so here is the alternate alternate answer to this I found the answer here: https://social.technet.microsoft.com/Forums/en-US/f2c3f742-056e-47bc-863a-9247bedd0c1c/power-query-cant-get-data-from-a-shared-onedrive-excel-file? – UEExcel Feb 21 '20 at 21:31
  • "Instead of going to "From Web", go to "From Other Sources" -> "Blank Query", and in the pop up go to "View"->"Advanced Editor". Once the editor is open, replace the text that's there with the following: let sharingUrl = "", base64Value = Binary.ToText(Text.ToBinary(sharingUrl, TextEncoding.Utf8), BinaryEncoding.Base64), encodedUrl = "https://api.onedrive.com/v1.0/shares/u!" & base64Value & "/root/content", Source = Web.Contents(encodedUrl) in Source where in your case you'll want to replace – UEExcel Feb 21 '20 at 21:32
  • AFAIK it complies with the Microsoft Security Standards. The download link is only useful if you have already login to the onedrive account. But an extra check would be nice to have. Let us know if you do it. – Ricardo Diaz Feb 21 '20 at 21:38
  • Also if the answer solves your problem, remember to mark it (check at the left) so others may find it too. – Ricardo Diaz Feb 21 '20 at 21:39
  • True, you only have the link if you already logged in to one drive. if you edit the data source settings, you can edit the permissions under permission editing there are several areas to input credentials. do you know if there is another link that could work that requiers the credentials? what would happen if I shre the file with the query with someone who doesnt have access to the source file (was not shared with them)? – UEExcel Feb 21 '20 at 22:08
  • I tried with other links, but only this one worked. – Ricardo Diaz Feb 22 '20 at 09:33
  • Hey ricardo, I ran into an issue with your method and just wanted to know if may have the same issue. It seems like the download link stops working after some time. Every 10 or so minutes, the link stops working. Then I go and download the file again. The link is different. I wonder if there is a permanent link – UEExcel Feb 25 '20 at 23:40
  • Fantastic! i wasted several hours looking. Finally broke down and came back to the overflow. if you dont mind me asking, how did you find that info? it would be great to know – UEExcel Feb 26 '20 at 03:17
  • Googled the link to download files from one drive and then by just trying, found the way to make it work with the function to query contents from web...glad it worked. I hate when I waste hours looking for an answer.... – Ricardo Diaz Feb 26 '20 at 08:29