ActiveSheet.QueryTables.Add using InputBox and/or a URL list in a .txt file list

[crazy_vba]

Hello everyone !
here is a new trick for all of vba programmers out there.

So far in my macro, I got this line:
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://www.xxx.com/mypage/thispage.htm" _
, Destination:=Range("A1"))

Here are my two questions:

==> 1st case : using InputBox function, I would like to ask via a
msgbox for the URL address. And then, VBA would put this var into the
ActiveSheet.QueryTables.Add.

I've tried to do this way, but I got an error msg saying that "this is
not a valid URL adress". Why?

_______
Address = InputBox("Type in the URL Adress of the website page",
"Automatic Data Importation")

With ActiveSheet.QueryTables.Add(Connection:= _
"URL;Address" _
, Destination:=Range("A1"))
________

How can I do that ?

==> Now 2nd solution: I got a .txt file (it could also be into an excel
sheet btw) in which there are 500 URL adresses, and I would like VBA to
import the first address, execute the macro by putting this first
address in the ActiveSheet.QueryTables.Add, then use the second adress
etc etc..


Thanks for your appreciated *help*
Crazy

 

[Tom Ogilvy]

Address = InputBox("Type in the URL Adress of the website page",
"Automatic Data Importation")

With ActiveSheet.QueryTables.Add(Connection:= _
"URL;" & Address, Destination:=Range("A1"))

 

[crazy_vba]

Very helpful Tom ! Thanks ) Why didn't I think about it before !

By the way, Do you have any idea regarding the 2nd solution: I'
thinking about writing this code, knowing that the 500 URL addresse
are in the sheets "source".

With ActiveSheet.QueryTables.Add(Connection:= _
"URL;" & !Source!A1, Destination:=Range("A1"))

But then I got the issue of changing this A1 in A2 when the macro wil
have finished running with the A1 address....

Any ideas ?
Thanks for your post :-

 

[Tom Ogilvy]

With ActiveSheet.QueryTables.Add(Connection:= _
"URL;" & !Source!A1, Destination:=Cells(rows.count,1).End(xlup)(2))

It will start in A2 (or the next empty cell) and work down from there on the
next use.

 

[crazy_vba]

cool
Thanks it really helps out !

I guess I will have quite a big post tomorrow as I'm trying to code a
"Search and Find" function in my macro, so it will scan every cell of a
sheet, find a specific word and therefore locate this cell, extract from
this specific cell the information located in another cell in another
sheet of my workbook

I will try to make it more clear and to EXACTLY define what I want my
macro to do. Hope u'll be able to help me on that too ;-)

See ya and thanks again