Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Web Query? Is This Possible?
I am wondering how easy, or difficult, it will be to do a web query on this
site: http://www.dsireusa.org/ Im not sure if there is a pattern or not; doesnt really seem to be one and that may prevent this project from getting off the ground. Anyway, I wanted to try to get Excel to €˜drill down to each state, such as CA, where you would see this: http://www.dsireusa.org/incentives/i...srp=1&state=CA Then drill down to each link, between €˜Financial Incentives and €˜Related Programs & Initiatives (but not below this). In each of those links I want to Import .WebTables = "6,7,8", offset 2 or 3 rows and then back out of the page, drill down to the next link, and so on and so forth. This may be a massive undertaking, and simply not worth the effort, or it may not be bad at all. I cant tell, but if someone could assess, and even give me an idea of how to get started, Id be very appreciative. Thank in advance! Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Web Query? Is This Possible?
there is nothing to download on the main page. Is there another page you
want to download. what method do you want to used. Just a Data -Import External Import Data - New Web Query or do you want to open a n internet explorer and download the daa using the internet explorted. have you tried the Qery. Have you tried the query while recording a macro? I need a little bit more info to be able to answer your question. if the worksheet menu option works then it is simple. If you don't get the results you are looking for then it is harder. "ryguy7272" wrote: I am wondering how easy, or difficult, it will be to do a web query on this site: http://www.dsireusa.org/ Im not sure if there is a pattern or not; doesnt really seem to be one and that may prevent this project from getting off the ground. Anyway, I wanted to try to get Excel to €˜drill down to each state, such as CA, where you would see this: http://www.dsireusa.org/incentives/i...srp=1&state=CA Then drill down to each link, between €˜Financial Incentives and €˜Related Programs & Initiatives (but not below this). In each of those links I want to Import .WebTables = "6,7,8", offset 2 or 3 rows and then back out of the page, drill down to the next link, and so on and so forth. This may be a massive undertaking, and simply not worth the effort, or it may not be bad at all. I cant tell, but if someone could assess, and even give me an idea of how to get started, Id be very appreciative. Thank in advance! Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Web Query? Is This Possible?
Thanks for taking an interest in this Joel. I've seen your work with web
queries; quite remarkable. If anyone can do this, it's probably you. Ok, this is the main page for the site: http://www.dsireusa.org/ If you click on 'CA', for instance, you end up he http://www.dsireusa.org/incentives/i...srp=1&state=CA What I want to do on this page, is get Excel to drill down to each link, between 'Financial Incentives' and 'Related Programs & Initiatives' (not including the information below this). Clicking on the first hyperlink takes you he http://www.dsireusa.org/incentives/i...164F&re=1&ee=1 I want to import the date from .WebTables = "6,7,8", offset 2 or 3 rows and then back out of the page, drill down to the next link, and so on and so forth. I've done lots of web queries and recorded macros many times to automate the process of importing this data from the web, but the loop is the tricky thing here. I don't know how to handle the looping part. Does it make sense so far? First, I'd like to import the date from he http://www.dsireusa.org/incentives/i...164F&re=1&ee=1 The link is named: 'Costa Mesa - Fee Waiver for Green Building' After importing data from .WebTables = "6,7,8" and offsetting offset 2 or 3 rows, I'd like Excel to go back one web page, to get back he http://www.dsireusa.org/incentives/i...srp=1&state=CA Then, move to the next hyperlink, which is this: http://www.dsireusa.org/incentives/i...A08F&re=1&ee=1 The link is named: 'Marin County - Green Building Incentive Program' Then, I'd like excel to import the date from .WebTables = "6,7,8", offset 2 or 3 rows and then back out of the page, drill down to the next link, and so on and so forth. Does it make sense? I'm pretty sure there is a way to do this, but it may be more complicated than it is worth. What do you think? You are the expert in these matters!! Thanks, Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Joel" wrote: there is nothing to download on the main page. Is there another page you want to download. what method do you want to used. Just a Data -Import External Import Data - New Web Query or do you want to open a n internet explorer and download the daa using the internet explorted. have you tried the Qery. Have you tried the query while recording a macro? I need a little bit more info to be able to answer your question. if the worksheet menu option works then it is simple. If you don't get the results you are looking for then it is harder. "ryguy7272" wrote: I am wondering how easy, or difficult, it will be to do a web query on this site: http://www.dsireusa.org/ Im not sure if there is a pattern or not; doesnt really seem to be one and that may prevent this project from getting off the ground. Anyway, I wanted to try to get Excel to €˜drill down to each state, such as CA, where you would see this: http://www.dsireusa.org/incentives/i...srp=1&state=CA Then drill down to each link, between €˜Financial Incentives and €˜Related Programs & Initiatives (but not below this). In each of those links I want to Import .WebTables = "6,7,8", offset 2 or 3 rows and then back out of the page, drill down to the next link, and so on and so forth. This may be a massive undertaking, and simply not worth the effort, or it may not be bad at all. I cant tell, but if someone could assess, and even give me an idea of how to get started, Id be very appreciative. Thank in advance! Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Web Query? Is This Possible?
I'm thinking that you may need to open two internet explorer applications.
the links will probably href properties. I probably won't get to look at this until late tonight or tomorrow morning. to get to each state webpage I would simply create a table of each atates abbreviation and use it as part of the URL California webpage is this http://www.dsireusa.org/incentives/i...srp=1&state=CA New York is this http://www.dsireusa.org/incentives/i...srp=1&state=NY So you can put all the state abbreviation on a spreadsheet Then your loop would be BaseName = "http://www.dsireusa.org/incentives/index.cfm?" & "_re=1&ee=1&spv=0&st=0&srp=1&state=" Set IE = CreateObject("InternetExplorer.Application") IE.Visible = True for RowCount = 1 to 50 URL = BaseName & Range("A" & RowCount) IE.Navigate2 URL Do While IE.busy = True And _ IE.readystate < 4 DoEvents Loop Set document = IE.document 'add your code here next rowCount "ryguy7272" wrote: Thanks for taking an interest in this Joel. I've seen your work with web queries; quite remarkable. If anyone can do this, it's probably you. Ok, this is the main page for the site: http://www.dsireusa.org/ If you click on 'CA', for instance, you end up he http://www.dsireusa.org/incentives/i...srp=1&state=CA What I want to do on this page, is get Excel to drill down to each link, between 'Financial Incentives' and 'Related Programs & Initiatives' (not including the information below this). Clicking on the first hyperlink takes you he http://www.dsireusa.org/incentives/i...164F&re=1&ee=1 I want to import the date from .WebTables = "6,7,8", offset 2 or 3 rows and then back out of the page, drill down to the next link, and so on and so forth. I've done lots of web queries and recorded macros many times to automate the process of importing this data from the web, but the loop is the tricky thing here. I don't know how to handle the looping part. Does it make sense so far? First, I'd like to import the date from he http://www.dsireusa.org/incentives/i...164F&re=1&ee=1 The link is named: 'Costa Mesa - Fee Waiver for Green Building' After importing data from .WebTables = "6,7,8" and offsetting offset 2 or 3 rows, I'd like Excel to go back one web page, to get back he http://www.dsireusa.org/incentives/i...srp=1&state=CA Then, move to the next hyperlink, which is this: http://www.dsireusa.org/incentives/i...A08F&re=1&ee=1 The link is named: 'Marin County - Green Building Incentive Program' Then, I'd like excel to import the date from .WebTables = "6,7,8", offset 2 or 3 rows and then back out of the page, drill down to the next link, and so on and so forth. Does it make sense? I'm pretty sure there is a way to do this, but it may be more complicated than it is worth. What do you think? You are the expert in these matters!! Thanks, Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Joel" wrote: there is nothing to download on the main page. Is there another page you want to download. what method do you want to used. Just a Data -Import External Import Data - New Web Query or do you want to open a n internet explorer and download the daa using the internet explorted. have you tried the Qery. Have you tried the query while recording a macro? I need a little bit more info to be able to answer your question. if the worksheet menu option works then it is simple. If you don't get the results you are looking for then it is harder. "ryguy7272" wrote: I am wondering how easy, or difficult, it will be to do a web query on this site: http://www.dsireusa.org/ Im not sure if there is a pattern or not; doesnt really seem to be one and that may prevent this project from getting off the ground. Anyway, I wanted to try to get Excel to €˜drill down to each state, such as CA, where you would see this: http://www.dsireusa.org/incentives/i...srp=1&state=CA Then drill down to each link, between €˜Financial Incentives and €˜Related Programs & Initiatives (but not below this). In each of those links I want to Import .WebTables = "6,7,8", offset 2 or 3 rows and then back out of the page, drill down to the next link, and so on and so forth. This may be a massive undertaking, and simply not worth the effort, or it may not be bad at all. I cant tell, but if someone could assess, and even give me an idea of how to get started, Id be very appreciative. Thank in advance! Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Web Query? Is This Possible?
This is what I have so far. I'm only doing California and NY as a test.
California is working. New York I 'm having problems with one of the links. It is not working like the others and giving me an error. Will Look at it tomorrow. You need to create a worksheet called USA. Sub USA() 'define states in searching webpage Const FindCategories = 0 Const ExtractCategory = 1 USAStates = Array("CA", "NY") BaseName = "http://www.dsireusa.org/incentives/index.cfm?" & _ "re=1&ee=1&spv=0&st=0&srp=1&state=" Set IE = CreateObject("InternetExplorer.Application") IE.Visible = True With Sheets("USA") .Cells.ClearContents RowCount = 1 For Each USAState In USAStates URL = BaseName & USAState IE.Navigate2 URL Do While IE.busy = True And _ IE.readystate < 4 DoEvents Loop .Range("A" & RowCount) = USAState RowCount = RowCount + 1 Set document = IE.document 'Call Dump(document) 'code for extracting table State = FindCategories For Each itm In IE.document.all Select Case itm.classname Case "categorytype" Select Case Trim(itm.innertext) Case "Financial Incentives", _ "Rules, Regulations & Policies" .Range("B" & RowCount) = itm.innertext RowCount = RowCount + 1 State = ExtractCategory Case Else State = FindCategories End Select End Select If State = ExtractCategory Then Select Case itm.classname Case "copybold" .Range("C" & RowCount) = itm.innertext RowCount = RowCount + 1 Case "copy" .Range("D" & RowCount) = itm.innertext .Range("E" & RowCount) = _ itm.FirstChild.href RowCount = RowCount + 1 End Select End If Next itm .Columns.AutoFit Next USAState End With IE.Quit Set IE = Nothing End Sub Sub Dump(document) With Sheets("dump") .Cells.ClearContents RowCount = 1 For Each itm In document.all .Range("A" & RowCount) = itm.tagname .Range("B" & RowCount) = itm.classname .Range("C" & RowCount) = itm.ID .Range("D" & RowCount) = Left(itm.innertext, 1024) RowCount = RowCount + 1 Next itm End With End Sub "Joel" wrote: I'm thinking that you may need to open two internet explorer applications. the links will probably href properties. I probably won't get to look at this until late tonight or tomorrow morning. to get to each state webpage I would simply create a table of each atates abbreviation and use it as part of the URL California webpage is this http://www.dsireusa.org/incentives/i...srp=1&state=CA New York is this http://www.dsireusa.org/incentives/i...srp=1&state=NY So you can put all the state abbreviation on a spreadsheet Then your loop would be BaseName = "http://www.dsireusa.org/incentives/index.cfm?" & "_re=1&ee=1&spv=0&st=0&srp=1&state=" Set IE = CreateObject("InternetExplorer.Application") IE.Visible = True for RowCount = 1 to 50 URL = BaseName & Range("A" & RowCount) IE.Navigate2 URL Do While IE.busy = True And _ IE.readystate < 4 DoEvents Loop Set document = IE.document 'add your code here next rowCount "ryguy7272" wrote: Thanks for taking an interest in this Joel. I've seen your work with web queries; quite remarkable. If anyone can do this, it's probably you. Ok, this is the main page for the site: http://www.dsireusa.org/ If you click on 'CA', for instance, you end up he http://www.dsireusa.org/incentives/i...srp=1&state=CA What I want to do on this page, is get Excel to drill down to each link, between 'Financial Incentives' and 'Related Programs & Initiatives' (not including the information below this). Clicking on the first hyperlink takes you he http://www.dsireusa.org/incentives/i...164F&re=1&ee=1 I want to import the date from .WebTables = "6,7,8", offset 2 or 3 rows and then back out of the page, drill down to the next link, and so on and so forth. I've done lots of web queries and recorded macros many times to automate the process of importing this data from the web, but the loop is the tricky thing here. I don't know how to handle the looping part. Does it make sense so far? First, I'd like to import the date from he http://www.dsireusa.org/incentives/i...164F&re=1&ee=1 The link is named: 'Costa Mesa - Fee Waiver for Green Building' After importing data from .WebTables = "6,7,8" and offsetting offset 2 or 3 rows, I'd like Excel to go back one web page, to get back he http://www.dsireusa.org/incentives/i...srp=1&state=CA Then, move to the next hyperlink, which is this: http://www.dsireusa.org/incentives/i...A08F&re=1&ee=1 The link is named: 'Marin County - Green Building Incentive Program' Then, I'd like excel to import the date from .WebTables = "6,7,8", offset 2 or 3 rows and then back out of the page, drill down to the next link, and so on and so forth. Does it make sense? I'm pretty sure there is a way to do this, but it may be more complicated than it is worth. What do you think? You are the expert in these matters!! Thanks, Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Joel" wrote: there is nothing to download on the main page. Is there another page you want to download. what method do you want to used. Just a Data -Import External Import Data - New Web Query or do you want to open a n internet explorer and download the daa using the internet explorted. have you tried the Qery. Have you tried the query while recording a macro? I need a little bit more info to be able to answer your question. if the worksheet menu option works then it is simple. If you don't get the results you are looking for then it is harder. "ryguy7272" wrote: I am wondering how easy, or difficult, it will be to do a web query on this site: http://www.dsireusa.org/ Im not sure if there is a pattern or not; doesnt really seem to be one and that may prevent this project from getting off the ground. Anyway, I wanted to try to get Excel to €˜drill down to each state, such as CA, where you would see this: http://www.dsireusa.org/incentives/i...srp=1&state=CA Then drill down to each link, between €˜Financial Incentives and €˜Related Programs & Initiatives (but not below this). In each of those links I want to Import .WebTables = "6,7,8", offset 2 or 3 rows and then back out of the page, drill down to the next link, and so on and so forth. This may be a massive undertaking, and simply not worth the effort, or it may not be bad at all. I cant tell, but if someone could assess, and even give me an idea of how to get started, Id be very appreciative. Thank in advance! Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Web Query? Is This Possible?
Hre is the final results. Use two macros. I wrote the 1st which gets you
all the URL's. Then write the 2nd macro which gets the actual data . Sub USA() 'define states in searching webpage Const FindCategories = 0 Const ExtractCategory = 1 USAStates = Array("AK", "AL", "AR", "AZ", _ "CA", "CO", "CT", "DC", "DE", "FL", "GA", _ "HI", "IA", "ID", "IL", "IN", "KS", "KY", _ "LA", "MA", "MD", "ME", "MI", "MN", "MO", _ "MS", "MT", "NC", "ND", "NE", "NH", "NJ", _ "NM", "NV", "NY", "OH", "OK", "OR", "PA", _ "RI", "SC", "SD", "TN", "TX", "UT", "VA", _ "VT", "WA", "WI", "WV", "WY") BaseName = "http://www.dsireusa.org/incentives/index.cfm?" & _ "re=1&ee=1&spv=0&st=0&srp=1&state=" Set IE = CreateObject("InternetExplorer.Application") IE.Visible = True With Sheets("USA") .Cells.ClearContents RowCount = 1 .Range("A" & RowCount) = "State" .Range("B" & RowCount) = "Category" .Range("C" & RowCount) = "Topic" .Range("D" & RowCount) = "SubTopic" .Range("E" & RowCount) = "URL" RowCount = 2 For Each USAState In USAStates URL = BaseName & USAState IE.Navigate2 URL Do While IE.busy = True Or _ IE.readystate < 4 DoEvents Loop Set document = IE.document 'Call Dump(document) 'code for extracting table State = FindCategories For Each itm In IE.document.all Select Case itm.classname Case "categorytype" Select Case Trim(itm.innertext) Case "Financial Incentives", _ "Rules, Regulations & Policies" Category = Trim(itm.innertext) State = ExtractCategory Case Else State = FindCategories End Select End Select If State = ExtractCategory Then Select Case itm.classname Case "copybold" Topic = Trim(itm.innertext) Case "copy" .Range("A" & RowCount) = USAState .Range("B" & RowCount) = Category .Range("C" & RowCount) = Topic Subtopic = Trim(itm.innertext) .Range("D" & RowCount) = Subtopic HREF = itm.FirstChild.HREF .Range("E" & RowCount) = HREF RowCount = RowCount + 1 End Select End If Next itm .Columns.AutoFit Next USAState End With IE.Quit Set IE = Nothing End Sub Sub Dump(document) With Sheets("dump") .Cells.ClearContents RowCount = 1 For Each itm In document.all .Range("A" & RowCount) = itm.tagname .Range("B" & RowCount) = itm.classname .Range("C" & RowCount) = itm.ID .Range("D" & RowCount) = Left(itm.innertext, 1024) RowCount = RowCount + 1 Next itm End With End Sub "Joel" wrote: This is what I have so far. I'm only doing California and NY as a test. California is working. New York I 'm having problems with one of the links. It is not working like the others and giving me an error. Will Look at it tomorrow. You need to create a worksheet called USA. Sub USA() 'define states in searching webpage Const FindCategories = 0 Const ExtractCategory = 1 USAStates = Array("CA", "NY") BaseName = "http://www.dsireusa.org/incentives/index.cfm?" & _ "re=1&ee=1&spv=0&st=0&srp=1&state=" Set IE = CreateObject("InternetExplorer.Application") IE.Visible = True With Sheets("USA") .Cells.ClearContents RowCount = 1 For Each USAState In USAStates URL = BaseName & USAState IE.Navigate2 URL Do While IE.busy = True And _ IE.readystate < 4 DoEvents Loop .Range("A" & RowCount) = USAState RowCount = RowCount + 1 Set document = IE.document 'Call Dump(document) 'code for extracting table State = FindCategories For Each itm In IE.document.all Select Case itm.classname Case "categorytype" Select Case Trim(itm.innertext) Case "Financial Incentives", _ "Rules, Regulations & Policies" .Range("B" & RowCount) = itm.innertext RowCount = RowCount + 1 State = ExtractCategory Case Else State = FindCategories End Select End Select If State = ExtractCategory Then Select Case itm.classname Case "copybold" .Range("C" & RowCount) = itm.innertext RowCount = RowCount + 1 Case "copy" .Range("D" & RowCount) = itm.innertext .Range("E" & RowCount) = _ itm.FirstChild.href RowCount = RowCount + 1 End Select End If Next itm .Columns.AutoFit Next USAState End With IE.Quit Set IE = Nothing End Sub Sub Dump(document) With Sheets("dump") .Cells.ClearContents RowCount = 1 For Each itm In document.all .Range("A" & RowCount) = itm.tagname .Range("B" & RowCount) = itm.classname .Range("C" & RowCount) = itm.ID .Range("D" & RowCount) = Left(itm.innertext, 1024) RowCount = RowCount + 1 Next itm End With End Sub "Joel" wrote: I'm thinking that you may need to open two internet explorer applications. the links will probably href properties. I probably won't get to look at this until late tonight or tomorrow morning. to get to each state webpage I would simply create a table of each atates abbreviation and use it as part of the URL California webpage is this http://www.dsireusa.org/incentives/i...srp=1&state=CA New York is this http://www.dsireusa.org/incentives/i...srp=1&state=NY So you can put all the state abbreviation on a spreadsheet Then your loop would be BaseName = "http://www.dsireusa.org/incentives/index.cfm?" & "_re=1&ee=1&spv=0&st=0&srp=1&state=" Set IE = CreateObject("InternetExplorer.Application") IE.Visible = True for RowCount = 1 to 50 URL = BaseName & Range("A" & RowCount) IE.Navigate2 URL Do While IE.busy = True And _ IE.readystate < 4 DoEvents Loop Set document = IE.document 'add your code here next rowCount "ryguy7272" wrote: Thanks for taking an interest in this Joel. I've seen your work with web queries; quite remarkable. If anyone can do this, it's probably you. Ok, this is the main page for the site: http://www.dsireusa.org/ If you click on 'CA', for instance, you end up he http://www.dsireusa.org/incentives/i...srp=1&state=CA What I want to do on this page, is get Excel to drill down to each link, between 'Financial Incentives' and 'Related Programs & Initiatives' (not including the information below this). Clicking on the first hyperlink takes you he http://www.dsireusa.org/incentives/i...164F&re=1&ee=1 I want to import the date from .WebTables = "6,7,8", offset 2 or 3 rows and then back out of the page, drill down to the next link, and so on and so forth. I've done lots of web queries and recorded macros many times to automate the process of importing this data from the web, but the loop is the tricky thing here. I don't know how to handle the looping part. Does it make sense so far? First, I'd like to import the date from he http://www.dsireusa.org/incentives/i...164F&re=1&ee=1 The link is named: 'Costa Mesa - Fee Waiver for Green Building' After importing data from .WebTables = "6,7,8" and offsetting offset 2 or 3 rows, I'd like Excel to go back one web page, to get back he http://www.dsireusa.org/incentives/i...srp=1&state=CA Then, move to the next hyperlink, which is this: http://www.dsireusa.org/incentives/i...A08F&re=1&ee=1 The link is named: 'Marin County - Green Building Incentive Program' Then, I'd like excel to import the date from .WebTables = "6,7,8", offset 2 or 3 rows and then back out of the page, drill down to the next link, and so on and so forth. Does it make sense? I'm pretty sure there is a way to do this, but it may be more complicated than it is worth. What do you think? You are the expert in these matters!! Thanks, Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Joel" wrote: there is nothing to download on the main page. Is there another page you want to download. what method do you want to used. Just a Data -Import External Import Data - New Web Query or do you want to open a n internet explorer and download the daa using the internet explorted. have you tried the Qery. Have you tried the query while recording a macro? I need a little bit more info to be able to answer your question. if the worksheet menu option works then it is simple. If you don't get the results you are looking for then it is harder. "ryguy7272" wrote: I am wondering how easy, or difficult, it will be to do a web query on this site: http://www.dsireusa.org/ Im not sure if there is a pattern or not; doesnt really seem to be one and that may prevent this project from getting off the ground. Anyway, I wanted to try to get Excel to €˜drill down to each state, such as CA, where you would see this: http://www.dsireusa.org/incentives/i...srp=1&state=CA Then drill down to each link, between €˜Financial Incentives and €˜Related Programs & Initiatives (but not below this). In each of those links I want to Import .WebTables = "6,7,8", offset 2 or 3 rows and then back out of the page, drill down to the next link, and so on and so forth. This may be a massive undertaking, and simply not worth the effort, or it may not be bad at all. I cant tell, but if someone could assess, and even give me an idea of how to get started, Id be very appreciative. Thank in advance! Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Web Query? Is This Possible?
Thanks for the Effort Joel!!! This is awesome!!!
The code looks right, but does actually this work for you? When I run it here IE opens and hangs for a long time. Then, I close the browser and this line is yellow: Do While IE.busy = True Or IE.readystate < 4 No data was imported in about 7 minutes or so. I have a cable connection; pretty fast. I imagine it will take a while to run, but I don't think it was actually doing anything. unless, everything has to import and then update all at onece. however, I don't think that's what it is doing. Any thoughts? Suggestions? I'm going to try to troubleshoot, but if you can think of something that may prevent this from running (maybe a setting in IE), please let me know. Kindest of regards, Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Joel" wrote: Hre is the final results. Use two macros. I wrote the 1st which gets you all the URL's. Then write the 2nd macro which gets the actual data . Sub USA() 'define states in searching webpage Const FindCategories = 0 Const ExtractCategory = 1 USAStates = Array("AK", "AL", "AR", "AZ", _ "CA", "CO", "CT", "DC", "DE", "FL", "GA", _ "HI", "IA", "ID", "IL", "IN", "KS", "KY", _ "LA", "MA", "MD", "ME", "MI", "MN", "MO", _ "MS", "MT", "NC", "ND", "NE", "NH", "NJ", _ "NM", "NV", "NY", "OH", "OK", "OR", "PA", _ "RI", "SC", "SD", "TN", "TX", "UT", "VA", _ "VT", "WA", "WI", "WV", "WY") BaseName = "http://www.dsireusa.org/incentives/index.cfm?" & _ "re=1&ee=1&spv=0&st=0&srp=1&state=" Set IE = CreateObject("InternetExplorer.Application") IE.Visible = True With Sheets("USA") .Cells.ClearContents RowCount = 1 .Range("A" & RowCount) = "State" .Range("B" & RowCount) = "Category" .Range("C" & RowCount) = "Topic" .Range("D" & RowCount) = "SubTopic" .Range("E" & RowCount) = "URL" RowCount = 2 For Each USAState In USAStates URL = BaseName & USAState IE.Navigate2 URL Do While IE.busy = True Or _ IE.readystate < 4 DoEvents Loop Set document = IE.document 'Call Dump(document) 'code for extracting table State = FindCategories For Each itm In IE.document.all Select Case itm.classname Case "categorytype" Select Case Trim(itm.innertext) Case "Financial Incentives", _ "Rules, Regulations & Policies" Category = Trim(itm.innertext) State = ExtractCategory Case Else State = FindCategories End Select End Select If State = ExtractCategory Then Select Case itm.classname Case "copybold" Topic = Trim(itm.innertext) Case "copy" .Range("A" & RowCount) = USAState .Range("B" & RowCount) = Category .Range("C" & RowCount) = Topic Subtopic = Trim(itm.innertext) .Range("D" & RowCount) = Subtopic HREF = itm.FirstChild.HREF .Range("E" & RowCount) = HREF RowCount = RowCount + 1 End Select End If Next itm .Columns.AutoFit Next USAState End With IE.Quit Set IE = Nothing End Sub Sub Dump(document) With Sheets("dump") .Cells.ClearContents RowCount = 1 For Each itm In document.all .Range("A" & RowCount) = itm.tagname .Range("B" & RowCount) = itm.classname .Range("C" & RowCount) = itm.ID .Range("D" & RowCount) = Left(itm.innertext, 1024) RowCount = RowCount + 1 Next itm End With End Sub "Joel" wrote: This is what I have so far. I'm only doing California and NY as a test. California is working. New York I 'm having problems with one of the links. It is not working like the others and giving me an error. Will Look at it tomorrow. You need to create a worksheet called USA. Sub USA() 'define states in searching webpage Const FindCategories = 0 Const ExtractCategory = 1 USAStates = Array("CA", "NY") BaseName = "http://www.dsireusa.org/incentives/index.cfm?" & _ "re=1&ee=1&spv=0&st=0&srp=1&state=" Set IE = CreateObject("InternetExplorer.Application") IE.Visible = True With Sheets("USA") .Cells.ClearContents RowCount = 1 For Each USAState In USAStates URL = BaseName & USAState IE.Navigate2 URL Do While IE.busy = True And _ IE.readystate < 4 DoEvents Loop .Range("A" & RowCount) = USAState RowCount = RowCount + 1 Set document = IE.document 'Call Dump(document) 'code for extracting table State = FindCategories For Each itm In IE.document.all Select Case itm.classname Case "categorytype" Select Case Trim(itm.innertext) Case "Financial Incentives", _ "Rules, Regulations & Policies" .Range("B" & RowCount) = itm.innertext RowCount = RowCount + 1 State = ExtractCategory Case Else State = FindCategories End Select End Select If State = ExtractCategory Then Select Case itm.classname Case "copybold" .Range("C" & RowCount) = itm.innertext RowCount = RowCount + 1 Case "copy" .Range("D" & RowCount) = itm.innertext .Range("E" & RowCount) = _ itm.FirstChild.href RowCount = RowCount + 1 End Select End If Next itm .Columns.AutoFit Next USAState End With IE.Quit Set IE = Nothing End Sub Sub Dump(document) With Sheets("dump") .Cells.ClearContents RowCount = 1 For Each itm In document.all .Range("A" & RowCount) = itm.tagname .Range("B" & RowCount) = itm.classname .Range("C" & RowCount) = itm.ID .Range("D" & RowCount) = Left(itm.innertext, 1024) RowCount = RowCount + 1 Next itm End With End Sub "Joel" wrote: I'm thinking that you may need to open two internet explorer applications. the links will probably href properties. I probably won't get to look at this until late tonight or tomorrow morning. to get to each state webpage I would simply create a table of each atates abbreviation and use it as part of the URL California webpage is this http://www.dsireusa.org/incentives/i...srp=1&state=CA New York is this http://www.dsireusa.org/incentives/i...srp=1&state=NY So you can put all the state abbreviation on a spreadsheet Then your loop would be BaseName = "http://www.dsireusa.org/incentives/index.cfm?" & "_re=1&ee=1&spv=0&st=0&srp=1&state=" Set IE = CreateObject("InternetExplorer.Application") IE.Visible = True for RowCount = 1 to 50 URL = BaseName & Range("A" & RowCount) IE.Navigate2 URL Do While IE.busy = True And _ IE.readystate < 4 DoEvents Loop Set document = IE.document 'add your code here next rowCount "ryguy7272" wrote: Thanks for taking an interest in this Joel. I've seen your work with web queries; quite remarkable. If anyone can do this, it's probably you. Ok, this is the main page for the site: http://www.dsireusa.org/ If you click on 'CA', for instance, you end up he http://www.dsireusa.org/incentives/i...srp=1&state=CA What I want to do on this page, is get Excel to drill down to each link, between 'Financial Incentives' and 'Related Programs & Initiatives' (not including the information below this). Clicking on the first hyperlink takes you he http://www.dsireusa.org/incentives/i...164F&re=1&ee=1 I want to import the date from .WebTables = "6,7,8", offset 2 or 3 rows and then back out of the page, drill down to the next link, and so on and so forth. I've done lots of web queries and recorded macros many times to automate the process of importing this data from the web, but the loop is the tricky thing here. I don't know how to handle the looping part. Does it make sense so far? First, I'd like to import the date from he http://www.dsireusa.org/incentives/i...164F&re=1&ee=1 The link is named: 'Costa Mesa - Fee Waiver for Green Building' After importing data from .WebTables = "6,7,8" and offsetting offset 2 or 3 rows, I'd like Excel to go back one web page, to get back he http://www.dsireusa.org/incentives/i...srp=1&state=CA Then, move to the next hyperlink, which is this: http://www.dsireusa.org/incentives/i...A08F&re=1&ee=1 The link is named: 'Marin County - Green Building Incentive Program' Then, I'd like excel to import the date from .WebTables = "6,7,8", offset 2 or 3 rows and then back out of the page, drill down to the next link, and so on and so forth. Does it make sense? I'm pretty sure there is a way to do this, but it may be more complicated than it is worth. What do you think? You are the expert in these matters!! Thanks, Ryan--- |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Web Query? Is This Possible?
I'm using 2003 on a high speede dsl line and the code runs in under 2 minutes
(much faster than I thought it would). The webpage may be out of service. did you try using just the internet explorer to get to the page. If the IE explorer works manually then the code should also run. did a IE application run and did the page get to the correct site? I've used this code with IR 6, 7, and 8 in the past. I'm know using 7. The code will hang on that line when the browser is closed since you killed the IE object manually. while the code is running you should be able to switch betwen the webpage and the worksheet and actually watch the spreadsheet filling up as the macro is running. I have a clearcontents msthod in the code so if you restart it will clear the old data. "ryguy7272" wrote: Thanks for the Effort Joel!!! This is awesome!!! The code looks right, but does actually this work for you? When I run it here IE opens and hangs for a long time. Then, I close the browser and this line is yellow: Do While IE.busy = True Or IE.readystate < 4 No data was imported in about 7 minutes or so. I have a cable connection; pretty fast. I imagine it will take a while to run, but I don't think it was actually doing anything. unless, everything has to import and then update all at onece. however, I don't think that's what it is doing. Any thoughts? Suggestions? I'm going to try to troubleshoot, but if you can think of something that may prevent this from running (maybe a setting in IE), please let me know. Kindest of regards, Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Joel" wrote: Hre is the final results. Use two macros. I wrote the 1st which gets you all the URL's. Then write the 2nd macro which gets the actual data . Sub USA() 'define states in searching webpage Const FindCategories = 0 Const ExtractCategory = 1 USAStates = Array("AK", "AL", "AR", "AZ", _ "CA", "CO", "CT", "DC", "DE", "FL", "GA", _ "HI", "IA", "ID", "IL", "IN", "KS", "KY", _ "LA", "MA", "MD", "ME", "MI", "MN", "MO", _ "MS", "MT", "NC", "ND", "NE", "NH", "NJ", _ "NM", "NV", "NY", "OH", "OK", "OR", "PA", _ "RI", "SC", "SD", "TN", "TX", "UT", "VA", _ "VT", "WA", "WI", "WV", "WY") BaseName = "http://www.dsireusa.org/incentives/index.cfm?" & _ "re=1&ee=1&spv=0&st=0&srp=1&state=" Set IE = CreateObject("InternetExplorer.Application") IE.Visible = True With Sheets("USA") .Cells.ClearContents RowCount = 1 .Range("A" & RowCount) = "State" .Range("B" & RowCount) = "Category" .Range("C" & RowCount) = "Topic" .Range("D" & RowCount) = "SubTopic" .Range("E" & RowCount) = "URL" RowCount = 2 For Each USAState In USAStates URL = BaseName & USAState IE.Navigate2 URL Do While IE.busy = True Or _ IE.readystate < 4 DoEvents Loop Set document = IE.document 'Call Dump(document) 'code for extracting table State = FindCategories For Each itm In IE.document.all Select Case itm.classname Case "categorytype" Select Case Trim(itm.innertext) Case "Financial Incentives", _ "Rules, Regulations & Policies" Category = Trim(itm.innertext) State = ExtractCategory Case Else State = FindCategories End Select End Select If State = ExtractCategory Then Select Case itm.classname Case "copybold" Topic = Trim(itm.innertext) Case "copy" .Range("A" & RowCount) = USAState .Range("B" & RowCount) = Category .Range("C" & RowCount) = Topic Subtopic = Trim(itm.innertext) .Range("D" & RowCount) = Subtopic HREF = itm.FirstChild.HREF .Range("E" & RowCount) = HREF RowCount = RowCount + 1 End Select End If Next itm .Columns.AutoFit Next USAState End With IE.Quit Set IE = Nothing End Sub Sub Dump(document) With Sheets("dump") .Cells.ClearContents RowCount = 1 For Each itm In document.all .Range("A" & RowCount) = itm.tagname .Range("B" & RowCount) = itm.classname .Range("C" & RowCount) = itm.ID .Range("D" & RowCount) = Left(itm.innertext, 1024) RowCount = RowCount + 1 Next itm End With End Sub "Joel" wrote: This is what I have so far. I'm only doing California and NY as a test. California is working. New York I 'm having problems with one of the links. It is not working like the others and giving me an error. Will Look at it tomorrow. You need to create a worksheet called USA. Sub USA() 'define states in searching webpage Const FindCategories = 0 Const ExtractCategory = 1 USAStates = Array("CA", "NY") BaseName = "http://www.dsireusa.org/incentives/index.cfm?" & _ "re=1&ee=1&spv=0&st=0&srp=1&state=" Set IE = CreateObject("InternetExplorer.Application") IE.Visible = True With Sheets("USA") .Cells.ClearContents RowCount = 1 For Each USAState In USAStates URL = BaseName & USAState IE.Navigate2 URL Do While IE.busy = True And _ IE.readystate < 4 DoEvents Loop .Range("A" & RowCount) = USAState RowCount = RowCount + 1 Set document = IE.document 'Call Dump(document) 'code for extracting table State = FindCategories For Each itm In IE.document.all Select Case itm.classname Case "categorytype" Select Case Trim(itm.innertext) Case "Financial Incentives", _ "Rules, Regulations & Policies" .Range("B" & RowCount) = itm.innertext RowCount = RowCount + 1 State = ExtractCategory Case Else State = FindCategories End Select End Select If State = ExtractCategory Then Select Case itm.classname Case "copybold" .Range("C" & RowCount) = itm.innertext RowCount = RowCount + 1 Case "copy" .Range("D" & RowCount) = itm.innertext .Range("E" & RowCount) = _ itm.FirstChild.href RowCount = RowCount + 1 End Select End If Next itm .Columns.AutoFit Next USAState End With IE.Quit Set IE = Nothing End Sub Sub Dump(document) With Sheets("dump") .Cells.ClearContents RowCount = 1 For Each itm In document.all .Range("A" & RowCount) = itm.tagname .Range("B" & RowCount) = itm.classname .Range("C" & RowCount) = itm.ID .Range("D" & RowCount) = Left(itm.innertext, 1024) RowCount = RowCount + 1 Next itm End With End Sub "Joel" wrote: I'm thinking that you may need to open two internet explorer applications. the links will probably href properties. I probably won't get to look at this until late tonight or tomorrow morning. to get to each state webpage I would simply create a table of each atates abbreviation and use it as part of the URL California webpage is this http://www.dsireusa.org/incentives/i...srp=1&state=CA New York is this http://www.dsireusa.org/incentives/i...srp=1&state=NY So you can put all the state abbreviation on a spreadsheet Then your loop would be BaseName = "http://www.dsireusa.org/incentives/index.cfm?" & "_re=1&ee=1&spv=0&st=0&srp=1&state=" Set IE = CreateObject("InternetExplorer.Application") IE.Visible = True for RowCount = 1 to 50 URL = BaseName & Range("A" & RowCount) IE.Navigate2 URL Do While IE.busy = True And _ IE.readystate < 4 DoEvents Loop Set document = IE.document 'add your code here next rowCount "ryguy7272" wrote: Thanks for taking an interest in this Joel. I've seen your work with web queries; quite remarkable. If anyone can do this, it's probably you. Ok, this is the main page for the site: http://www.dsireusa.org/ If you click on 'CA', for instance, you end up he http://www.dsireusa.org/incentives/i...srp=1&state=CA What I want to do on this page, is get Excel to drill down to each link, between 'Financial Incentives' and 'Related Programs & Initiatives' (not including the information below this). Clicking on the first hyperlink takes you he http://www.dsireusa.org/incentives/i...164F&re=1&ee=1 I want to import the date from .WebTables = "6,7,8", offset 2 or 3 rows and then back out of the page, drill down to the next link, and so on and so forth. I've done lots of web queries and recorded macros many times to automate the process of importing this data from the web, but the loop is the tricky thing here. I don't know how to handle the looping part. Does it make sense so far? |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Web Query? Is This Possible?
I just retested the code and it works perfectly. I also notice if you want
the US teritories add "TR" to the array definiation. I already had the District of columbia in the List. "ryguy7272" wrote: Thanks for the Effort Joel!!! This is awesome!!! The code looks right, but does actually this work for you? When I run it here IE opens and hangs for a long time. Then, I close the browser and this line is yellow: Do While IE.busy = True Or IE.readystate < 4 No data was imported in about 7 minutes or so. I have a cable connection; pretty fast. I imagine it will take a while to run, but I don't think it was actually doing anything. unless, everything has to import and then update all at onece. however, I don't think that's what it is doing. Any thoughts? Suggestions? I'm going to try to troubleshoot, but if you can think of something that may prevent this from running (maybe a setting in IE), please let me know. Kindest of regards, Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Joel" wrote: Hre is the final results. Use two macros. I wrote the 1st which gets you all the URL's. Then write the 2nd macro which gets the actual data . Sub USA() 'define states in searching webpage Const FindCategories = 0 Const ExtractCategory = 1 USAStates = Array("AK", "AL", "AR", "AZ", _ "CA", "CO", "CT", "DC", "DE", "FL", "GA", _ "HI", "IA", "ID", "IL", "IN", "KS", "KY", _ "LA", "MA", "MD", "ME", "MI", "MN", "MO", _ "MS", "MT", "NC", "ND", "NE", "NH", "NJ", _ "NM", "NV", "NY", "OH", "OK", "OR", "PA", _ "RI", "SC", "SD", "TN", "TX", "UT", "VA", _ "VT", "WA", "WI", "WV", "WY") BaseName = "http://www.dsireusa.org/incentives/index.cfm?" & _ "re=1&ee=1&spv=0&st=0&srp=1&state=" Set IE = CreateObject("InternetExplorer.Application") IE.Visible = True With Sheets("USA") .Cells.ClearContents RowCount = 1 .Range("A" & RowCount) = "State" .Range("B" & RowCount) = "Category" .Range("C" & RowCount) = "Topic" .Range("D" & RowCount) = "SubTopic" .Range("E" & RowCount) = "URL" RowCount = 2 For Each USAState In USAStates URL = BaseName & USAState IE.Navigate2 URL Do While IE.busy = True Or _ IE.readystate < 4 DoEvents Loop Set document = IE.document 'Call Dump(document) 'code for extracting table State = FindCategories For Each itm In IE.document.all Select Case itm.classname Case "categorytype" Select Case Trim(itm.innertext) Case "Financial Incentives", _ "Rules, Regulations & Policies" Category = Trim(itm.innertext) State = ExtractCategory Case Else State = FindCategories End Select End Select If State = ExtractCategory Then Select Case itm.classname Case "copybold" Topic = Trim(itm.innertext) Case "copy" .Range("A" & RowCount) = USAState .Range("B" & RowCount) = Category .Range("C" & RowCount) = Topic Subtopic = Trim(itm.innertext) .Range("D" & RowCount) = Subtopic HREF = itm.FirstChild.HREF .Range("E" & RowCount) = HREF RowCount = RowCount + 1 End Select End If Next itm .Columns.AutoFit Next USAState End With IE.Quit Set IE = Nothing End Sub Sub Dump(document) With Sheets("dump") .Cells.ClearContents RowCount = 1 For Each itm In document.all .Range("A" & RowCount) = itm.tagname .Range("B" & RowCount) = itm.classname .Range("C" & RowCount) = itm.ID .Range("D" & RowCount) = Left(itm.innertext, 1024) RowCount = RowCount + 1 Next itm End With End Sub "Joel" wrote: This is what I have so far. I'm only doing California and NY as a test. California is working. New York I 'm having problems with one of the links. It is not working like the others and giving me an error. Will Look at it tomorrow. You need to create a worksheet called USA. Sub USA() 'define states in searching webpage Const FindCategories = 0 Const ExtractCategory = 1 USAStates = Array("CA", "NY") BaseName = "http://www.dsireusa.org/incentives/index.cfm?" & _ "re=1&ee=1&spv=0&st=0&srp=1&state=" Set IE = CreateObject("InternetExplorer.Application") IE.Visible = True With Sheets("USA") .Cells.ClearContents RowCount = 1 For Each USAState In USAStates URL = BaseName & USAState IE.Navigate2 URL Do While IE.busy = True And _ IE.readystate < 4 DoEvents Loop .Range("A" & RowCount) = USAState RowCount = RowCount + 1 Set document = IE.document 'Call Dump(document) 'code for extracting table State = FindCategories For Each itm In IE.document.all Select Case itm.classname Case "categorytype" Select Case Trim(itm.innertext) Case "Financial Incentives", _ "Rules, Regulations & Policies" .Range("B" & RowCount) = itm.innertext RowCount = RowCount + 1 State = ExtractCategory Case Else State = FindCategories End Select End Select If State = ExtractCategory Then Select Case itm.classname Case "copybold" .Range("C" & RowCount) = itm.innertext RowCount = RowCount + 1 Case "copy" .Range("D" & RowCount) = itm.innertext .Range("E" & RowCount) = _ itm.FirstChild.href RowCount = RowCount + 1 End Select End If Next itm .Columns.AutoFit Next USAState End With IE.Quit Set IE = Nothing End Sub Sub Dump(document) With Sheets("dump") .Cells.ClearContents RowCount = 1 For Each itm In document.all .Range("A" & RowCount) = itm.tagname .Range("B" & RowCount) = itm.classname .Range("C" & RowCount) = itm.ID .Range("D" & RowCount) = Left(itm.innertext, 1024) RowCount = RowCount + 1 Next itm End With End Sub "Joel" wrote: I'm thinking that you may need to open two internet explorer applications. the links will probably href properties. I probably won't get to look at this until late tonight or tomorrow morning. to get to each state webpage I would simply create a table of each atates abbreviation and use it as part of the URL California webpage is this http://www.dsireusa.org/incentives/i...srp=1&state=CA New York is this http://www.dsireusa.org/incentives/i...srp=1&state=NY So you can put all the state abbreviation on a spreadsheet Then your loop would be BaseName = "http://www.dsireusa.org/incentives/index.cfm?" & "_re=1&ee=1&spv=0&st=0&srp=1&state=" Set IE = CreateObject("InternetExplorer.Application") IE.Visible = True for RowCount = 1 to 50 URL = BaseName & Range("A" & RowCount) IE.Navigate2 URL Do While IE.busy = True And _ IE.readystate < 4 DoEvents Loop Set document = IE.document 'add your code here next rowCount "ryguy7272" wrote: Thanks for taking an interest in this Joel. I've seen your work with web queries; quite remarkable. If anyone can do this, it's probably you. Ok, this is the main page for the site: http://www.dsireusa.org/ If you click on 'CA', for instance, you end up he http://www.dsireusa.org/incentives/i...srp=1&state=CA What I want to do on this page, is get Excel to drill down to each link, between 'Financial Incentives' and 'Related Programs & Initiatives' (not including the information below this). Clicking on the first hyperlink takes you he http://www.dsireusa.org/incentives/i...164F&re=1&ee=1 I want to import the date from .WebTables = "6,7,8", offset 2 or 3 rows and then back out of the page, drill down to the next link, and so on and so forth. I've done lots of web queries and recorded macros many times to automate the process of importing this data from the web, but the loop is the tricky thing here. I don't know how to handle the looping part. Does it make sense so far? |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Web Query? Is This Possible?
yes, yes, yes. Did you see my last post? There WAS something weird with my
IE settings. Ok, got it working now!!! This is totally awesome!!! The only things is...I wanted the code to drill down one more level. If you run the code, you will see a link in cell E2. When E2 is selected, hit F2, then hit enter, now click on the link...thats what I wanted; that data right there. I wanted to import everything there; looks like .WebTables = "4". With all youve done, youve made me believe that this is actually possible!! Can you please modify the code to go one level deeper, import that data from there, then continue until all imports are complete? Please send me an email: Thanks a ton for all of this!! You have totally made my weekend!! This is going to save me so much time!! Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Joel" wrote: I just retested the code and it works perfectly. I also notice if you want the US teritories add "TR" to the array definiation. I already had the District of columbia in the List. "ryguy7272" wrote: Thanks for the Effort Joel!!! This is awesome!!! The code looks right, but does actually this work for you? When I run it here IE opens and hangs for a long time. Then, I close the browser and this line is yellow: Do While IE.busy = True Or IE.readystate < 4 No data was imported in about 7 minutes or so. I have a cable connection; pretty fast. I imagine it will take a while to run, but I don't think it was actually doing anything. unless, everything has to import and then update all at onece. however, I don't think that's what it is doing. Any thoughts? Suggestions? I'm going to try to troubleshoot, but if you can think of something that may prevent this from running (maybe a setting in IE), please let me know. Kindest of regards, Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Joel" wrote: Hre is the final results. Use two macros. I wrote the 1st which gets you all the URL's. Then write the 2nd macro which gets the actual data . Sub USA() 'define states in searching webpage Const FindCategories = 0 Const ExtractCategory = 1 USAStates = Array("AK", "AL", "AR", "AZ", _ "CA", "CO", "CT", "DC", "DE", "FL", "GA", _ "HI", "IA", "ID", "IL", "IN", "KS", "KY", _ "LA", "MA", "MD", "ME", "MI", "MN", "MO", _ "MS", "MT", "NC", "ND", "NE", "NH", "NJ", _ "NM", "NV", "NY", "OH", "OK", "OR", "PA", _ "RI", "SC", "SD", "TN", "TX", "UT", "VA", _ "VT", "WA", "WI", "WV", "WY") BaseName = "http://www.dsireusa.org/incentives/index.cfm?" & _ "re=1&ee=1&spv=0&st=0&srp=1&state=" Set IE = CreateObject("InternetExplorer.Application") IE.Visible = True With Sheets("USA") .Cells.ClearContents RowCount = 1 .Range("A" & RowCount) = "State" .Range("B" & RowCount) = "Category" .Range("C" & RowCount) = "Topic" .Range("D" & RowCount) = "SubTopic" .Range("E" & RowCount) = "URL" RowCount = 2 For Each USAState In USAStates URL = BaseName & USAState IE.Navigate2 URL Do While IE.busy = True Or _ IE.readystate < 4 DoEvents Loop Set document = IE.document 'Call Dump(document) 'code for extracting table State = FindCategories For Each itm In IE.document.all Select Case itm.classname Case "categorytype" Select Case Trim(itm.innertext) Case "Financial Incentives", _ "Rules, Regulations & Policies" Category = Trim(itm.innertext) State = ExtractCategory Case Else State = FindCategories End Select End Select If State = ExtractCategory Then Select Case itm.classname Case "copybold" Topic = Trim(itm.innertext) Case "copy" .Range("A" & RowCount) = USAState .Range("B" & RowCount) = Category .Range("C" & RowCount) = Topic Subtopic = Trim(itm.innertext) .Range("D" & RowCount) = Subtopic HREF = itm.FirstChild.HREF .Range("E" & RowCount) = HREF RowCount = RowCount + 1 End Select End If Next itm .Columns.AutoFit Next USAState End With IE.Quit Set IE = Nothing End Sub Sub Dump(document) With Sheets("dump") .Cells.ClearContents RowCount = 1 For Each itm In document.all .Range("A" & RowCount) = itm.tagname .Range("B" & RowCount) = itm.classname .Range("C" & RowCount) = itm.ID .Range("D" & RowCount) = Left(itm.innertext, 1024) RowCount = RowCount + 1 Next itm End With End Sub "Joel" wrote: This is what I have so far. I'm only doing California and NY as a test. California is working. New York I 'm having problems with one of the links. It is not working like the others and giving me an error. Will Look at it tomorrow. You need to create a worksheet called USA. Sub USA() 'define states in searching webpage Const FindCategories = 0 Const ExtractCategory = 1 USAStates = Array("CA", "NY") BaseName = "http://www.dsireusa.org/incentives/index.cfm?" & _ "re=1&ee=1&spv=0&st=0&srp=1&state=" Set IE = CreateObject("InternetExplorer.Application") IE.Visible = True With Sheets("USA") .Cells.ClearContents RowCount = 1 For Each USAState In USAStates URL = BaseName & USAState IE.Navigate2 URL Do While IE.busy = True And _ IE.readystate < 4 DoEvents Loop .Range("A" & RowCount) = USAState RowCount = RowCount + 1 Set document = IE.document 'Call Dump(document) 'code for extracting table State = FindCategories For Each itm In IE.document.all Select Case itm.classname Case "categorytype" Select Case Trim(itm.innertext) Case "Financial Incentives", _ "Rules, Regulations & Policies" .Range("B" & RowCount) = itm.innertext RowCount = RowCount + 1 State = ExtractCategory Case Else State = FindCategories End Select End Select If State = ExtractCategory Then Select Case itm.classname Case "copybold" .Range("C" & RowCount) = itm.innertext RowCount = RowCount + 1 Case "copy" .Range("D" & RowCount) = itm.innertext .Range("E" & RowCount) = _ itm.FirstChild.href RowCount = RowCount + 1 End Select End If Next itm .Columns.AutoFit Next USAState End With IE.Quit Set IE = Nothing End Sub Sub Dump(document) With Sheets("dump") .Cells.ClearContents RowCount = 1 For Each itm In document.all .Range("A" & RowCount) = itm.tagname .Range("B" & RowCount) = itm.classname .Range("C" & RowCount) = itm.ID .Range("D" & RowCount) = Left(itm.innertext, 1024) RowCount = RowCount + 1 Next itm End With End Sub "Joel" wrote: I'm thinking that you may need to open two internet explorer applications. the links will probably href properties. I probably won't get to look at this until late tonight or tomorrow morning. to get to each state webpage I would simply create a table of each atates abbreviation and use it as part of the URL California webpage is this http://www.dsireusa.org/incentives/i...srp=1&state=CA New York is this http://www.dsireusa.org/incentives/i...srp=1&state=NY So you can put all the state abbreviation on a spreadsheet Then your loop would be BaseName = "http://www.dsireusa.org/incentives/index.cfm?" & "_re=1&ee=1&spv=0&st=0&srp=1&state=" Set IE = CreateObject("InternetExplorer.Application") IE.Visible = True for RowCount = 1 to 50 URL = BaseName & Range("A" & RowCount) IE.Navigate2 URL Do While IE.busy = True And _ IE.readystate < 4 DoEvents Loop Set document = IE.document 'add your code here next rowCount "ryguy7272" wrote: Thanks for taking an interest in this Joel. I've seen your work with web queries; quite remarkable. If anyone can do this, it's probably you. Ok, this is the main page for the site: http://www.dsireusa.org/ If you click on 'CA', for instance, you end up he http://www.dsireusa.org/incentives/i...srp=1&state=CA What I want to do on this page, is get Excel to drill down to each link, between 'Financial Incentives' and 'Related Programs & Initiatives' (not including the information below this). Clicking on the first hyperlink takes you he http://www.dsireusa.org/incentives/i...164F&re=1&ee=1 I want to import the date from .WebTables = "6,7,8", offset 2 or 3 rows and |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Web Query? Is This Possible?
How do you want the data. when I've done this before I put all the data into
one worksheet. In this case adding the data starting at column G of the present worksheet. I think you need a two row header. The first Row would be the 3 main categories 1) Last Desire Review 2) Summary 3) Contact the 1st and 3rd category has types which are the items to the right of the Colon on each line or group of lines like this 1) State - skip since it is already in column A 2) Amount 3) Terms 4) WebSite And the details to the right of the column will be in the actual row cell. The other choice is to put data into one cell or multiple cells. I just need to know which column you need and how you need them orgainized. "ryguy7272" wrote: yes, yes, yes. Did you see my last post? There WAS something weird with my IE settings. Ok, got it working now!!! This is totally awesome!!! The only things is...I wanted the code to drill down one more level. If you run the code, you will see a link in cell E2. When E2 is selected, hit F2, then hit enter, now click on the link...thats what I wanted; that data right there. I wanted to import everything there; looks like .WebTables = "4". With all youve done, youve made me believe that this is actually possible!! Can you please modify the code to go one level deeper, import that data from there, then continue until all imports are complete? Please send me an email: Thanks a ton for all of this!! You have totally made my weekend!! This is going to save me so much time!! Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Joel" wrote: I just retested the code and it works perfectly. I also notice if you want the US teritories add "TR" to the array definiation. I already had the District of columbia in the List. "ryguy7272" wrote: Thanks for the Effort Joel!!! This is awesome!!! The code looks right, but does actually this work for you? When I run it here IE opens and hangs for a long time. Then, I close the browser and this line is yellow: Do While IE.busy = True Or IE.readystate < 4 No data was imported in about 7 minutes or so. I have a cable connection; pretty fast. I imagine it will take a while to run, but I don't think it was actually doing anything. unless, everything has to import and then update all at onece. however, I don't think that's what it is doing. Any thoughts? Suggestions? I'm going to try to troubleshoot, but if you can think of something that may prevent this from running (maybe a setting in IE), please let me know. Kindest of regards, Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Joel" wrote: Hre is the final results. Use two macros. I wrote the 1st which gets you all the URL's. Then write the 2nd macro which gets the actual data . Sub USA() 'define states in searching webpage Const FindCategories = 0 Const ExtractCategory = 1 USAStates = Array("AK", "AL", "AR", "AZ", _ "CA", "CO", "CT", "DC", "DE", "FL", "GA", _ "HI", "IA", "ID", "IL", "IN", "KS", "KY", _ "LA", "MA", "MD", "ME", "MI", "MN", "MO", _ "MS", "MT", "NC", "ND", "NE", "NH", "NJ", _ "NM", "NV", "NY", "OH", "OK", "OR", "PA", _ "RI", "SC", "SD", "TN", "TX", "UT", "VA", _ "VT", "WA", "WI", "WV", "WY") BaseName = "http://www.dsireusa.org/incentives/index.cfm?" & _ "re=1&ee=1&spv=0&st=0&srp=1&state=" Set IE = CreateObject("InternetExplorer.Application") IE.Visible = True With Sheets("USA") .Cells.ClearContents RowCount = 1 .Range("A" & RowCount) = "State" .Range("B" & RowCount) = "Category" .Range("C" & RowCount) = "Topic" .Range("D" & RowCount) = "SubTopic" .Range("E" & RowCount) = "URL" RowCount = 2 For Each USAState In USAStates URL = BaseName & USAState IE.Navigate2 URL Do While IE.busy = True Or _ IE.readystate < 4 DoEvents Loop Set document = IE.document 'Call Dump(document) 'code for extracting table State = FindCategories For Each itm In IE.document.all Select Case itm.classname Case "categorytype" Select Case Trim(itm.innertext) Case "Financial Incentives", _ "Rules, Regulations & Policies" Category = Trim(itm.innertext) State = ExtractCategory Case Else State = FindCategories End Select End Select If State = ExtractCategory Then Select Case itm.classname Case "copybold" Topic = Trim(itm.innertext) Case "copy" .Range("A" & RowCount) = USAState .Range("B" & RowCount) = Category .Range("C" & RowCount) = Topic Subtopic = Trim(itm.innertext) .Range("D" & RowCount) = Subtopic HREF = itm.FirstChild.HREF .Range("E" & RowCount) = HREF RowCount = RowCount + 1 End Select End If Next itm .Columns.AutoFit Next USAState End With IE.Quit Set IE = Nothing End Sub Sub Dump(document) With Sheets("dump") .Cells.ClearContents RowCount = 1 For Each itm In document.all .Range("A" & RowCount) = itm.tagname .Range("B" & RowCount) = itm.classname .Range("C" & RowCount) = itm.ID .Range("D" & RowCount) = Left(itm.innertext, 1024) RowCount = RowCount + 1 Next itm End With End Sub "Joel" wrote: This is what I have so far. I'm only doing California and NY as a test. California is working. New York I 'm having problems with one of the links. It is not working like the others and giving me an error. Will Look at it tomorrow. You need to create a worksheet called USA. Sub USA() 'define states in searching webpage Const FindCategories = 0 Const ExtractCategory = 1 USAStates = Array("CA", "NY") BaseName = "http://www.dsireusa.org/incentives/index.cfm?" & _ "re=1&ee=1&spv=0&st=0&srp=1&state=" Set IE = CreateObject("InternetExplorer.Application") IE.Visible = True With Sheets("USA") .Cells.ClearContents RowCount = 1 For Each USAState In USAStates URL = BaseName & USAState IE.Navigate2 URL Do While IE.busy = True And _ IE.readystate < 4 DoEvents Loop .Range("A" & RowCount) = USAState RowCount = RowCount + 1 Set document = IE.document 'Call Dump(document) 'code for extracting table State = FindCategories For Each itm In IE.document.all Select Case itm.classname Case "categorytype" Select Case Trim(itm.innertext) Case "Financial Incentives", _ "Rules, Regulations & Policies" .Range("B" & RowCount) = itm.innertext RowCount = RowCount + 1 State = ExtractCategory Case Else State = FindCategories End Select End Select If State = ExtractCategory Then Select Case itm.classname Case "copybold" .Range("C" & RowCount) = itm.innertext RowCount = RowCount + 1 Case "copy" .Range("D" & RowCount) = itm.innertext .Range("E" & RowCount) = _ itm.FirstChild.href RowCount = RowCount + 1 End Select End If Next itm .Columns.AutoFit Next USAState End With IE.Quit Set IE = Nothing End Sub Sub Dump(document) With Sheets("dump") .Cells.ClearContents RowCount = 1 For Each itm In document.all .Range("A" & RowCount) = itm.tagname .Range("B" & RowCount) = itm.classname .Range("C" & RowCount) = itm.ID .Range("D" & RowCount) = Left(itm.innertext, 1024) RowCount = RowCount + 1 Next itm End With End Sub "Joel" wrote: I'm thinking that you may need to open two internet explorer applications. the links will probably href properties. I probably won't get to look at this until late tonight or tomorrow morning. to get to each state webpage I would simply create a table of each atates abbreviation and use it as part of the URL California webpage is this http://www.dsireusa.org/incentives/i...srp=1&state=CA New York is this http://www.dsireusa.org/incentives/i...srp=1&state=NY So you can put all the state abbreviation on a spreadsheet Then your loop would be BaseName = "http://www.dsireusa.org/incentives/index.cfm?" & "_re=1&ee=1&spv=0&st=0&srp=1&state=" Set IE = CreateObject("InternetExplorer.Application") IE.Visible = True for RowCount = 1 to 50 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Web Query? Is This Possible?
I think the first choice is better, but I don't completely understand what
you mean. I think data in separate cells is best; this is for a database that I'm developing. Basically, this is going into a SQL Server DB. I need to be able to query by different criteria. I don't know how to do this kind of thing using SQL Server, or even Access, so I opted for Excel. I think if everything is in one sheet, with distinct data in distinct cells, it will just be easier to Import into SQL Server; one single 'flat file'. Do you know if this can be done using SQL Server or Access? Excel is perfectly fine as an interface; just one more step to transfer to SQL Server, and this is going to save me soooo much time. Can you send me an email? Thanks for everything!! Ryan-- -- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Use MS Query to query data within the current work book | Excel Discussion (Misc queries) | |||
Convert hard coded query criteria to Parameter Query | Excel Discussion (Misc queries) | |||
Excel 2007 / MS Query - editing existing query to another sheet | Excel Discussion (Misc queries) | |||
Microsoft Query rejects "nz" function in Access Query | Excel Discussion (Misc queries) | |||
How to use a Access Query that as a parameter into Excel database query | Excel Discussion (Misc queries) |