If it helps you any, here is the macro I posted earlier modified to read the
table of values into myStr using the method you posted in your code. Again,
it places the data on the active worksheet with Column A containing the
years and Columns B through M containing January through December's values
for that year appearing in Column A starting on Row 2. This leaves room for
headers on Row 1 (if the layout I used is what you want, the header
placements can be added to the code quite easily).
Sub DistributeYearData()
Dim IE As Object
Dim Rng As Range
Dim X As Long
Dim Z As Long
Dim FileNum As Long
Dim myStr As String
Dim sURLdate As String
Dim MonthData() As String
Dim YearParts() As String
Const sURL1 As String =
"http://tonto.eia.doe.gov/dnav/ng/hist/n9132cn2m.htm"
Set IE = CreateObject("InternetExplorer.Application")
IE.Navigate sURL1
While IE.ReadyState < 4
DoEvents
Wend
myStr = IE.Document.body.innerhtml
Set Rng = Range("A2")
YearParts = Split(myStr, "<td class=B4 ", , vbTextCompare)
For X = 0 To UBound(YearParts) - 1
Rng.Offset(X).Value = Val(YearParts(X + 1))
MonthData = Split(YearParts(X + 1), "<td class=B3", , vbTextCompare)
For Z = 1 To 12
Rng.Offset(X, Z).Value = Val(Replace(MonthData(Z), ",", ""))
Next
Next
End Sub
--
Rick (MVP - Excel)
"Jason" wrote in message
...
Colbert
Thanks! That was exactly what I was looking for!
You even fixed my syntax problem in regular expression. However, I
noticed
that for some reason, if the total for the month is greater than 10,000 it
only grabs the numbers before the comma. Or if the number is just a
single
digit, it messes that up as well...
If you scroll down the months for the totals after Dec 1976, you'll see
the
single digit problem.
And after say, year 2001, you can see the issue with the numbers over
10,000...
You can compare to the totals on the website
http://tonto.eia.doe.gov/dnav/ng/hist/n9132cn2m.htm
I know its just a syntax issue in the Pattern
Thanks again for your work so far.