ActiveSheet.QueryTables.Add using InputBox and/or a URL list in a .txt file list  
Author Message
SteveTBM®





PostPosted: Sat Apr 29 15:35:55 CDT 2006 Top

Excel Programming >> 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.hide-link.com/ ; _
, 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.hide-link.com/ ;userid=33679
View this thread: http://www.hide-link.com/

Excel367  
 
 
Tom





PostPosted: Sat Apr 29 15:35:55 CDT 2006 Top

Excel Programming >> 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



>
> 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?action=getinfo&userid=33679
> View this thread: http://www.excelforum.com/showthread.php?threadid=537500
>


 
 
crazy_vba





PostPosted: Sat Apr 29 16:00:23 CDT 2006 Top

Excel Programming >> 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?action=getinfo&userid=3367
View this thread: http://www.excelforum.com/showthread.php?threadid=53750

 
 
Tom





PostPosted: Sat Apr 29 16:20:14 CDT 2006 Top

Excel Programming >> 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




>
> 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?action=getinfo&userid=33679
> View this thread: http://www.excelforum.com/showthread.php?threadid=537500
>


 
 
crazy_vba





PostPosted: Sat Apr 29 16:54:50 CDT 2006 Top

Excel Programming >> 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?action=getinfo&userid=33679
View this thread: http://www.excelforum.com/showthread.php?threadid=537500