MickTordoff
09-23-2017, 09:29 PM
Old scraping algorithm.
Sub Get_Odds_Data(ByRef CurrentSheet As String)
Dim URL As String
Dim XMLreq As Object
Dim HTMLdoc As Object
Dim OddsTable As Object
Dim tableRows As Object
Dim tableCell As Object
Dim dest As Range
Dim OddsData As Variant
Dim HTMLrow As Integer, i As Integer, C As Integer
Dim AString As String
Dim AnInt As Integer
Set dest = Sheets(CurrentSheet).Cells(31, 1)
dest.Parent.Activate
On Error GoTo keeppreviousread
URL = Sheets(CurrentSheet).Cells(1, 27)
Set XMLreq = CreateObject("MSXML2.XMLhttp")
HTMLrow = 0
With XMLreq
Debug.Print Now, URL
.Open "GET", URL, False
.send
Set HTMLdoc = CreateObject("HTMLFile")
HTMLdoc.body.innerHTML = .responseText
End With
'Extract table into array
Set OddsTable = HTMLdoc.GetElementById("t1") '
Set tableRows = OddsTable.Rows
ReDim OddsData(1 To tableRows.Length - HTMLrow + 1, 1 To tableRows(HTMLrow).Cells.Length)
i = 1
While HTMLrow < tableRows.Length
C = 1
AString = tableRows(HTMLrow).Cells(0).innerText
If IsNumeric(Mid(AString, 1, 1)) Then
FirstCell = True
For Each tableCell In tableRows(HTMLrow).Cells
If FirstCell <> True Then ' ignore first cell in row
OddsData(i, C) = tableCell.innerText
C = C + 1
End If
FirstCell = False
Next
i = i + 1
End If
HTMLrow = HTMLrow + 1
Wend
Worksheets(CurrentSheet).Range("A31:AA60").Clear
'Copy array to sheet cells
Set dest = dest.Offset(0, 0)
dest.Resize(UBound(OddsData, 1), UBound(OddsData, 2)).Value = OddsData
DoEvents
Set XMLreq = Nothing
Set HTMLdoc = Nothing
Set OddsTable = Nothing
Set tableRows = Nothing
Set tableCell = Nothing
Exit Sub
keeppreviousread:
Sheets(CurrentSheet).Cells(25, 2) = "xxxxxxxx"
End Sub
__________________________________________________ ____________
Test
VBA Range.Sort with arrays. Alternative for simple use.
I am considering a fairly simple VBA Array alternative approach to a simple use of the available VBA Range.Sort Method. https://docs.microsoft.com/en-us/office/vba/api/excel.range.sort
In an important file of mine, I currently use the available VBA Range.Sort Method. https://docs.microsoft.com/en-us/office/vba/api/excel.range.sort
It lets me sort some long rows of data where the order is based on the values in one of the columns , then for any rows which have the same value in that column , those similar rows would be further sorted by another column. For the VBA Range.Sort Method the syntax terminology would say that I am using pseudo code of this sort of form
MyRange.Sort Key1:=use column1 , __ , Key2:=use columnx , __ , __ , __ ,
You can use up to 3 “Keyxs” in the simple single code line.
In other words, if you have some identical values in the columns used to define the final list order, you can sort further using the values in a second column to determine the order in the group of identical values in the first column which you used. In the simple single line use of the available VBA Range.Sort Method, you can do that one more time.##
Here is a simple graphical illustration of what I am talking about: Consider this spreadsheet range:
Before:-
_____[/color ( Using Excel 2007 32 bit )
Row\Col
G
H
I
J
K
13Was G13CWas I13Was J13Was K13
14Was G14aWas I14Was J14Was K14
15Was G15gWas I15ce
16Was G16gWas I16bWas K16
17Was G17gWas I17cf
18Was G18GWas I18cWas K18
19Was G19fWas I19Was J19Was K19
Worksheet: Sorting
I will run this simple procedure, ( which is in a normal code module. When I run the procedure the worksheet with Name “Sorting” is active).
Sub RangeSortExample()
range("G13:K19").Sort Key1:=range("G13:K19").Columns("B:B"), Order1:=xlAscending, Key2:=range("G13:K19").Columns("D:D"), order2:=xlAscending, Key3:=range("G13:K19").Columns("E:E"), order3:=xlDescending, MatchCase:=False
End Sub
Here is a break down of what that routine does:
The first key ,
Key1:=range("G13:K19").Columns("B:B"), Order1:=xlAscending , MatchCase:=False
, results in this
_____ ( Using Excel 2007 32 bit )
Row\Col
G
H
I
J
K
13Was G14aWas I14Was J14Was K14
14Was G13CWas I13Was J13Was K13
15Was G19fWas I19Was J19Was K19
16Was G15gWas I15ce
17Was G16gWas I16bWas K16
18Was G17gWas I17cf
19Was G18GWas I18cWas K18
Worksheet: Sorting
The above screen shows that we have all ordered ( based on the “B” columns of that range , ( which is the column H of the spreadsheet). But note, we have duplicates in column 2 with values of g in the last 4 rows.
This next part ,
Key2:=range("G13:K19").Columns("D:D"), order2:=xlAscending, MatchCase:=False
, then sorts those last 4 rows using column J values.
_____ ( Using Excel 2007 32 bit )
Row\Col
G
H
I
J
K
13Was G14aWas I14Was J14Was K14
14Was G13CWas I13Was J13Was K13
15Was G19fWas I19Was J19Was K19
16Was G16gWas I16bWas K16
17Was G15gWas I15ce
18Was G17gWas I17cf
19Was G18GWas I18cWas K18
Worksheet: Sorting
In the above screen shot we see that we now have 3 rows containing all gs in the second column, and all cs in the forth column.
The final term is, noting that we are using xlDescending ,
Key3:=range("G13:K19").Columns("E:E"), order3:=xlDescending
So the last 3 rows are resorted such as to give in those last 3 rows a descending order:
_____ ( Using Excel 2007 32 bit )
Row\Col
G
H
I
J
K
13Was G14aWas I14Was J14Was K14
14Was G13CWas I13Was J13Was K13
15Was G19fWas I19Was J19Was K19
16Was G16gWas I16bWas K16
17Was G18GWas I18cWas K18
18Was G17gWas I17cf
19Was G15gWas I15ce
Worksheet: Sorting
For simplicity I chose to be case insensitive ( so like g = G __ - _ MatchCase:=False )
Some other argument options are available with the available VBA Range.Sort Method. I will not consider those.
## Furthermore, If you add the options on other lines then use a .Apply code line to apply the sort, then you can have more than 3 “Keys”
I am only considering a comparison to the simple single line using minimal options similar to the worked example
Why do an array alternative?.
The .Sort Method appears to be regarded as a good way to do these things, and often a solution for sorting arrays is based on pasting into a worksheet Range , then using the Range.Sort on that and then capturing the sorted range back into the original range
But I was just interested out of interest to have an alternative.
Various sorting methods, mathematical ways to sort..
The various methods of sorting seem immense and need a good understanding of advanced mathematics. I can’t begin to discuss any of that. I will use exclusively the simply “Bubble sort method” , which I will explain from the simplest form, and then adapt to our case
The next posts will go through the simplest Bubble sort theory as applied to a simple 2 dimensional array, and then progress in the following posts to a Function to do similar to the simple case of a VBA Range.Sort Method with the possibility to add keys, for sorting further when values in the initial column used for sorting are identical
For ease of explanation I will refer to the first dimensions in an array as the rows, and the second dimensions as columns , pseudo such that an array could be thought of as a spreadsheet of values. For example a 2 dimensional array of 5 x 2 could be pictorially considered as 5 rows x 2 columns:
r1, c1r1, c2
r2, c1r2, c2
r3, c1r3, c2
r4, c1r4, c2
r5, c1r5, c2
Sub Get_Odds_Data(ByRef CurrentSheet As String)
Dim URL As String
Dim XMLreq As Object
Dim HTMLdoc As Object
Dim OddsTable As Object
Dim tableRows As Object
Dim tableCell As Object
Dim dest As Range
Dim OddsData As Variant
Dim HTMLrow As Integer, i As Integer, C As Integer
Dim AString As String
Dim AnInt As Integer
Set dest = Sheets(CurrentSheet).Cells(31, 1)
dest.Parent.Activate
On Error GoTo keeppreviousread
URL = Sheets(CurrentSheet).Cells(1, 27)
Set XMLreq = CreateObject("MSXML2.XMLhttp")
HTMLrow = 0
With XMLreq
Debug.Print Now, URL
.Open "GET", URL, False
.send
Set HTMLdoc = CreateObject("HTMLFile")
HTMLdoc.body.innerHTML = .responseText
End With
'Extract table into array
Set OddsTable = HTMLdoc.GetElementById("t1") '
Set tableRows = OddsTable.Rows
ReDim OddsData(1 To tableRows.Length - HTMLrow + 1, 1 To tableRows(HTMLrow).Cells.Length)
i = 1
While HTMLrow < tableRows.Length
C = 1
AString = tableRows(HTMLrow).Cells(0).innerText
If IsNumeric(Mid(AString, 1, 1)) Then
FirstCell = True
For Each tableCell In tableRows(HTMLrow).Cells
If FirstCell <> True Then ' ignore first cell in row
OddsData(i, C) = tableCell.innerText
C = C + 1
End If
FirstCell = False
Next
i = i + 1
End If
HTMLrow = HTMLrow + 1
Wend
Worksheets(CurrentSheet).Range("A31:AA60").Clear
'Copy array to sheet cells
Set dest = dest.Offset(0, 0)
dest.Resize(UBound(OddsData, 1), UBound(OddsData, 2)).Value = OddsData
DoEvents
Set XMLreq = Nothing
Set HTMLdoc = Nothing
Set OddsTable = Nothing
Set tableRows = Nothing
Set tableCell = Nothing
Exit Sub
keeppreviousread:
Sheets(CurrentSheet).Cells(25, 2) = "xxxxxxxx"
End Sub
__________________________________________________ ____________
Test
VBA Range.Sort with arrays. Alternative for simple use.
I am considering a fairly simple VBA Array alternative approach to a simple use of the available VBA Range.Sort Method. https://docs.microsoft.com/en-us/office/vba/api/excel.range.sort
In an important file of mine, I currently use the available VBA Range.Sort Method. https://docs.microsoft.com/en-us/office/vba/api/excel.range.sort
It lets me sort some long rows of data where the order is based on the values in one of the columns , then for any rows which have the same value in that column , those similar rows would be further sorted by another column. For the VBA Range.Sort Method the syntax terminology would say that I am using pseudo code of this sort of form
MyRange.Sort Key1:=use column1 , __ , Key2:=use columnx , __ , __ , __ ,
You can use up to 3 “Keyxs” in the simple single code line.
In other words, if you have some identical values in the columns used to define the final list order, you can sort further using the values in a second column to determine the order in the group of identical values in the first column which you used. In the simple single line use of the available VBA Range.Sort Method, you can do that one more time.##
Here is a simple graphical illustration of what I am talking about: Consider this spreadsheet range:
Before:-
_____[/color ( Using Excel 2007 32 bit )
Row\Col
G
H
I
J
K
13Was G13CWas I13Was J13Was K13
14Was G14aWas I14Was J14Was K14
15Was G15gWas I15ce
16Was G16gWas I16bWas K16
17Was G17gWas I17cf
18Was G18GWas I18cWas K18
19Was G19fWas I19Was J19Was K19
Worksheet: Sorting
I will run this simple procedure, ( which is in a normal code module. When I run the procedure the worksheet with Name “Sorting” is active).
Sub RangeSortExample()
range("G13:K19").Sort Key1:=range("G13:K19").Columns("B:B"), Order1:=xlAscending, Key2:=range("G13:K19").Columns("D:D"), order2:=xlAscending, Key3:=range("G13:K19").Columns("E:E"), order3:=xlDescending, MatchCase:=False
End Sub
Here is a break down of what that routine does:
The first key ,
Key1:=range("G13:K19").Columns("B:B"), Order1:=xlAscending , MatchCase:=False
, results in this
_____ ( Using Excel 2007 32 bit )
Row\Col
G
H
I
J
K
13Was G14aWas I14Was J14Was K14
14Was G13CWas I13Was J13Was K13
15Was G19fWas I19Was J19Was K19
16Was G15gWas I15ce
17Was G16gWas I16bWas K16
18Was G17gWas I17cf
19Was G18GWas I18cWas K18
Worksheet: Sorting
The above screen shows that we have all ordered ( based on the “B” columns of that range , ( which is the column H of the spreadsheet). But note, we have duplicates in column 2 with values of g in the last 4 rows.
This next part ,
Key2:=range("G13:K19").Columns("D:D"), order2:=xlAscending, MatchCase:=False
, then sorts those last 4 rows using column J values.
_____ ( Using Excel 2007 32 bit )
Row\Col
G
H
I
J
K
13Was G14aWas I14Was J14Was K14
14Was G13CWas I13Was J13Was K13
15Was G19fWas I19Was J19Was K19
16Was G16gWas I16bWas K16
17Was G15gWas I15ce
18Was G17gWas I17cf
19Was G18GWas I18cWas K18
Worksheet: Sorting
In the above screen shot we see that we now have 3 rows containing all gs in the second column, and all cs in the forth column.
The final term is, noting that we are using xlDescending ,
Key3:=range("G13:K19").Columns("E:E"), order3:=xlDescending
So the last 3 rows are resorted such as to give in those last 3 rows a descending order:
_____ ( Using Excel 2007 32 bit )
Row\Col
G
H
I
J
K
13Was G14aWas I14Was J14Was K14
14Was G13CWas I13Was J13Was K13
15Was G19fWas I19Was J19Was K19
16Was G16gWas I16bWas K16
17Was G18GWas I18cWas K18
18Was G17gWas I17cf
19Was G15gWas I15ce
Worksheet: Sorting
For simplicity I chose to be case insensitive ( so like g = G __ - _ MatchCase:=False )
Some other argument options are available with the available VBA Range.Sort Method. I will not consider those.
## Furthermore, If you add the options on other lines then use a .Apply code line to apply the sort, then you can have more than 3 “Keys”
I am only considering a comparison to the simple single line using minimal options similar to the worked example
Why do an array alternative?.
The .Sort Method appears to be regarded as a good way to do these things, and often a solution for sorting arrays is based on pasting into a worksheet Range , then using the Range.Sort on that and then capturing the sorted range back into the original range
But I was just interested out of interest to have an alternative.
Various sorting methods, mathematical ways to sort..
The various methods of sorting seem immense and need a good understanding of advanced mathematics. I can’t begin to discuss any of that. I will use exclusively the simply “Bubble sort method” , which I will explain from the simplest form, and then adapt to our case
The next posts will go through the simplest Bubble sort theory as applied to a simple 2 dimensional array, and then progress in the following posts to a Function to do similar to the simple case of a VBA Range.Sort Method with the possibility to add keys, for sorting further when values in the initial column used for sorting are identical
For ease of explanation I will refer to the first dimensions in an array as the rows, and the second dimensions as columns , pseudo such that an array could be thought of as a spreadsheet of values. For example a 2 dimensional array of 5 x 2 could be pictorially considered as 5 rows x 2 columns:
r1, c1r1, c2
r2, c1r2, c2
r3, c1r3, c2
r4, c1r4, c2
r5, c1r5, c2