If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
data query problem
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 |
#3
|
|||
|
|||
original post garbled
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 |
#4
|
|||
|
|||
data query problem
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=&FormSportsCategor ies=&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 "aaron" wrote in message ... 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 . |
Thread Tools | |
Display Modes | |
|
|