Today I was setting up a refreshable Web query in Excel, something I've done numerous times without any issues, and I came across an interesting problem. The URL that I wanted to use was too long. After entering it into the New Web Query window, I received this most unwelcome error message:
"The text string you entered is too long. Reduce the number of characters used or cancel the entry."
I thought to myself, "Well that's silly! I can't change the URL of my data source, it's not something I have any control over!"
I turned to my good friend, Google, and tried to figure out if there was a way to get around this problem. Unfortunately, most of what I was finding were confusing forum posts about attempted bits and pieces of VBA code or people insisting that it's just not possible. I'm pleased to say that, a few moments later, it occured to me that there is a very simple solution... and it doesn't involve any programming.
Ever heard of TinyURL? It's a handy online tool that lets you take any URL and create a much smaller one that automatically redirects you to the full one. It's free, and the link will never expire (according to TinyURL, anyway). In any case, I tried using it for my web query in Excel, and I'm pleased to say that it worked perfectly! It actually redirected and changed the URL to the super long one, but I never got that annoying error message.
So here's how you do it. Copy your super long URL to your clipboard (Ctrl + C). Go to www.tinyurl.com, and paste your URL (Ctrl + V) into the 'Enter a long URL to make tiny:' text box. Click the 'Make TinyURL!' button. Copy the TinyURL that it provides you on the next page (it'll start with http://www.tinyurl/ and be followed by some random characters). Now, go to Excel to the sheet where you'd like to set up the Web query. Get the New Web Query window open (go to the Data tab, and click the 'From Web' button in the 'Get External Data' group), and paste in the TinyURL you just created into the address text box. Click the 'Go' button, and hey! No error message! Hooray!
"The text string you entered is too long. Reduce the number of characters used or cancel the entry."
I thought to myself, "Well that's silly! I can't change the URL of my data source, it's not something I have any control over!"
I turned to my good friend, Google, and tried to figure out if there was a way to get around this problem. Unfortunately, most of what I was finding were confusing forum posts about attempted bits and pieces of VBA code or people insisting that it's just not possible. I'm pleased to say that, a few moments later, it occured to me that there is a very simple solution... and it doesn't involve any programming.
Ever heard of TinyURL? It's a handy online tool that lets you take any URL and create a much smaller one that automatically redirects you to the full one. It's free, and the link will never expire (according to TinyURL, anyway). In any case, I tried using it for my web query in Excel, and I'm pleased to say that it worked perfectly! It actually redirected and changed the URL to the super long one, but I never got that annoying error message.
So here's how you do it. Copy your super long URL to your clipboard (Ctrl + C). Go to www.tinyurl.com, and paste your URL (Ctrl + V) into the 'Enter a long URL to make tiny:' text box. Click the 'Make TinyURL!' button. Copy the TinyURL that it provides you on the next page (it'll start with http://www.tinyurl/
Posts
thank God for Google and thank God for Stacey Du Bois!
ReplyDeleteYou're a lifesaver Stacy! Thank you!
ReplyDeleteI would have loved this to work for me, except that I needed at least 3 custom parameters (by using ["Parameter-Name"]) in the URL which regularly changed and setting those parameters needed to correspond to cells of my spreadsheet in the Connections > Properties > Definition > Parameters section (Excel 2010)... So a simple tinyURL wouldn't work for me. Here is what I found that works -- even with parameters. Enter as much of the query as you want or can in the Web query form (not so much that it gives you that error), then click the "Save Query" disk icon and save the query to a file. Open the query file from where you saved it up with an editor, such as Notepad and you'll see something like this (Excel 2010):
ReplyDelete-----------
WEB
1
http://example.com/mod_perl/user/blah?param1=["PARAM1"]&PARAM2=["PARAM2"]&PARAM3=["PARAM3"]&blah=blah
Selection=3
Formatting=None
PreFormattedTextToColumns=True
ConsecutiveDelimitersAsOne=True
SingleBlockTextImport=False
DisableDateRecognition=False
DisableRedirections=False
-----------
Now, just add the extra length you need to the URL string above, save, and make sure you are using the connection query file in your Excel spreadsheet, and you should be good. My query is almost 400 characters, and based on what I've read you should be able to go up to about 1000 characters with this.
Johnny
I never considered that scenario - thanks for posting this!
DeleteThanks so much for the tips. The first one didn't work for me but the 2nd tip did.
ReplyDeleteAppreciate your OP Stacy and the tip on the workaround that worked for me.
Glad to hear I could help you a bit!
DeleteAnother way to attempt this is to save the webpage on to your desktop and then point the URL to the local directory of the saved file. For instance, I wanted to import an XML output from a firewall API. I went to the webpage and saved the XML output to a file on my desktop and then had excel import the XML from my desktop. That worked as well. I didn't want to use tinyURL as my URL had the username and password of my firewall.
ReplyDeleteExcellent, thanks for sharing your solution!
DeleteThank you for sharing your knowledge, Stacy!
ReplyDeleteThanks Stacy and Milagrofrost for the solutions. Both have worked for me under different data sets
ReplyDeleteThe Holy F_ing Grail.. Thanks to the 04/12 annon post. My life is about to get MUCH better. Many thanks to whomever that was
ReplyDeletemega dittos! Thank you annon 4/12
ReplyDeleteI have tiny url's that I've pasted in excel and they worked but the next day some refused to open. Sup?
ReplyDelete