Launch Web Query from Macro
 

Is it possible to have a macro launch the Web Query dialog with it set to a
particular URL? We want to provide our users a button to click that brings
up the web browser UI without having to add in a new web query and enter the
URL. Alternately, we could add one in, erase the cell contents, and get
them to right-click a cell and select Edit Query - even then a button would
be cleaner.

Also, once the dialog is launched we would like to (programmatically) 1)
Turn off the yellow arrows, 2) Set the size (Size of window is kept while
Excel is open, but not when it's restarted).

Any thoughts would be appreciated.

Thanks,
Krip

Launch Web Query from Macro
 

I regularly download data of all scrips from my stock exchange My code is
given below with some comment lines beginning with an apostrophe
one part of the url is a date which changes and I have used an input box
you can modify to suit you

code starts
Worksheets("sheet1").Activate
'the next line deltes the existing data in sheet1 so that new query can be
invoked
Worksheets("sheet1").UsedRange.ClearContents
'I am using a varaiable <scripdate which can be an input
Dim scripdate As String
scripdate = InputBox("type date as for e.g. 7JUL2005")
With Selection.QueryTable
.Connection =
"URL;http://www.nseindia.com/content/historical/EQUITIES/2005/JUL/cm" & _
scripdate & "bhav.csv"

.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.Refresh BackgroundQuery:=False
End With
code ends

will this help you????

if there is no variable part in the url you can for the firsttime download
by using a web query and then subseuently only refresh it for this the code
is

code begins
Worksheets("sheet1").Select
Cells.Select
Selection.Clear
Range("A1").Select
Selection.QueryTable.Refresh BackgroundQuery:=False
Range(Range("a1"), Range("a65536").End(xlUp)).Select
code ends

the downoad starts in A1 in the original query.

the last line will select all the downlaoded data for some subseuent
manouvering


--
remove $$$ from email addresss to send email



Anil Kripalani wrote in message
...
Is it possible to have a macro launch the Web Query dialog with it set to
a
particular URL? We want to provide our users a button to click that
brings
up the web browser UI without having to add in a new web query and enter
the
URL. Alternately, we could add one in, erase the cell contents, and get
them to right-click a cell and select Edit Query - even then a button
would
be cleaner.

Also, once the dialog is launched we would like to (programmatically) 1)
Turn off the yellow arrows, 2) Set the size (Size of window is kept while
Excel is open, but not when it's restarted).

Any thoughts would be appreciated.

Thanks,
Krip

Launch Web Query from Macro
 

R,

Thanks for the code snippets, but I need to launch the Web Query dialog -
the user needs to provide criteria for the query results on the first page,
then the results are shown which are imported into the spreadsheet. So, I
do not know the parameters for the URL until the user picks items from a
list on the web page.

One idea we have is to launch a web browser from within Excel (apart from
Web Query) for the user to make his selections; extract the URL, and feed
that to Web Query as a full URL for import (So that we don't need to bring
up the Web Query UI.)

-Krip