5

I'm looking to pull into the XML feed from Feedburner's API. This is just a matter of writing the URL and using the "From Web" data connection in Excel.

https://feedburner.google.com/api/awareness/1.0/GetItemData?uri=RSSFEEDNAME&dates=2011-08-01,2011-08-05

This works fine (and is pretty fast).

Now, I'd like to be able to update two cells in the "dates" sheet to have it pull that range of data. This is done using parameters in the URL:

https://feedburner.google.com/api/awareness/1.0/GetItemData?uri=RSSFEEDNAME[]

Using the Excel UI, I can then assign the [] to any cell. However, no matter what I try, this doesn't work. I initially thought there might be some issue with the date format so I've worked myself to the point where I am entering into the cell, the exact copy (&dates=2011-08-01,2011-08-05) as text.

Each time, the feed pulls up with just the current days data (which is the default behavior when no dates are specified). It isn't giving an error (which it will do for relatively small infractions, like not having two-digit months) which makes me think it just simply isn't replacing the [] with the specified text. I'm also using this same method for a WebTrends Web Service query and gettign similarly frustrating results. I've read every how-to on web queries, and I'm following them exactly.

I can't find any place to see what the final URL Excel is requesting, so it's a bit of a shot in the dark. Any thoughts on next steps would be greatly appreciated!

Best, Nathan

nathanziarek
  • 619
  • 1
  • 6
  • 16

2 Answers2

9

The answer was no to use the Web Query "wizard" and just do it by hand.

  1. Open Notepad (or some text editor)

  2. In the editor type the following four lines:

    WEB
1
http://example.com/index.html?something=[]&somethingelse=[]
[BLANK]
  3. Save it as anything with an .iqy extension.
  4. Open Excel, go to the Data ribbon, and click "Existing Connections"
  5. Click "Browse for More..."
  6. Find the IQY file you made and click "Open"

Excel will then ask you where you want to put the resulting data, followed by prompts for each placeholder you entered in the URL. Those prompts let you either type in a value, or select a cell to act as the data.

nathanziarek
  • 619
  • 1
  • 6
  • 16
  • great solution, any suggestions for dealing with date formatting? currently getting the excel int for my date perams – workabyte Nov 17 '15 at 21:11
1

I would have thought that dates should have been a named parameter and that you should link that to whichever cell has the date value(s).

The cell should just have 2011-08-01,2011-08-05 as its value as long as you create the named parameter dates and link it to that cell

barrowc
  • 10,444
  • 1
  • 40
  • 53
  • You know, I tried that too. The issue there is that the web service chokes when there is a dates variable, but no data. So setting it up with a placeholder never gave me good XML, and Excel would refuse to change it's mind once good XML data came through. – nathanziarek Aug 12 '11 at 13:26