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

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


--
crazy_vba
------------------------------------------------------------------------
crazy_vba's Profile: http://www.excelforum.com/member.php...o&userid=33679
View this thread: http://www.excelforum.com/showthread...hreadid=537500

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

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

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


--
Regards,
Tom Ogilvy

"crazy_vba" wrote
in message ...

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


--
crazy_vba
------------------------------------------------------------------------
crazy_vba's Profile:
http://www.excelforum.com/member.php...o&userid=33679
View this thread: http://www.excelforum.com/showthread...hreadid=537500

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

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

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 :-

--
crazy_vb
-----------------------------------------------------------------------
crazy_vba's Profile: http://www.excelforum.com/member.php...fo&userid=3367
View this thread: http://www.excelforum.com/showthread.php?threadid=53750

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

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.

--
Regards,
Tom Ogilvy


"crazy_vba" wrote in
message ...

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

By the way, Do you have any idea regarding the 2nd solution: I'm
thinking about writing this code, knowing that the 500 URL addresses
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 will
have finished running with the A1 address....

Any ideas ?
Thanks for your post :-)


--
crazy_vba
------------------------------------------------------------------------
crazy_vba's Profile:
http://www.excelforum.com/member.php...o&userid=33679
View this thread: http://www.excelforum.com/showthread...hreadid=537500

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

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


--
crazy_vba
------------------------------------------------------------------------
crazy_vba's Profile: http://www.excelforum.com/member.php...o&userid=33679
View this thread: http://www.excelforum.com/showthread...hreadid=537500