I am using Microsoft Office Version 1703.
I have been tasked with:
- Creating a weekly Excel sheet using data from AccuWeather Professional for 10 specific locations and have that updated weekly.
- Creating historical data going back 4 or 5 years for the same multiple locations. Ideally I'd like to take the time to automate this as it has been considered a long term project.
Now the pos for doing this was originally using Text to Columns in Excel. If I use Text to Column it imports it as an array and I have to use space as a delimiter to break them down into columns and rows correctly before finally hand inputting it into the presentation sheet.
There is a picture of the accuweather site and the information I'm attempting to grab:
When simply copying and pasting the data I receive this as an array for example:
TODAY'S DATE: 2-JUN-17JUN-17 FOR Monticello White County Airp, IN (676') LAT=40.7N LON= 86.8W
TEMPERATURE PRECIPITATION ACTUAL NORMAL HI LO AVG HI LO AVG DEPT AMNT SNOW SNCVR HDD1 81 48 65 78 55 66 -1 0.00 0.0e 0 0 2 M M M 78 55 67 M M 0.0 0 M 3 M M M 78 56 67 M M 0.0 0 M 4 M M M 79 56 67 M M 0.0 0 M 5 M M M 79 56 68 M M 0.0 0 M 6 M M M 79 57 68 M M 0.0 0 M 7 M M M 79 57 68 M M 0.0 0 M 8 M M M 80 57 69 M M 0.0 0 M 9 M M M 80 58 69 M M 0.0 0 M 10 M M M 80 58 69 M M 0.0 0 M 11 M M M 80 58 69 M M 0.0 0 M 12 M M M 81 58 70 M M 0.0 0 M 13 M M M 81 59 70 M M 0.0 0 M 14 M M M 81 59 70 M M 0.0 0 M 15 M M M 81 59 70 M M 0.0 0 M 16 M M M 81 59 70 M M 0.0 0 M 17 M M M 82 60 71 M M 0.0 0 M 18 M M M 82 60 71 M M 0.0 0 M 19 M M M 82 60 71 M M 0.0 0 M 20 M M M 82 60 71 M M 0.0 0 M 21 M M M 82 60 71 M M 0.0 0 M 22 M M M 82 61 72 M M 0.0 0 M 23 M M M 83 61 72 M M 0.0 0 M 24 M M M 83 61 72 M M 0.0 0 M 25 M M M 83 61 72 M M 0.0 0 M 26 M M M 83 61 72 M M 0.0 0 M 27 M M M 83 61 72 M M 0.0 0 M 28 M M M 83 61 72 M M 0.0 0 M 29 M M M 83 62 73 M M 0.0 0 M 30 M M M 84 62 73 M M 0.0 0 M
TOTALS FOR KMCXHIGHEST TEMPERATURE 81 TOTAL PRECIP 0.00 LOWEST TEMPERATURE 48 TOTAL SNOWFALL 0.0 AVERAGE TEMPERATURE 64.5 NORMAL PRECIP 4.08 DEPARTURE FROM NORM -2.0 % OF NORMAL PRECIP 0 HEATING DEGREE DAYS 0 NORMAL DEGREE DAYS 0
shows up like this:
The HTML selector is:
body > center > table > tbody > tr > td.pageContent > table > tbody > tr:nth-child(2) > td > table > tbody > tr:nth-child(1) > td > font > table:nth-child(5) > tbody > tr > td > pre
The issue with doing a Web Query is that even if I have Internet Explorer save my password it will not login in Web Query. I managed to frankenstein a VBA script that opens I.E., logs in successfully, and navigates to this intended page. I imagine I could create individual scripts in a sequence to accomplish grabbing the weather data for each specific location fairly easily. The problem I'm having is writing a VBA script to only grab what is between that <pre> I referenced above. Right now I have the script selecting all, copying and pasting it into my sheet.
What I would ideally like to accomplish is Navigate to AccuWeather Pro, succesfull Log In, Pull up historical data for specific location. Grab all the data referenced above, import it into Excel, and format it to my presentation sheet automatically. It'd be even nicer if I could get it to automatically update at least weekly.
Here is my VBA code:
Sub Test()
Dim ieApp As Object
Sheets("Sheet1").Select
Range("A1:A1000") = "" ' erase previous data
Range("A1").Select
Set ieApp = CreateObject("InternetExplorer.Application")
With ieApp
.Visible = True
.Navigate "https://wwwl.accuweather.com/error.php?url=proa.accuweather.com/adcbin/professional/forecast_local.asp?zipcode=47960&mt=pro"
Do While .Busy: DoEvents: Loop
Do Until .ReadyState = READYSTATE_COMPLETE: DoEvents: Loop
Set ieDoc = .Document
' fill in the login form – View Source from your browser to get the control names
With ieDoc.forms(0)
.UserName.Value = "username"
.Password.Value = "password"
.Submit
End With
Do While .Busy: DoEvents: Loop
Do Until .ReadyState = READYSTATE_COMPLETE: DoEvents: Loop
' now that we’re in, go to the page we want
.Visible = True
.Navigate "http://proa.accuweather.com/adcbin/professional/historical_index.asp"
Do While .Busy: DoEvents: Loop
Do Until .ReadyState = READYSTATE_COMPLETE: DoEvents: Loop
.ExecWB 17, 0 ' // SelectAll
.ExecWB 12, 2 ' // Copy selection
ActiveSheet.PasteSpecial Format:="Text", link:=False, DisplayAsIcon:=False
Range("A1").Select
.Quit
.Quit ' just to make sure
End With
End Sub
I did my best to be as thorough, accurate, and correct with my question as possible, I apologize if I've committed any stack exchange social faux pas etc.
