data query problem

  • Thread starter Thread starter aaron
  • Start date Start date
A

aaron

Thanks in advance for your help.

I am querying a website for some tabular data. This
website contains several pages of tabular data in the
following format:There should be some way to import my
data source once, and then cut and paste it, somehow
referencing a list of keywords (1-100) for each paste. I
have a feeling that those of you who do this kind of
thing regularly may have a simple answer for me. Any
advice?

Thanks,
Aaron

BaseURL?Keyword=K

where K is a number ranging from 1 to 100.

I would like an efficient method to quickly import all of
the data from all of the pages (K=1-100) into an excel
worksheet.

Can anyone give me some guidance on how to do so? The
only ways I can figure out how to do this require a lot
of work:

* inserting "new web query" manually over and over,
changing the full URL every time

* writing 100 different data sources (.iqy files) with
different full URLs

* writing 1 data source with the form
BaseURL?Keyword=["keyword", "Enter number:"]
This requires me to insert, over ang over again, the data
source, entering a new keyword each time
 
You didn't post your url but something like this should give you an idea.
For i = 1 To 100 'test with 2
datasheet.Range("a3:a200").EntireRow.Delete
datasheet.Range("a1:gb1").EntireColumn.Delete
myurl =
"http://www.tvguide.com/listings/Search/SearchResults.asp?I=63892&Zip=&FormT
ext=auto+racing&FormCategories=&FormSportsCategories=&Page=" & i
With datasheet.QueryTables.Add(Connection:="URL;" & myurl & "",
Destination:=datasheet.Range("A5"))
.BackgroundQuery = True
.WebFormatting = xlWebFormattingNone
'.TablesOnlyFromHTML = False
.Refresh BackgroundQuery:=False
.SaveData = True
End With
'put your copy to other sheet code here
next
 
I somehow garbled the original post. Here is how I
intended it to read. Thanks in advance for your help.

I am querying a website for some tabular data. This
website contains several pages of tabular data in the
following format:

BaseURL?Keyword=K

where K is a number ranging from 1 to 100.

I would like an efficient method to quickly import all of
the data from all of the pages (K=1-100) into an excel
worksheet. Can anyone give me some guidance on how to do
so? The only ways I can figure out how to do this require
a lot of work:

* inserting "new web query" manually over and over,
changing the full URL every time

* writing 100 different data sources (.iqy files) with
different full URLs

* writing 1 data source with the form
BaseURL?Keyword=["keyword", "Enter number:"]
This requires me to insert, over ang over again, the data
source, entering a new keyword each time

There should be some way to import my data source once,
and then cut and paste it, somehow referencing a list of
keywords (1-100) for each paste. I have a feeling that
those of you who do this kind of thing regularly may have
a simple answer for me. Any advice?

Thanks,
Aaron
 
Don-

Thanks for your help. Somehow my original message got
garbled (see my corrected post if it's confusing).

If I understand your answer, this will require writing a
macro? I haven't written macros before, and thought that
I could solve this problem through using functions and
cut-and-pasting.

For example, I thought I could write a list of 1-100 (A2
= A1+1, etc), to use as keywords for the data queries.
But I can't figure out how to make the queries use
relative cell references -- they seem to use absolute
references no matter what I do ($ signs or not). Thus,
I'd have to manually open each query and tell it a new
keyword (1-100).

Any thoughts on how to do it without macros?

Thanks,
Aaron

-----Original Message-----
You didn't post your url but something like this should give you an idea.
For i = 1 To 100 'test with 2
datasheet.Range("a3:a200").EntireRow.Delete
datasheet.Range("a1:gb1").EntireColumn.Delete
myurl =
"http://www.tvguide.com/listings/Search/SearchResults.asp ?I=63892&Zip=&FormT
ext=auto+racing&FormCategories=&FormSportsCategories=&Pag e=" & i
With datasheet.QueryTables.Add(Connection:="URL;" & myurl & "",
Destination:=datasheet.Range("A5"))
.BackgroundQuery = True
.WebFormatting = xlWebFormattingNone
'.TablesOnlyFromHTML = False
.Refresh BackgroundQuery:=False
.SaveData = True
End With
'put your copy to other sheet code here
next
--
Don Guillett
SalesAid Software
(e-mail address removed)
Thanks in advance for your help.

I am querying a website for some tabular data. This
website contains several pages of tabular data in the
following format:There should be some way to import my
data source once, and then cut and paste it, somehow
referencing a list of keywords (1-100) for each paste. I
have a feeling that those of you who do this kind of
thing regularly may have a simple answer for me. Any
advice?

Thanks,
Aaron

BaseURL?Keyword=K

where K is a number ranging from 1 to 100.

I would like an efficient method to quickly import all of
the data from all of the pages (K=1-100) into an excel
worksheet.

Can anyone give me some guidance on how to do so? The
only ways I can figure out how to do this require a lot
of work:

* inserting "new web query" manually over and over,
changing the full URL every time

* writing 100 different data sources (.iqy files) with
different full URLs

* writing 1 data source with the form
BaseURL?Keyword=["keyword", "Enter number:"]
This requires me to insert, over ang over again, the data
source, entering a new keyword each time
Click to expand...


.
Click to expand...
 
Back
Top