VBA and web data

BobBridges · #6 01-11-2014, 08:03 AM

I think I found it, Younes. I've never tried automating a QueryTable before, so I had to go look it up, but the documentation says your string has to start with "URL;". I thought maybe there was something wrong with the URL itself, you'll remember, so I made a copy of your program and plugged in a different URL that I knew was valid:

Code:

Sub Test()
  URL = "http://www.onelook.com/?w=frick&ls=a"
  With ActiveSheet.QueryTables.Add(Connection:=URL, Destination:=Cells(1, 1))
    .Name = ""
    .FieldNames = True
    .RowNumbers = False
    ' blah, blah, blah
    End With
  End Sub

...and it failed the same way yours did. But when I looked up more about that Connection thingy, I changed the program in just one minor way and it didn't object any more:

Code:

Sub Test()
  URL = "URL;http://www.onelook.com/?w=frick&ls=a"
  With ActiveSheet.QueryTables.Add(Connection:=URL, Destination:=Cells(1, 1))
    .Name = ""
    .FieldNames = True
    .RowNumbers = False
    ' blah, blah, blah
    End With
  End Sub

Mind you, the rest of your program may fail for other reasons. But at least this'll get you to the next step.

Sorry for the delay; I should have gotten to it sooner.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Match two sets of data and display specific data	lolly150	Excel	1	05-14-2012 10:33 PM
Edit Data Source- Linking template charts to new data	lbf	PowerPoint	0	10-28-2011 12:19 PM
Powerpoint: adding data to trend lines w/o data labels	HaiLe	PowerPoint	0	04-11-2011 09:21 AM