Import External Data: Dynamic URL

[djhosu]

I have a list of URLs in a workbook:

A1: www.site.com/table1
A2: www.site.com/table2
A3: www.site.com/table3
(and so on)

Given an entry in another cell, say B1, the appropriate URL from
column A appears in C1.

I am trying to figure out how to create a dynamic data import that
will pull the selected data from the web given the URL in cell C1.
The site I am pulling from contains very consistent data, so the table
selections all of the imports are the same, but the URLs will change.

I have hunted around for awhile in this group, but I can't find
exactly what I am looking for. Please help!

 

[Dave]

I have a list of URLs in a workbook:

A1:www.site.com/table1
A2:www.site.com/table2
A3:www.site.com/table3
(and so on)

Given an entry in another cell, say B1, the appropriate URL from
column A appears in C1.

I am trying to figure out how to create a dynamic data import that
will pull the selected data from the web given the URL in cell C1.
The site I am pulling from contains very consistent data, so the table
selections all of the imports are the same, but the URLs will change.

I have hunted around for awhile in this group, but I can't find
exactly what I am looking for. Please help!

Never mind...I found the answer here: http://www.vertex42.com/News/excel-web-query.html

 

[Cringing Dragon]

Hello,
firstly, the subject line of your post gets truncated, so I can't see
the entire error. Could you please put it in the message part of a post?

Also, can we please see the parts of code that most recently defined the
named ranges RRcopy_from and RRCopy_to? Or, if these are not defined in the
code but named ranges already existing in the sheet(s) then can you tell us
what the ranges refer to? By "most recently" I mean the last time before the
code where it fails.

If RRcopy_from is not defined, the copy will fail (but shouldn't hang Excel).

A possibility is that the insert row has caused Excel to hang, and the VBA
fails at the copy command because Excel is not responding. Guessing... maybe
the insert tries to push something off the bottom of the sheet into that
mysterious land that lies beyond row 65536. Interactively you'd get the
dialog box pop up warning you that something is being pushed off the edge, so
maybe the VBA isn't properly dealing with that. You don't happen to get a
dialog box appear somewhere, do you? Maybe hiding behind something else.
Maybe it's not truly hung, but is waiting for a response.

More along those lines... If RRcopy_to is a range defined by the number of
rows in RRcopy_from ... then if there's not enough rows left on the sheet to
define RRcopy_to ... what happens? Interactively it won't let you define a
range that goes beyond row 65536 - what happens when VBA code tries to define
a range like that?

Regardless of the cause, that Excel hangs instead of handing back control
might be reportable as a bug.

 

[Cringing Dragon]

Sorry about that. That was a reply to a different post completely. I don't
know how I managed to stuff it like that.